SSRS #51 – Using Parameters in SSRS – Cascading Parameter (2)

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.

SSRS #50 – Using Parameters in SSRS (1)

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.