Manipulating SSRS parameter functionality

When it comes to report development it is difficult to accomplish perfection. In any development cycle, there are constant change requests and minor bugs to fix months after deployment and new visualizations and numbers that people would like to see.

The good news, however, is that these changes almost always require relatively simple solutions and, if not, someone out there will almost always have an answer. One such situation last week, though, proved that not to be the case. It was time to get creative.

If you have used SSRS before, you probably have noticed that there are some limitations associated with parameter functionality. One of those limitations relates to multi-select cascading parameters. The long-standing issue with these is that, when changing the selection of the “parent” parameter, the default selection of the “child” parameter is not always automatically changed. Take the below scenario as an example:

The user wants to have three multi-select parameters called League, Teams and Players. Because League is at the highest level, it is expected that this parameter cascades down to Teams, which cascades down to Players. In other words, if the user selects “NBA” from the League list, the Teams parameter list should populate with only NBA teams, and the Players parameter list should populate with only NBA players. That’s easy. But much of the time, the user would like to add a selection, such as “NFL,” and still have ALL children (in this case, all NBA teams and players plus all NFL teams and players) selected by default so that it saves them the step of having to manually select all teams and players every time an item is changed or added to the parent (League) list.

Unfortunately, as described above, this is not what happens, as Microsoft has prevented this functionality by design. The reason being: since the users may have changed the selection in the dependent parameter drop-down, they may not want to overwrite their change with the default values every time they select something else in the parent parameter.

So what if users actually do want the defaults to refresh every time? How do SSRS developers get around this shortcoming? It has to do with the report cache. If you are able to write a query in your dataset in a manner that the dependent parameter would change its value every time you change its parent parameter, the cache will be refreshed every time, thus ensuring that all options are selected by default. By appending a pipe and dynamic count to the value of each dependent parameter, this refresh workaround can be accomplished successfully.

Let’s break down how to accomplish this – first for a SQL-based report, then for a MDX cube-based report. We will use the following table to help visualize this exercise:

SQL Report

First off, since it’s not dependent on anything, the dataset query that feeds the parent parameter will not be any different, no matter the data source (SQL DB or cube). In our example, the value of the parameter would be the League field . Once that dataset is created, you can start addressing the workaround using this step-by-step process:

1. Create three visible parameters: @League, @Team, @Player
2. Create three internal parameters: @LeagueCount, @TeamCount, @PlayerCount. Set the default value of each equal to its respective parameter count (ex: Parameters!League.Count).
3. Create a dataset for the first child (Team), passing through the parent selections as normal (ex: WHERE D_Sports.Team IN (@League)). In that query, create a column that concatenates the Team with a pipe (|) and the count of the values selected in the parent (League), and pass that through the query. Set the value of that parameter equal to the concatenated column. Example: D_Sports.Team + ‘|’ + CAST(@LeagueCount as nvarchar(max)) as Value).
4. Create a dataset for the second child. Dynamic SQL must be used in order to handle passing through the @Team value which, as pointed out in step 3, is a concatenated field that you created. It would look something like this:

• Handling the concatenated value

As you’d expect, simply passing the @Team values to the Player query will not return anything, as an attribute such as “Chicago Bulls|3” does not actually exist in the D_Sports dimension. In order to filter by the correct Player values, the pipe and count must first be stripped back out, and the multi-select values must be joined by a comma, using the following custom code:

• Now, instead of using Parameters!Team.Value as the parameter value to pass through, you will create an expression for your query parameter value using the custom code to handle the strip and pass through the values: Code.CommaSeparatedParam(Parameters!Team.Value).

1. Use the same approach as step 4 when creating the main query that will source your report tablix, chart, etc., bearing in mind that you’ve already written the custom code.
2. Test out that your parameters are now functioning as expected, and ensure that your report runs without issues.

MDX Report

There is some redundancy in the steps below, but considering MDX reports use a different data source from that of SQL reports, its important to list each specific step again.

As stated before, the dataset query that feeds the parent parameter will not be any different than a normal query, so create that first. In our example, the value of the parameter would be the League field. Then, do the following:

1. Create three visible parameters: @League, @Team, @Player.
2. Create a dataset for the first child (Team), passing through the parent selections as normal. Keep in mind that all parameterized queries in MDX must be written as one dynamic string. Create a measure that concatenates the Team member with a pipe (|) and the count of the values selected in the parent, and pass that through the query. Set the value of that parameter equal to the concatenated column.
   • Measure example
     “MEMBER [Measures].[Parent Count] AS ” & Parameters!League.Count & ” MEMBER [Measures].[Value] AS ([Sports].[Team].currentmember + “”|”” + CSTR([Measures].[Parent Count]))”
   • Filter Example:
     “WHERE ({” & join(Parameters!Team.Value,”,”) & “})”
3. Create a similar dataset for the second child (Player). The parameter choices you pass through the query will be handled a little bit differently.
   • Handling the concatenated value:
     • As you’d expect, if you simply pass the @Team values to the Player query, the query will not return anything, as an attribute such as “[Sports].[Team].[Chicago Bulls|3]” does not actually exist in the D_Sports dimension. In order to filter by the correct Player values, the pipe and count must first be stripped back out, and the multi-select values must be joined by a comma, using the custom code from Step 4 of the SQL section.
     • Now, instead of using Parameters!Team.Value as the parameter value and using the SSRS Join function to pass through multiple selections, you will pass through your values like this: “WHERE({“ & Code.CommaSeparatedParam(Parameters!Team.Value) & “})”
4. Use the same approach as Step 3 when creating the main query that will source your report tablix, chart, etc., bearing in mind that you’ve already written the custom code.
5. Test out that your parameters are now functioning as expected, and ensure that your report runs without issues.

—————————————–

This solution, however, does come with one small caveat. There is only one instance in which the refresh will not work. If you select multiple parents and only children that fall under one of those parents, then remove one parent whose children are NOT selected and replace that parent with ONLY ONE other parent, the child parameter will not default to select all. This is because the dependent parameter value, as stated before, must change every time in order for the cache to refresh. In this specific case, which is probably the rarest scenario for any end user I’ve ever heard of, it’s not.

There happens to be a slightly more complex solution that can be implemented to solve this issue where, instead of incorporating a count of the parent parameters, a fully concatenated list can be passed through using more intricate VBA custom code, as described in this blog. The method described above, however, may be a bit easier to understand and follow.

It’s also important to know that Microsoft recently announced increased control over SSRS parameter rendering functionality, as well as a new parameter page, coming to SQL Server 2016 (see details here). Until then, I hope that this solution will provide you with a viable work around for default refresh issues.