Creating reports with cascading parameters

In reporting services, it can be useful to have parameters in which the choice of one parameter is dependent upon another parameter. In this recipe we'll show how to first load up a list of states. Then, once the user selects a state as a second parameter, City will populate with a list of cities for that state.

This recipe will also build on to the next recipe, Creating reports with dynamic datasets.

Getting ready

Create a new report in your Chapter 3 solution, and name it Report 03-05 Cascading Params and Dynamic Datasets.rdl. Use the Add | New Item... menu option, and just pick Report, don't use the Report Wizard.

How to do it....

1. Add a shared data source, pointing to WideWorldImportersDW.

2. Add a new Dataset. Name it State and use the WideWorldImportersDW data source. Select the embedded dataset option, use the SQL as the query, and then click on OK to close the window:

         SELECT DISTINCT  
                [State Province] 
          FROM [Dimension]....