Subscribing to a report with different default par

Question

I have created a report in VS2013. I have also created a Stored procedure that returns a dataset that works out Last_week_start, Last_week_finish, Last_month_start, Last_month_finish etc. I have done this so users can subscribe to these reports and get them emailed to them on a Monday for the weekly and on the 1st on the month for the monthly reports. Do I have to create separate reports for the weekly and monthly reports with different default parameters? ie Audit Report (weekly) and Audit Report (monthly) and maintain 2 reports or is there a more dynamic way to do this

Answer 1

The way I have handled reports that I want to allow end users to subscribe to, and have the default date values vary depending on whether subscription was a daily, weekly, or monthly one, is to have an extra parameter that makes this possible.

The parameter I add to the report is one I call Period (or Report Period), and it has to be the first parameter in the report, or at least listed before the date parameters. The only values in the dropdown for this parameter are Daily, Weekly, and Monthly (or whatever is applicable). These can be whatever you need. Enter these manually as value options in the parameter in the report, since they are not expected to change very often. Based on what the end user chooses for this parameter when creating a subscription, the default date values change. This is done via an expression in the default value for the date parameters that evaluates the value chosen from the Period dropdown.

So, if the end user wants a daily subscription, they choose Daily from the Period parameter dropdown, and the default values for the start and end date parameters change to only include the prior day. If they choose Weekly, the start and end date parameters change to only include the prior week, and so on.

Here is an example for the start date parameter default value expression.

=Switch(Parameters!Period.Value = "Daily" , DateAdd(DateInterval.Day, -1, Today), 
Parameters!Period.Value = "Weekly" , DateAdd(DateInterval.WeekOfYear, -1, DateAdd(DateInterval.Day, -(DatePart(DateInterval.Weekday, Today, 0, 0)-1), Today)) , 
Parameters!Period.Value = "Monthly" , DateAdd(DateInterval.Month, -1, DateAdd(DateInterval.Day, -(DatePart(DateInterval.Day, Today, 0, 0)-1), Today)))

For the end date parameter…

=Switch(Parameters!Period.Value = "Daily" , Today, 
Parameters!Period.Value = "Weekly" , DateAdd(DateInterval.Day, -(DatePart(DateInterval.Weekday, Today, 0, 0)), Today) , 
Parameters!Period.Value = "Monthly" , DateAdd(DateInterval.Day, -(DatePart(DateInterval.Day, Today, 0, 0)), Today))

Warning!! Changing the Period value either in the report designer (preview), or online, will not cause the date values to automatically change right before your eyes. It will while creating (and thus executing) a subscription, however. I have never looked into why this is.

One report, dynamic date ranges based on a parameter.

Give it a try.