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.
Let's understand with a simple example; suppose you have two parameters: ProductCategory and Product. When you choose the ProductCategory then the Product parameter lists only those Products which belong to the selected ProductCategory.
How can we create cascaded parameters in SQL Server Reporting Services?
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"; 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 @ProductCategoryand 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 @ProductSubCategoryand 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 @ProductCatergoryparameter. 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 @ProductSubCategoryparameter. 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
Cascading Parameters in SSRS 2014
In SQL Server Reporting Services, Cascading parameters means one parameter value will be filtered depending upon other parameter. In this article we will show you, How to configure Cascading parameters in SQL Server Reporting Services 2014 with example.
We are going to use below shown report to explain, Cascading parameters in SSRS Reports. Please refer SSRS Table Report article to understand the creation of Table report. If you observe the below screenshot, It was a normal Table report with Country, State, City, Full Name, Occupation, Yearly Income and Sales Amount columns.
Below screenshot will show you the, Data Source and Dataset we used for this report.
SQL Command we used in the above screenshot is:
T-SQL QUERY
Configuring Cascading Parameters in SSRS 2014
In this example we will create three Report parameters such as Country, State and City. In final report, If you select United states as country then state parameter will display all the states that belongs to United States. Next, If you select New York as state then City parameter will display all the cities present in New York.
First, We are creating one Embedded Dataset holding Distinct country names from [DimGeography] table in [AdventureWorksDW2014]
SQL Command we used in the above screenshot is:
T-SQL QUERY
Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.
Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties. Please provide valid Name, Prompt text and Data type for the Report parameter.
Allow Multiple values: If you want to allow the user to select more than one value, Please check mark this option. Please refer Multi-Valued parameters in SSRS article to understand the uses of multi values.
Next, We have to select Available Values as shown in below screenshot. If you can specify the values manually, you can select specify values option and enter the values manually. In this example, We would like to use the CountryName dataset we created earlier so, selectGet Values from a query option and select the Dataset from the list. Next, We are assigning [Country] as Value Filed and Label Field.
Click Ok to finish configuring the Country parameter.
Next, We are creating one more Embedded Dataset holding Distinct State names from [DimGeography] table in [AdventureWorksDW2014]. SQL Command we used in the below screenshot is:
T-SQL QUERY
Next, We have to create one more parameter to hold the State information so, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.
Please provide valid Name, Prompt text and Data type for the Report parameter. Please check mark this Allow Multiple values option, If you want to allow the user to select more than one value.
In this example, We would like to use the StateDS dataset we created earlier so, select Get Values from a query option and select the Dataset from the list. Next, We are assigning [State Province Name] as Value Filed and Label Field.
Click Ok to finish configuring the State parameter.
Next, We are creating one more Embedded Dataset holding Distinct City names from [DimGeography] table in [AdventureWorksDW2014]. SQL Command we used in the below screenshot is:
T-SQL QUERY
Next, We have to create one more parameter to hold the City information so, Right Click on the Parameters Folder present in the Report Data tab will open the Context Menu to select Add parameters.. option.
Please provide valid Name, Prompt text and Data type for the Report parameter. Please check mark this Allow Multiple values option, If you want to allow the user to select more than one value.
In this example, We would like to use the CityDS dataset we created earlier so, select Get Values from a query option and select the Dataset from the list. Next, We are assigning [City] column as Value Filed and Label Field.
We have successfully created three Datasets and three Parameters for Country Name, State Name and City.
Now, We have to apply filter conditions on dataset. Please refer Filter at Dataset Level in SSRS article to understand the creation of Filters.
Select the Dataset from the Report data tab and Right click on it will open the context menu. Please select the Dataset Properties.. option from it
Once you click on Add parameters.. option, it will open a new window called Report parameter Properties to configure the parameter properties.
First, Select the previously created StateDs and add below shown filter. This filter allows SSRS Report to filter the states (parameter data) as per the selected country.
Second, Select the previously created CityDs and add below shown filter. This filter allows SSRS Report to filter the Cities (parameter data) as per the selected state.
In this example, We are going to display the records whose [Country Name] is present in[Country Parameter], [State Name] is present in [State Parameter] and [City Name] is present in [City Parameter] so, we are creating three filters for country, state and city. Here we are using Operator as IN Operator. If you find any difficult to write the parameter name, Please click on the fx button and select the parameter name from the GUI.
Click Ok to finish configuring the Filters at Dataset Level. Lets Click on Preview Tab to preview the Data.
From the above screenshot you can observe that, It is displaying Blank Report allowing us to select Multiple values from the Country names only. Remaining parameters for State and City values are disabled. Once you selected the Country Name (s) then State Parameter will be enabled for selection and also displays the states correspond to the selected countries.
Here we are selecting United Kingdom as Country Name so, it is displaying the states inside United kingdom (as per Dataset). Next, We selected England from United kingdom.
Here we are selecting Birmingham, Liverpool and London cities present in United Kingdom
Lets preview the data by pressing Enter button
Thank You for visiting Our Blog.
