How to: Add Cascading Parameters to a Report
New: 17 July 2006
Cascading parameters provide a way of managing large amounts of report data. You can define a set of parameters where the list of values for one parameter depends on the value chosen in another parameter. For example, the first parameter could present a list of product categories. When the user selects a category, the second parameter is updated with a list of subcategories within the category. A third parameter could then display a list of products within the selected subcategory. The value for the product parameter could then be used to filter the report to a particular product. This process of filtering a list of parameter values based on a value from another parameter is known variously as cascading, dependent, or hierarchical parameters.
You create a separate dataset that supplies available values for each cascading parameter. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list. The order of the parameters determines the order in which the parameter queries are run. When you open the Report Properties dialog box, the parameters are listed in order. You can change the order by using the up and down arrow buttons.
For an additional example of using cascading parameters, see Tutorial: Advanced Features Using Parameters.
To create cascading parameters using queried available values lists
Open a report definition in Report Designer. You will be creating a dataset to supply available values for each parameter you define.
Create a dataset named Categories containing a query that retrieves a list of categories. This should be a simple query, with columns for CategoryName and ProductCategoryID, as in the following SQL query:
SELECT ProductCategoryID, Name AS CategoryName FROM Production.ProductCategoryCreate a dataset named Subcategories containing a query that retrieves a list of subcategories, filtered by category, as in the following SQL query:
SELECT ProductSubcategoryID, Name AS SubcategoryName FROM Production.ProductSubcategory WHERE ProductCategoryID = @CategoryCreate a dataset named Products containing a query that retrieves a list of products, filtered by subcategory, as in the following SQL query:
SELECT ProductID, Name AS ProductName FROM Production.Product WHERE ProductSubcategoryID = @SubcategoryEdit the Category report parameter. The parameter already exists because the @Category query parameter was created in the Subcategories dataset. Specify a queried available values list that uses the Categories dataset, setting the label to CategoryName and the value to ProductCategoryID.
Edit the Subcategory report parameter. The parameter already exists because the @Subcategory query parameter was created in the Products dataset. Specify a queried available values list that uses the Subcategories dataset, setting the label to SubcategoryName and the value to ProductSubcategoryID.
Create a new parameter and give it a name of Product. Set the prompt to "Product". Specify an available values list that uses the Products dataset, setting the label to ProductName and the value to ProductID.
Use the value from the Product parameter to filter the data in the report to the product.
See Also
Tasks
How to: Associate a Query Parameter with a Report Parameter (Report Designer)
Concepts
Defining Report Datasets
Using Parameters to Control Report Data
Other Resources
Tutorial: Adding Parameters to a Basic Tabular Report
Tutorial: Advanced Features Using Parameters
Defining Report Data Sources
Data View (Report Designer)