I have attended Mike Davis’ session on Advanced Parameters for SSRS several times now. One of the coolest features he brings up during the session is cascading parameters. In my recent online presentation for 24 Hours of PASS, I was asked how to make this happen. Since I have not been able to find a write up by Mike to point to, I will take a swing at it here.
First, cascading parameters are when one parameter’s selection options are determined by another parameter’s selected criteria. The example Mike uses is allowing the user to narrow down city selections by allowing them to define the state first. Once the state is selected, only cities with that state will be presented as options.
In my example, I will use some data I have on hand for a current project for the LPGA. They often need to select the tournament to review. Since they have data for 50 years, it would be nice to narrow it down to a single year. So we will present the user with 2 parameters, the Year and the Tournament.
The first thing I do is set up my dataset to present the Tournament options to the user:
Please note that I am filtering my list based on the Parameter @Year. Normally I would add additional filters and order by options – but we are keeping this simple.
Next, I set up the parameter for the Tournament. In this case, I will present the Tournament Name to the user, but actually grab the TournamentID as the parameter to utilize. The setup looks like this:
Please note that @Year must be presented before @TournamentID or there will be issues because @Year must be available for the data to pull the Tournament list correctly.
What the user sees is:
Note they have not options for the Tournament selection when the parameters are first presented. Once the year is entered they have options:
Thanks Mike for making it easy. This works starting SSRS 2005.
SQLPam's Blog 