In the previous blog post, SSRS #50 – Using Parameters in SSRS (1), I listed 7 different ways you can use report parameters. They are also the skills that report developers need to master in order to develop interactive reports.

In this blog, I’ll show you some simple steps to use cascading parameters.

Goal

You want to allow users to select a supervisor from the first dropdown list, then be able to see the employees who report to the selected supervisor in the second dropdown list.

The end result looks like this. Note that the second dropdown list is dimmed initially. Once the selection is made from the first dropdown list, the second dropdown list will be selectable.

The screen shots are from BIDS 2008, which will be slightly different from BIDS 2005. The concept and the steps are the same though.

Step 1 – Create two datasets: One for supervisors, one for team members

In my example, they are DATASET_SUP and DATASET_UW. I have other datasets in the screen shots, but they are irrelevant for the purpose of this post.

Examine the properties for the DATASET_SUP, you can see that the SQL query just simply retrieves the unique supervisor names and their IDs (within the selected date range).

Examine the properties for the DATASET_UW, you can see that the SQL query now has a where clause WHERE SUPCCRID IN (@SUP_CCRID).

The at sign @ represents a report parameter. At this point, do not worry about if parameter @SUP_CCRID has been created or not. If not, BIDS will automatically create one for you and you can modify its properties later.

Note1: make sure you have both the ID and the name fields in your queries for both the supervisors and the team members. See my blog post, SSRS #46 – A case against using character field in multi-value parameters, for the reasons why you want to use ID field to pass between queries or stored procedures.

Note 2: the where clause WHERE SUPCCRID IN (@SUP_CCRID) is the trick that makes the cascading happen.

Note 3: as observant as you are, you must have noticed that I didn’t write my where clause as this:

WHERE SUPCCRID = @SUP_CCRID

With WHERE SUPCCRID IN (@SUP_CCRID) , I am allowing multiple values of SUP_CCRID passing to my query, instead of a single value.

Step 2 – Create two parameters: one for supervisors, one for team members

In my example, @SUP_CCRID is the parameter that allows users to select a supervisor from a list, and @CCRID is the parameter that allows users to select team members from a list.

Let’s examine their properties.

For parameter @SUP_CCRID, this is what you will need to do on the Available Values tab:

1. you need to get values from a dataset. Select DATASET_SUP from the dropdown.
2. Select the ID field for the Value field.
3. Select the name field for the Label field.

For parameter @CCRID, you will do the same as for @SUP_CCRID.

1. you need to get values from a dataset. Select DATASET_UW from the dropdown.
2. Select the ID field for the Value field.
3. Select the name field for the Label field.

You are done. Preview your report, you will see the result as shown in the first screen shot in this post.