how to default a date report parameter to the previous week's date
-
I've written an SSRS report to list data where a datetime field is greater than the date entered in a report parameter. Problem is I need to schedule this to run now as a subscription to report all data in the last week i.e. each time the subscription runs it reports for the last week (dateadd(dd,-7,getdate()) in sql speak I guess!). Is it possible to set values against parameters in a subscription? I don't really want to change the sql up front to report from a week ago as I still need the flexibility to run the report from any date on request. Was thinking of creating a linked report may be and setting a default parameter value of a week ago, but don't know how to make this dynamic rather than physically hard-coding the date for a week ago.
-
I would set the default value for the parameter to T-7. You can always override the default when running manually. You can create a dataset with a query using the dateadd function you suggested. This dataset would be the basis for you default parameter.
-
Do you mean type "T-7" in to the default value field for the parameter in question in report manager > properties (of the report) > Parameters tab?
-
No... You need to create a dataset with the following query...
select (dateadd(dd,-7,getdate())) .... be sure to compensate for the time at the end of the date.
Then in the report parameters window, use this dataset as the basis for the default value.
-
Hi,
Assume you want to report on the last week's data, you should have something in your dataset like this: datetime_date beween @date1 and @date2
In the Report Parameters window, click your @date1 parameter. Enter below expression in the Default values section:
=dateadd(DateInterval.Day,-7,Today())
Default for @date2: = Today()
When you create subscription just leave default values selected for these parameters.
Regards,
William S.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply