subscription contains parameter values that are not valid

Question

I have a report with begin date and end date parameters and is specifically meant for subscription that should serve 2 types of subscriptions: 1, One for previous fiscal week -->Begin date is first day of previous fiscal week and end date is last day of previous fiscal week. 2, One for previous day -->Begin date is previous day and end date is also previous day.

Dataset Datefields: This is the dataset query results for both date parameters available and default values. When I create subscription for previous day it only runs that day...after midnight that day, begin date and end date parameter values are blank and the subscription fails with a status message "The subscription contains parameter values that are not valid" values. Subscription created for previous fiscal week is good for a week until the values previous fiscal week start and end dates change.

R. Richards R. Richards · Accepted Answer · 2016-06-01T22:39:10

You are using a dataset for defaults and values for date parameters, which probably isn’t the best approach.

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 (if 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. I have other, less painful, things to fill my time.

Give this a try.

subscription contains parameter values that are not valid

1 Answers