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.
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.
Add a shared data source, pointing to
WideWorldImportersDW
.Add a new Dataset. Name it
State
and use theWideWorldImportersDW
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]....