Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Cascaded Parameters in SQL Server Reporting Services


By:   |   Read Comments (22)   |   Related Tips: > Reporting Services Parameters

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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.

Report Datasource Window1

Report Datasource Window2

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 Report Dataset Window

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.

Report Data Pane after MainDataset

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

Product Dataset Query

SELECT DISTINCT ProductKey, EnglishProductName FROM DimProduct

  • Dataset for @ProductCategory Parameter

Product Category Dataset

ProductCategory Dataset Query

SELECT DISTINCT ProductCategoryKey, EnglishProductcategoryName FROM DimProductCategory

  • Dataset for @ProductSubCategory Parameter

ProductSubCategory Dataset

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.

Report Data Pane after all datasets

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.

Product Report Parameter Properties1

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.

Product Report Parameter Properties2

  • @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.

ProductCategory Report Parameter Properties1

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.

ProductCategory Report Parameter Properties2

  • @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.

ProductSubCategory Report Parameter Properties1

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.

ProductSubCategory Report Parameter Properties2

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.

Product dataset modification

  • 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.

ProductSubCategory dataset modification

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.

Parameter Forward Dependency 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.

Report parameter reordering

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.

Cascaded Parameters in SSRS

Step 7: Add Report items

At this point you can add the Report Items as per your user requirements.

Report Item List

Report Preview using Cascaded Parameters

Report Preview
Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Thursday, June 08, 2017 - 9:40:39 AM - Chris Stamey Back To Top

 Thanks a lot for the article. It helped me out.


Monday, June 05, 2017 - 1:09:38 AM - Tanmay Back To Top

Hi i want to set these values from using C# is it possible ? if yes then how 

 


Friday, November 11, 2016 - 10:53:05 AM - paul Back To Top

I am just beginning to use SSRS and was looking for some guidance on cascaded parameters. This article is very well explained with some clear guidance. very well broken down explanations. Thank you, i will read more of your articles

 


Thursday, September 15, 2016 - 6:09:02 AM - papaiah nali Back To Top

 

 very good step by step explanation and very nice 

Thanks for posting this and sharing your knoledge

THANK YOU


Tuesday, December 29, 2015 - 5:33:26 AM - Sweetie Back To Top

 Good explanation for beginners..Thank you..

 

 


Thursday, October 29, 2015 - 12:17:48 PM - Paul Back To Top

I just want to thank you for posting and sharing your knowledge.  I would not have been able to produce SSRS excel files from an SSIS package without your information.  I am greatly appreciative of the time you spent posting this.

 

Thank You.


Tuesday, October 06, 2015 - 5:19:03 AM - magnusstefan Back To Top

Where is the window "Report data" in Visual studio 2013?

Hi!

I have an assignment where I am asked to create a test report with Cascaded parameters in a report for use on an Intranet. So I  was happy when I did find this excellent guide! The only problem is, that when I have created my data source, my data sets and my queries, I should go to the window "Report parameter properties", for specifing the data which should be included in the choices in the report, and I cannot find it!

As a matter of fact the milieu I work in, Report designer in Visual Basic Studio Shell 2013, on a MS SQL 2012 Server, looks different in many way compared to the guide. It actually also differs from the Pictures in the book "Microsoft SQL Server 2012 Reporting Services" I also try to use for the purpose.

Am I perhaps working in the wrong place, or what can the problem be? Is this Visual basic 2013 Window some kind of upgrade which is different from the original version where you used to work with Report designer? Is there, if that is the case, any way back to the original interface?

Most grateful for any kind of tips or hint that leads me forward!

 

 


Wednesday, September 09, 2015 - 7:06:16 AM - Ghanesh Back To Top

Dear Sal,

We want to see Product list based on ProductCategory and ProductSubCategory parameter value.

Here parent child relationship is ProductCategory>>ProductSubCategory>>Product not 

Product>>ProductSubCategory>>ProductCategory

The step mentoned in this tip is correct.




Wednesday, September 09, 2015 - 2:17:54 AM - Sal Back To Top

Is Step 5 Correct?

Step 5: Parameter Dataset Modification

  • Product Dataset Modification

You said

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."

--

I think it should be:

