One of the readers asked me this question in Ask Sherry Li

Question

Dependent parameter in SSRS

How do I create a dependent parameter in SSRS? For example, select a Supervisor in the first drop down and then the Employees for that Supervisor appear in the second drop down.

Master Skills of Manipulating Report Parameters

Before I answer the question, I’d like to draw your attentions to skills report developers need to master.

Building interactive SQL Server Reporting Services reports not only require report developers to master skills of using parameters, but also manipulating report parameters in many different ways. Here are some of the ways we can manipulate the report parameters:

1. Cascading parameters – example: users select a supervisor from the dropdown, then the employees who report to the selected supervisor will appear in the second dropdown.
2. Available Values – example: pre-populate a dropdown list with all the supervisors.
3. Default Values – example: the Start Date and End Date parameter will be pre-populated with the Beginning of the Month, and the current date, respectively.
4. Multi-value – example: to see productivity of several employees at the same time, users can select multiple employees from a pre-populated dropdown list
5. Custom code in SQL stored procedures – Stored procedures allow the ultimate flexibility to use report parameters. One example is to use SQL code to split comma separated list into table values.
6. Using parameters to dynamically change report item properties – example: use a report view parameter to dynamically show data at aggregation or detail level
7. Using parameters in Drill Through and Sub reports

I’ll blog about the Steps to Create Cascading Parameters in the next post.