|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
If you can't
login and post questions or you are having trouble viewing forum posts:
Click Here
|
Callback
Support |
|
If you live in USA, UK, Canada, Australia or New
Zealand, you can leave us details on your question and request us to call you back and discuss
them with you personally (charges apply). |
|
Click Here
|
Buy Support
Incidents |
|
If you can't find your answer
in the FREE PUBLIC QDeveloper Forum, require URGENT Priority Support, or you need to send us
private or confidential information: |
|
Click Here
|
|
| User Prompted Date Ranges - Excel and SP_REPORT |
| Author |
Message |
|
|
| Posted : 2007-01-30 09:16:23 |
Greetings,
I wish to include SP_REPORT Data into my Excel Spreadsheet. However, each time I load the speadsheet that includes the report, I wish to specify a date range each time the data is refreshed.
I saw the posting on how to do it with information contained in tables - using filters and criteria, but how do I do it for reports? If I specify a date in the msquery itself, I can obtain the data I need but I don't want to go into the msquery editor each time I wish to change the date.
Thanks for your help |
|
|
|
| Tom |
 |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-01-30 10:18:01 |
For sp_reports use the parameters DateMacro = |All|Today|ThisWeek|ThisWeekToDate|ThisMonth
|ThisMonthToDate|ThisQuarter|ThisQuarterToDate|ThisYear|ThisYearToDate|Yesterday|LastWeek|LastWeekToDate|
LastMonth|LastMonthToDate|LastQuarter|LastQuarterToDate|LastYear|LastYearToDate|NextWeek|NextFourWeeks
|NextMonth|NextQuarter|NextYear|
For the reporting period you're interested in. |
|
|
|
|
|
| Posted : 2007-06-11 14:20:53 |
| Is there a way to structure the sp_report to prompt you for the DateFrom and DateTo parameters? Or better yet, look to a named range on a worksheet to obtain the DateFrom and DateTo?
The timeframes I may need to run a report don't always correspond to the list of DateMacro selections. |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-06-11 15:51:17 |
You can also extract data for any period like this:
For October, 2007:
sp_report TrialBalance show Debit_1_Title as Date, AccountNumber, AccountName, Debit_1 as Debit, Credit_1 as Credit
parameters DateFrom = {d'2007-10-01'}, DateTo= {d'2007-10-31'}, ReportBasis = 'Accrual'
See: Running an sp_report in Access using VBA for more on using VBA to program date entry and execution. |
|
|
|
|
|
| Posted : 2007-11-17 08:37:08 |
Tom,
My client needs to pull a query from the begining of a job until a certin date (the end of the year). If the job started in 2005, is still ongoing, and he needs everything from the begining until the end of 2006 how would you setup the DateFrom?
Thanks |
|
|
|
| Tom |
| |
| Group | : Administrator |
| Posts | : 5510 |
| Joined | : 2006-02-17 |
|
| Profile |
|
| Posted : 2007-11-18 21:36:13 |
The date parameters would be:
parameters DateFrom = {d'2005-01-01'}, DateTo= {d'2006-12-31'} |
|
|
|
|