@Product Parameter is independent. Therefore @ProductSubCatergory parameter should be dependent on the values chosen in the previous @Product parameter...



Wednesday, August 05, 2015 - 5:51:56 PM - Mike S Back To Top

Very good article! I have a question though. I have a report built using Report Builder with two parameters, the second one dependent on the first so that the screen refreshes when the first parameter is changed and the selections for parameter 2 is updated. The problem is when the report is deployed in SharePoint 2013, there is ugly message that displays after the screen refresh (from changing parameter 1): "Report parameter values must be specified before the report can be displayed. Choose parameter values in the parameters area and click the Apply button." Any help would be appreciated. Thanks!


Monday, August 03, 2015 - 6:44:15 PM - Mark Back To Top

Thanks for the tutorial.  I have a problem with my cascading parameter report where the subcategory defaults to the first value in the group after the drop down window has closed.  I have 3 parameters major category, subcatergory and diagnosis codes and all 3 allow multiple values.   The major category and diagnosis codes parameters maintain the values selected.  How do I correct this problem so the subcategory parameter maintains it values?   For instance I select major categories 280 & 320  and it displays subcategories 280 thru 289 and 320 thru 329 and I select 281, 282, 285, 324, 326.  After the subcategory window closes and I reopen it 280 and 320 are selected are my original selections are removed.

 

 


Wednesday, June 03, 2015 - 4:38:51 AM - Ghanesh Back To Top
Hello Andy,
 
Please check this article, it will give you some idea for your problem.
 
http://www.mssqltips.com/sqlservertip/3506/set-select-all-as-default-for-multivalue-report-parameters-in-sql-server-reporting-services/
 
Regards,
Ghanesh
 

Friday, May 29, 2015 - 10:29:06 AM - Andy Back To Top

Hi

Thank you for the great tutorial. But I have a problem directly related to cascading parameters.

I have parameters par1 (text, multivalue) and par2 (text, multivalue). Par 2 depend on selection in par1.

So at first when I open the report all values are selected in both parameters. But when I change values in par1 then values I par2 do get refreshed but are not all checked and this is very frustrating not only to me but I cant even explain it to the users why it does not work

 

please help

thanks

-andy 

 

 


Wednesday, April 29, 2015 - 12:17:31 PM - Sanjay Soni Back To Top

 

Awesome sir....


Tuesday, March 31, 2015 - 10:59:10 AM - Penny Casey Back To Top

You've done an excellent job of explaining how to solve a problem that has come up again and again in my work as a report writer.  Thank you, especially, for the illustrations.  You have saved me untold hours of fiddling on my own to figure this out.


Thursday, March 26, 2015 - 2:24:39 PM - Kris Back To Top

Awesome.

I could follow the steps and created the report successfully.

I would suggest users to key open SSMS and run all the queries mentioned in the steps so that you will ensure there is a database and data.

Thanks for educating the community and appreciate your volunteer-ship.

I would recommend others to read (One must know basic SSRS report creating Skill Set before attempting).

Thanks a bunch


Tuesday, February 17, 2015 - 8:39:38 AM - Ghanesh Back To Top

Helllo Ernesto,

In this database each product has one productsubcategory and each productsubcategory has one productcategory.

Regards,

Ghanesh


Monday, February 16, 2015 - 6:04:59 PM - Ernesto Back To Top

Example is clear and helps a lot.

In this database are products without category and subcategory, how this items could be included in this two cascading parameters?

Thank you in advance

Ernesto


Monday, February 16, 2015 - 11:14:40 AM - Ghanesh Back To Top

@jhogue

 

Thank you!


Monday, February 16, 2015 - 9:40:41 AM - jhogue Back To Top

Excellent. Clear, simple, to the point.

Especially good when explaining how to place the parameters in proper order. This was something I had to figure on my own a long time before I came across your article.

 


Friday, February 13, 2015 - 8:39:49 AM - Ghanesh Back To Top

@Per Eriksson

Your most welcome! 


Friday, February 13, 2015 - 3:54:05 AM - Per Eriksson Back To Top

Thanks a lot for this excellent tutorial!!!


Learn more about SQL Server tools