Some SSRS reports have a large number of parameters

I recently had a chance to help a co-worker to modify an existing SSRS+Cube report. The first thing that caught my eye is the large number of parameters in the report.

Although I never really developed any reports that have more than, let’s say, 6 parameters, I can understand why developers sometimes need to use, let’s say more than 20 parameters. This happened most likely because, the report developer is lack of authority to re-design the cube, or because the report has unnecessarily complex design. Another possibility is that the developer had made conscious decision to avoid writing MDX queries and shifting the coding to the Reporting Services.

A bug in SSRS 2008

Here is a scenario where developers sometimes might need to resolve to using a hidden parameter, that sits in the middle of two parameters. In the diagram below, the middle parameter will take the user input from parameter 1, and have some IIF statement to transform the input, and then the dynamically set value will be the input for the dataset 3 which feeds parameters 3.

I’ve tested this type of "cascading" parameters (with a middle parameter that has a dynamically set default value). The short conclusion is this: in SSRS 2008, the parameter 3 failed to reflect user’s section in parameters 1; in SSRS 2012, all is good.

So I had to say that this is a bug in SSRS 2008.

Remove the middle parameter to work around the bug

To work around the bug, fortunately, we have a pretty simple solution. Simply remove the middle parameters, AND then code the IIF logic in the query parameter for dataset 3 (which feeds parameter 3).

The only drawback I can see in this workaround is that if the transformation logic needs to be used for another dataset, then you have no choice by repeating it.

Longer version of the solution

The following is the longer version of the solution.

My Original Suggestion

• Use a middle parameter to do the transformation, with nested IIF statement in both Available Values and Default Values.
• Then pass the middle parameter to the cascading dataset; the middle parameter is passed directly to the dataset without the IIF statement.
• The solution failed in SSRS 2008: the cascading dataset DataSet_Employees would never get refreshed even when the middle parameter is refreshed correctly when ever a new Department is selected by the user.
• Reason for the failure: it might have to do with how the value for the middle parameter is set programmatically with the nested IIF statement, rather than by user selection.
• Good news: this solution worked in SSRS 2012 in my test.

Here are two screen shots from our failed solution.

New Solution That Worked

• Remove the middle parameter entirely.
• Pass the parameter Departments to the cascading dataset, DataSet_Employees, instead of the middle parameter.
• The Departments parameter needs to be modified to perform the transformation; use the same nested IIF statement we used in the middle parameter.
• Voila. It works.
• Reason for the success: by removing the middle parameter and coding the nested IIF in the parameter that is passed to the cascading dataset, Reporting Services made no mistake, but had to refresh the cascading dataset based on the user selection.

Here are a few screen shots from our successful solution.

The nested IIF statement is now coded in the parameter expression.