Overwriting Parameter Defaults

Issue: An interesting question popped today on the discussion list about overwriting the default of a cascading parameter. In this particular case, the report has two parameters: Frequency and HireDate. The HireDate parameter is dependent on the Frequency parameter. Specifically, when the user changes the Frequency parameter, custom code changes the default of the HireDate parameter. Sounds easy, right? Well, not so fast.

This requirement calls for smashing the HireDate default. However, smashing values that the user selected (either explicitly or implicitly by not changing the default) is usually considered to be a bad thing. There exist uncommon cases such as this one where the author of the report can reasonably know the user would expect their previously selected value will be overridden. Unfortunately, Reporting Services doesn’t not currently have a feature which would allow the report author to distinguish the common case from the uncommon case. As a result, the current design handles the common case. (Note: In SQL 2000, SSRS defaulted to smashing the user’s selection with the default when an upstream parameter value changed. And, this resulted in a large volume of customer complaints.

Solution: So, what’s the solution? One option you may want to explore, which the sample report demonstrates (attached), if the Valid Values list changes (as a result of the upstream parameter value changing) and it no longer contains the user’s selection, SSRS is forced to re-evaluate the default. Careful use of the Valid Values list can in many cases simulate the desired behavior of always overriding the user’s selection back to the default. This is I set the HireDate available values to the same expression, so its available value always changes.

[View:/CS/cfs-file.ashx/__key/CommunityServer.Blogs.Components.WeblogFiles/blog/parameter_5F00_default.zip]