How to: Add Cascading Parameters to a Report (Reporting Services)
Cascading parameters provide a way of managing large amounts of report data. You can define a set of related parameters so that the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter is independent and might present a list of product categories. When the user selects a category, the second parameter is dependent on the value of the first parameter. Its values are updated with a list of subcategories within the chosen category. When the user views the report, the values for both the category and subcategory parameters are used to filter report data.
To create cascading parameters, you define the dataset query first and include a query parameter for each cascading parameter that you need. You must also create a separate dataset for each cascading parameter to provide available values. For more information, see How to: Add, Change, or Delete Available Values for a Report Parameter (Reporting Services).
Order is important for cascading parameters because the dataset query for a parameter later in the list includes a reference to each parameter that is earlier in the list. At run time, the order of the parameters in the Report Data pane determines the order in which the parameter queries appear in the report, and therefore, the order in which a user chooses each successive parameter value.
To create the main dataset with a query that includes multiple related parameters
In the Report Data pane, right-click a data source, and then click Add Dataset.
In Name, type the name of the dataset.
In Data source, choose the name of the data source or click New to create one.
In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
In Query, type the query to use to retrieve data for this report. The query must include the following parts:
A list of data source fields. For example, in a Transact-SQL statement, the SELECT statement specifies a list of database column names from a given table or view.
One query parameter for each cascading parameter. A query parameter limits the data retrieved from the data source by specifying certain values to include or exclude from the query. Typically, query parameters occur in a restriction clause in the query. For example, in a Transact-SQL SELECT statement, query parameters occur in the WHERE clause. For more information, see Filtering Rows by Using WHERE and HAVING.
Click Run (!). After you include query parameters and then run the query, report parameters that correspond to the query parameters are automatically created.
Note
The order of query parameters the first time you run a query determines the order that they are created in the report. To change the order, see How to: Change the Order of a Report Parameter (Reporting Services)
Click OK.
Next, you will create a dataset that provides the values for the independent parameter.
To create a dataset to provide values for an independent parameter
In the Report Data pane, right-click a data source, and then click Add Dataset.
In Name, type the name of the dataset.
In Data source, verify the name is the name of the data source you chose in step 1.
In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
In Query, type the query to use to retrieve values for this parameter. Queries for independent parameters typically do not contain query parameters. For example, to create a query for a parameter that provides all category values, you might use a Transact-SQL statement similar to the following:
SELECT DISTINCT <column name> FROM <table>The SELECT DISTINCT command removes duplicate values from the result set so that you get each unique value from the specified column in the specified table.
Click Run (!). The result set shows the values that are available for this first parameter.
Click OK.
Next, you will set the properties of the first parameter to use this dataset to populate its available values at run-time.
To set available values for a report parameter
In the Report Data pane, in the Parameters folder, right-click the first parameter, and then click Parameter Properties.
In Name, verify that the name of the parameter is correct.
Click Available Values.
Click Get values from a query. Three fields appear.
In Dataset, from the drop-down list, click the name of the dataset you created in the previous procedure.
In Value field, click the name of the field that provides the parameter value.
In Label field, click the name of the field that provides the parameter label.
Click OK.
Next, you will create a dataset that provides the values for a dependent parameter.
To create a dataset to provide values for a dependent parameter
In the Report Data pane, right-click a data source, and then click Add Dataset.
In Name, type the name of the dataset.
In Data source, verify the name is the name of the data source you chose in step 1.
In Query type, choose the type of query for the selected data source. In this topic, query type Text is assumed.
In Query, type the query to use to retrieve values for this parameter. Queries for dependent parameters typically include query parameters for each parameter that this parameter is dependent on. For example, to create a query for a parameter that provides all subcategory (dependent parameter) values for a category (independent parameter), you might use a Transact-SQL statement similar to the following:
SELECT DISTINCT Subcategory FROM <table> WHERE (Category = @Category)In the WHERE clause, Category is the name of a field from <table> and @Category is a query parameter. This statement produces a list of subcategories for the category specified in @Category. At run time, this value will be filled in with the value that the user chooses for the report parameter that has the same name.
Click OK.
Next, you will set the properties of the second parameter to use this dataset to populate its available values at run time.
To set available values for a report parameter
In the Report Data pane, in the Parameters folder, right-click the first parameter, and then click Parameter Properties.
In Name, verify that the name of the parameter is correct.
Click Available Values.
Click Get values from a query.
In Dataset, from the drop-down list, click the name of the dataset you created in the previous procedure.
In Value field, click the name of the field that provides the parameter value.
In Label field, click the name of the field that provides the parameter label.
Click OK.
To test the cascading parameters
Click Preview.
From the drop-down list for the first, independent parameter, choose a value.
The report processor runs the dataset query for the next parameter and passes it the value you chose for the first parameter. The drop-down list for the second parameter is populated with the available values based on the first parameter value.
From the drop-down list for the second, dependent parameter, choose a value.
The report does not run automatically after you choose the last parameter so that you can change your choice.
Click View Report. The report updates the display based on the parameters you have chosen.
See Also