SSRS Report subscription date parameters

0
votes

The thing that I want to do is as follows I have a SQL reporting services report that takes date parameter (I have two parameters as Run_Report_From_Date_PARM and Run_Report_To_Date_PARM. These dates are picked from the Datepicker). I want to create a subscription to the report that will run on 1st of every month and show the data for the prior month.

While making subscription if i choose 2016-08-01 for Run_Report_From_Date_PARM and 2016-09-01 for Run_Report_To_Date_PARM then the report is generating always with that date range only. But i need the date ranges modify automatically after every month(like for the next month From date should be 2016-09-01 and to date should be 2016-10-01 ).

1
reporting-services
You can base your parameter value on the queries, or you can move this function into the report's dataset queries. – ajeh
Agree with @ajeh. Though, I'd go more towards a dataset or an expression in the default values of the parameters. – Kevin
@ajeh & @ Kevin- I am new to SSRS so could you guys please elaborate your answer and make it understandable to me. – Satish

1 Answers

1
votes

What is simplest is to just use expressions to set the default values for these parameters.

For a start date of the first of last month:

=DateAdd("m", -1, DateAdd("d", 1 + -1 * DatePart("d", Today()), Today()))

For an end date of the last of last month:

=DateAdd("d", -1 * DatePart("d", Today()), Today())

However, if you don't want these to be the default values when executing the report, you can set up a hidden boolean parameter that you only set to true in the subscription settings. That can be useful if you need to include formatting or default value tweaks in the subscription version of a report.