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:
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:
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:
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:
—————————————–
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.