Problem
You have a reporting requirement where a user has two or more parameters for a single report, but the values for one parameter depend on the values chosen in a previous parameter. For example, suppose you have two parameters: ProductCategory and Product. When you choose the Product Category then the Product parameter lists only those Products which belong to the selected Product Category. How can we create cascaded parameters in SQL Server Reporting Services?
Solution
This tip assumes that you have previous real world experience building a simple SQL Server Reporting Services (SSRS) Report. I have tried my best to prepare this tip in such a way that SSRS beginners can also understand the problem and implement the solution. I will use AdventureworksDW2008R2 database to demonstrate the solution.
The concept of cascading parameters is a list of values for one parameter which depends on the values chosen for a previous parameter. Cascaded Parameters help the user when a parameter has a long list of values. The user can filter parameters based on the previous parameter. Also keep in mind, when you create cascaded parameters, the independent parameter must be defined before the dependent parameter in the Report Data pane. For example, if you have two parameters: Product and Product Category then you need to choose the Product Category then the Product parameter, so it lists only those products which belong to the selected Product Category. In this case, the Product Category parameter is an independent parameter and it must be selected before the Product parameter in the SSRS Report Data pane. Ordering of the parameters is very important because it determines the order of query execution in Reporting Services. If ordering of the parameters is not correct then you will get an error like “Forward dependencies are not valid” and you can’t preview the report.
Steps to Create Cascaded Parameters in SSRS
To demonstrate the solution, I will create a simple report which has three query parameters @Product, @ProductCategory and @ProductSubCategory. @ProductCategory is an independent parameter, @ProductSubCategor is dependent on the @ProductCategory parameter value and @Product is dependent on the @ProductSubCategory parameter value.
Step 1: Create your data source connection
I am creating an embedded data source connection to AdventureworksDW2008R2 database. You can refer to the below images to create a new data source connection.
Data Source connection successfully created. You can check your new data source connection under the Data Sources folder in the Report Data pane.
Step 2: Create Dataset for your Report
I am creating a new dataset named “MainDataset”</b>; this dataset has three query parameters: @Product, @ProductCategory and @ProductSubCategory. The @Product parameter accepts multiple values, but @ProductCategory and @ProductSubCategory accepts only a single value. You can refer to the image below.
Main Dataset Query
SELECT EnglishProductCategoryName, EnglishProductSubcategoryName, EnglishProductName, SalesAmount, OrderQuantity, UnitPrice, TotalProductCost, OrderDateKey FROM DimProduct INNER JOIN DimProductSubcategory ON DimProduct.ProductSubcategoryKey = DimProductSubcategory.ProductSubcategoryKey INNER JOIN DimProductCategory ON DimProductSubcategory.ProductCategoryKey = DimProductCategory.ProductCategoryKey INNER JOIN FactInternetSales ON DimProduct.ProductKey = FactInternetSales.ProductKey Where FactInternetSales.ProductKey IN(@Product) and DimProductCategory.ProductCategoryKey =@ProductCategory and DimProductSubcategory.ProductSubcategoryKey =@ProductSubCategory
As you can see from the image below, “MainDataset” has been created. This dataset had three query parameters defined, so Reporting Services automatically created three Report parameters.
Step 3: Create Datasets for Parameters
As of now @Product, @ProductCategory and @ProductSubCategory parameters are not using a query for the available values. To get a list of values I create three new datasets, one for each parameter.
- Dataset for @Product Parameter
Product Dataset Query
SELECT DISTINCT ProductKey, EnglishProductName FROM DimProduct
- Dataset for @ProductCategory Parameter
ProductCategory Dataset Query
SELECT DISTINCT ProductCategoryKey, EnglishProductcategoryName FROM DimProductCategory
- Dataset for @ProductSubCategory Parameter
ProductSubCategory Dataset Query
SELECT DISTINCT ProductSubCategoryKey, EnglishProductSubcategoryName FROM DimProductSubCategory
I have created three new datasets one for each parameter. You can refer to the image below.
Step 4: Set available values for Report Parameters
In the previous step, I created three datasets one for each parameter. Each parameter will be assigned its dataset to get the available values.
- @Product Parameter
In the Report Data pane, in the parameters folder, right click on the first parameter @Product and then click on parameter properties. It will open the Report Parameter Properties window. As you know @Product parameter will accept multiple values, so select “Allow multiple values”. Change data type to Integer because @Product will be passing an integer value in the “MainDataset”. You can refer to the image below.
Click on “Available Values” tab and then select “Get values from a query” radio button. For “Dataset” select “Product”, for “Value field” select “ProductKey” and for the “Label field” select “EnglishProductName” and then click OK.
- @ProductCategory Parameter
Again in the Report Data pane, in the parameters folder, right click on the second parameter @ProductCategory and then click on parameter properties. It will open the Report Parameter Properties window. As you know @ProductCategory parameter will accept only single values, so we will not select “Allow multiple values”. Change data type to Integer because @ProductCategory will be passing an integer value in the “MainDataset”. You can refer to the image below.
Click on “Available Values” tab and then select “Get values from a query” radio button. For “Dataset” select “ProductCategory”, for the “Value field” select “ProductCategoryKey” and for the “Label field” select “EnglishProductCategoryName” and then click OK.
- @ProductSubCategory Parameter
Again in Report Data pane, in the parameters folder, right click on the third parameter @ProductSubCategory and then click on parameter properties. It will open the Report Parameter Properties window. As you know @ProductSubCategory parameter will accept only single values, so we will not select “Allow multiple values”. Change data type to Integer because @ProductSubCategory will be passing an integer value in the “MainDataset”. You can refer to the image below.
Click on “Available Values” tab and then select the “Get values from a query” radio button. For “Dataset” select “ProductSubCategory”, for the “Value field” select “ProductSubCaegoryKey” and for the “Label field” select “EnglishProductSubCategoryName” and then click OK.
Available values for all three parameters have been set.
Step 5: Parameter Dataset Modification
- Product Dataset Modification
The @Product parameter is dependent on the @ProductSubCatergory parameter, which means the list of values for @Product parameter depends on the values chosen in the previous @ProductSubCatergory parameter. The available values for the @Product parameter come from the Product dataset so I have to add one query parameter in the Product Dataset which will filter the products for the @ProductSubCategory parameter.
As you can see in the image below, a query parameter has been added in the Product Dataset. This query will return only those Products which belong to the @ProductSubCategory parameter value.
- ProductSubCategory Dataset Modification
The @ProductSubCategory parameter is dependent on the @ProductCatergory parameter, which means the list of values for the @ProductSubCategory parameter depend on the values chosen for the previous @ProductCatergory parameter. The available values for the @ProductSubCategory parameter are coming from the ProductSubCategory dataset, so I have to add one query parameter in the ProductSubCategory dataset which will filter the ProductSubCategory for the @ProductCategory parameter.
As you can see in the image below, a query parameter has been added in the ProductSubCategory dataset. This query will be return only the ProductSubCategory data which belongs to the @ProductCategory parameter value.
Step 6: Test the Cascaded parameter
We have successfully created the cascaded parameters, but purposely I have left one error. If I preview the report I will get the below error.
The reason for the above error is our parameter ordering is not correct in the Report Data pane. You can see from the image below, in the Report Data pane our first parameter is @Product, but this parameter is dependent on @ProductSubCategory parameter, so the @Product parameter should come after the @ProductSubCategory parameter. Let’s change the position of the @Product parameter using the Move Down Arrow button. As you can see from the image below I have moved the @Product parameter after @ProductSubCategory parameter.
Let’s preview the report again. As you can see from the below image initially all dependent parameters will be disabled only the independent parameter will be enabled. For the @ProductCategory parameter I am selecting Bikes, once I selected Bikes the @ProductSubCategory parameter lists all the ProductSubCategory which belongs to the Bikes category. Once I select Mountain Bikes for the @ProductSubCategory parameter, @Product parameter lists all products which belong to the Mountain Bikes ProductSubCategory.
Step 7: Add Report items
At this point you can add the Report Items as per your user requirements.
Report Preview using Cascaded Parameters
Next Steps
- Install AdventureworksDW2008R2 database.
- Try to create one report with cascaded parameters.
- Check out other tips on Parameters.
- Check out all of the SQL Server Reporting Services tips
