This article will continue to deal with parameters as in the last article of the two part series Optimization of SSRS Reports having Multiple Parameters. In this article we will look at how to add cascading parameters to a SQL Server Reporting Services (SSRS) report. This article also assumes that you know how to add report parameters to a report and setup data sources.
So what is a cascading parameter? A cascading parameter provides a way to filter the available values in another pick list based on the value selected in another pick list.
In this article we will look at a trivial example using the de-facto standard AdventureWorks database and the concept of Territories to Employees. Our goal is to choose a value from a pick list populated with Territories and then to have a second pick list of Employees be populated with only Employees found within the selected Territory.
We begin by creating 3 stored procedures
- rdc_LookupDepartments which will be the data source for the first pick list of departments
- rdc_LookupEmployeesByDepartment which will be the data source for the a list of Employees by a given department id
- rdc_GetEmployee Contact Information which will be the dataset for our report
The data sources for this report are setup as in the following screenshots.
Since our first pick list of Departments is driven from a stored procedure we need to manually add the parameter to the report in order for the first drop down list to be visible.
Set the options on this parameter as noted in the following screenshots.
When we added the dataset rdc_LookupEmployeeByDepartment a report parameter was created automatically. In order for this parameter to get the selected value from the Department pick list we must set its properties as in the following screenshots. What really makes all of this possible is the ‘Default Values’ option. We are telling the reporting engine that we want the default value of this pick list to be all employees with the Department ID selected from the first pick list.
When this report is executed the departments drop down list is fully populated with all departments while the list of employees is grayed out. You can see this behavior in the following screenshot.
Now when a department is selected from this list the selected value cascades as a parameter to our second pick list and then this procedure is executed and the drop down list is populated as you can see from the following screenshot.
We could continue this behavior for up to 32 nested levels of cascading parameters (although I would highly recommend never doing that!)
I hope this article has shown you how to add a cascading parameter and also that cascading parameters are a valuable way to limit the amount of report options that are presented to your users.
Until next time Happy Coding!