Set Default date for date prompt

[santosh.yelugoi]

Hi All,

I have a report studio report where I have a Daterange prompt .I want to schedule this report every Monday so that it has to take the date range for previous week i.e. Last Monday to Sunday. And the same report can be run by users on adhoc bais as their wish of date range selection.

If any one have any Idea please share with me.

Regards,
Santosh

[RKMI]

Hi Santosh,

Here is an Idea.  ;D

Best way to do this is add another prompt name is date filter where you will enter static choices such as last week, prior month, use a date range. Then since your user will mostly run it with date range default it to use date range. Then apply the filter on you query similar to this.

Then add filter similar to this,

(
?Date Filter? = 'Prior Month' AND [Presentation].[Months].[Month Begin Date] BETWEEN ADD_MONTHS(TRUNC({sysdate},'MONTH'), -1) AND  LAST_DAY(ADD_MONTHS(TRUNC({sysdate},'MONTH'), -1))
)
OR
( ?Date Filter? = 'Use Date Range' AND [Presentation].[Months].[Month Begin Date] BETWEEN cast(?Start Date?, date) AND cast(?End Date?, date)
)

I know this logic works, since I use this everywhere.

Happy Friday!  :)
RK

[yossiea]

You can also do something along the lines of an if then filter in the query.
If the prompt is populated, then use prompt date, otherwise do 1=1.
If the prompt is not populated, then do date-7, otherwise do 1=1.

I use this method when I have two date fields (or similar) a user might query against. Depending on which prompt value they select, the filter will filter against the specific date field in the DB.