The first part of the article about Reporting Services parameters explained how you can create parameters in SSRS, how you can provide values and set defaults, how you can use them to make your reports more dynamic and flexible, and how they can be utilized in data sets (both SQL and MDX) to make your reports more efficient.
This second part will explain some more advanced topics of SSRS parameters:
- * Drillthrough parameters
- * Cascading parameters
- * Dealing with multi-valued parameters
A very common scenario in Reporting Services is the use of parent/child reports. For example you start out with a report that contains high-level aggregated data – the parent report – where you can click on a link to drill through to the details displayed in another report, the child report.
The parent report here displays the sales information across the product categories over the different years:
Suppose I am interested in find out why the sales for the category Bikes has risen sharply between 2006 and 2007 (2005 and 2008 have only sales data for two quarters, hence the low values). I click on the cell containing the value 19,956.01 and this takes me to a drillthrough report, giving me more detailed sales information. The sales are split up in the subcategories of the category Bikes and the quarters of the year 2007.
In the detailed report we can see a new subcategory was added to the product catalog (Touring Bikes) which has led to additional sales.
How can we set this up in SSRS? First of all the child report needs to have two query parameters: @Year and @Category (see part 1 for more information on how to create parameters). These query parameters filter the data set that retrieves the sales data for the subcategories and the quarters. The query looks like this:
In the parent report, we need to add an action in order to enable the drillthrough. This is done in the textbox properties of the tablix cell.
In the Action pane, a Go to report action can be added. We need to specify the destination report and optionally we can map values of the tablix to the parameters of the child report.
This mapping is the crucial part of the drillthrough functionality. When clicking on a cell in the tablix of the parent report, the values of that cross section (the column value of year and the row value of product category) is passed on to the corresponding parameters of the child report.
Note that only visible and hidden parameters can be selected in the Name dropdown box. You cannot map values to an internal parameter of the child report. If the parameters are set to Visible, the child report can be run independently from the parent report. Or when you arrive to the child report using the drill through, you can optionally change the parameters to look at different scenarios.
When the parameters are set to Hidden, the child report can only be rendered through the parent report (unless you specify default values for the parameters). If you would run the report though, it will return an error saying values are not specified for the parameters.
For more information about drillthrough reports and their parameters, check out the following resources:
- * Drillthrough Reports (Report Builder and SSRS)
- * Adding Parameters to Pass to a Drillthrough Report (SSRS)
Suppose we have a report that we want to filter on subcategories. But there are a lot of different subcategories – making the dropdown box unwieldy – and some subcategories of different product categories share the same name. It would be useful if we could filter the selection of the subcategories based on the category. Cascading parameters is the solution to this problem and it provides us with a way to manage large amounts of report data.
First of all, we add a data set to the report that will fetch values for the product category. The query looks like this:
Then we add another data set to fetch values for the subcategories, but this time we add the @Category parameter as a filter to the query.
And that’s it! When we run the report, the prompt for subcategory will be greyed out until we select a value for product category.
Once a category is selected, we can choose a subcategory belonging to that category from the dropdown:
The @Year parameter is independent from the @Category and @SubCategory parameter, so a value can be provided to its prompt at any time.
In the parameters section of the Report Data toolbar, make sure cascading parameters are listed in the correct order. Suppose we would switch the @SubCategory parameter with the @Category parameter.
If the report is rendered, we receive an error stating forward dependencies are not allowed between parameters.
With a small trick, cascading parameters can be made optional. All we need to do is modify the queries of the data sets a bit. The query getting the sales data becomes:
The query for fetching the categories becomes:
And finally the query for the subcategories:
When we run the report, we can now select the Select All dummy record for the product category.
With this value selected, we get all of the subcategories and the additional Select All.
If we choose Select All for the product subcategory as well, we can run the report filtering on the year only, making the cascading parameters fully optional.
For more information about cascading parameters, check out the following resources:
- * Add Cascading Parameters to a Report (Report Builder and SSRS)
- * Adding Cascading Parameters (SSRS)
- * Walkthrough: Implement Optional Cascading Parameters with “<Select All>” in Reporting Services
Dealing with Multi-valued Parameters
At the end of the first part of this article parameters that allowed multiple values were shortly introduced in the section Parameters in MDX queries. MDX deals with multi-valued parameters pretty well and we will not discuss them further in the context of MDX queries. In this section; however, we will delve deeper into the consequences of working with multi-valued parameters in regular T-SQL queries.
Suppose we have the following report that breaks sales down by years and by months:
Now we want to filter the report by year, so that only the years are shown that interest us. Since we want to possibly look at multiple years, we need to configure the @Year parameter to accept multiple values.
We also need to modify the query where the parameter is used. Instead of using the equality operator, we need to use the T-SQL IN operator.
And that’s it for embedded SQL queries. We can now render the report and select multiple years to filter the report.
This results in the following report:
However, we still need to be cautious when we use the parameter in expressions. Suppose we want to show the selected years in a textbox.
The rendered report will return an error value to the textbox, because the parameter is actually an array of values now. The report itself will not error out, but show a warning instead.
To solve this issue, we need to concatenate the different values of the parameter together with the Join function.
Now the report renders without a problem:
The reason why multivalued parameters work so easily with embedded SQL queries and the IN operator is because SSRS is smart enough to pass a comma separated list into the SQL query. This can be verified using SQL Server Profiler.
Note that the parameter values are passed as Unicode values.
Unfortunately, this all falls apart when a stored procedure is used. Suppose we replace the embedded SQL query with the following stored procedure:
In the dataset properties window, we need to set the query type to stored procedure and select our newly created stored procedure from the dropdown list.
In the parameters pane, you can verify if the stored procedure parameter is mapped correctly to the report filter.
The report will still work when only one value is selected, but once multiple values are selected it errors out.
The reason for this is that SSRS passes a comma separated list into the @Year parameter of the stored procedure.
Since the @Year parameter is an integer, it will error out when it receives a Unicode string. Changing the parameter data type to NVARCHAR will not resolve the issue, because there is not a single year with the value “2006,2007,2008” (SSRS passes it a single value). The stored procedure won’t return any rows.
To get this issue resolved, we need to adapt the stored procedure. The most efficient method would be to use a splitter function that returns the individual values of the comma separated list and then use those values to filter the result set. An excellent splitter function is the DelimitedSplit8K table-valued function written by Jeff Moden. Due to its use of tally tables to split out the list, it is extremely fast and reliable.
The modified stored procedure takes the following form:
The report will now run without issues:
The workaround using the splitter function is effective, but it might become cumbersome when you have a lot of multivalued parameters. In that case you can consider using embedded SQL queries or using an Analysis Services cube as your data source.
For more information on multi-valued parameters, check out the following resources:
- * SQL Server Reporting Services Using Multi-value Parameters
- * Passing multivalue parameters in ssrs
- * Adding Parameters to Select Multiple Values in a List (SSRS)
- * How multi-value query parameters in SSRS are treated by SQL Server
This ends the two-part article on using parameters in Reporting Services reports. The most common uses, cases and scenarios have been explained. Important to remember is that parameters can be a very powerful instrument to make your reports more flexible, dynamic and efficient.
The reports used in this part of the article can be downloaded here.