EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Software Development Software Development Tutorials SSRS Tutorial SSRS Cascading Parameters
 

SSRS Cascading Parameters

Updated April 3, 2023

SSRS Cascading Parameters

 

 

Introduction to SSRS Cascading Parameters

The following article provides an outline for SSRS Cascading Parameters. Cascading parameters involve managing vast volumes of data. We can group relevant parameters, so the choice of options for one parameter is influenced by the value selected for the other. The first parameter, for instance, is self-contained and may display a list of product categories. The second parameter’s value is relative to the value of the first parameter when the user selects a category.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

What are SSRS Cascading Parameters?

When a parameter contains a long list of values, cascaded parameters assist the user. The user can utilize the last parameter to filter parameters. Or in other words, the second parameter’s values should be dynamically determined by the first parameter’s value. Cascading parameters can be used to achieve this.

To build cascading factors, we must first define the dataset query and then add a query element for each cascading parameter required. To give accessible values, one must also construct a distinct dataset for every cascade parameter. When we select the Add variables option, a pop-up window called Report parameter Properties will open, enabling us to customize the parameter properties. For the Report Parameter, enter a reasonable Choice, Prompt text, and Data type. Allow Multiple Values: Select this option if we want the user to be able to choose more than a value.

Create SSRS Cascading Parameters

  • Click Run to test the cascade parameters.
  • Select a value for the very first, an independent parameter from the drop-down list.
  • The reports processor executes the dataset query for the following parameter and provides the value specified for the first parameter. The second parameter’s drop-down list is populated with the possible values based on the first parameter’s value.
  • Select a value for the second dependent parameter from the drop-down list.
  • After we choose the last parameter, the analysis does not run automatically, allowing you to alter your mind.
  • To see the report, click View Report. The report’s appearance is updated based on the parameters that we select.

Steps to create:

Step 1: Create a data source process connection.

SSRS Cascading Parameters 1

After including a data source, the connection process is given here.

SSRS Cascading Parameters 2

Step 2: Creating a new database.

SSRS Cascading Parameters 3

Step 3: Create datasets for parameters to have a list of values.

Enlarge the Datasets node, right-click on the dataset to which the parameters should be tied, and choose Dataset Properties from the context menu. Then, in the Dataset Settings dialogue, select the Parameters option, enter each variable’s name from the query, and match this to the appropriate report parameter.

SSRS Cascading Parameters 4

Step 4: Report Parameter Properties.

This would also add a line to the parameter section, as shown below. As a result, a new part in the report area is created for entering parameter values, as seen below.

SSRS Cascading Parameters 5

Step 5: Dataset Modification.

We must define a parameter for the second dataset query as shown below.

Code:

SELECT DISTINCT SalPersonID
FROM Sales.SalesOrderHeader where Customerid=@custid

The parameter value of the third dataset must therefore be assigned to the parameter property of the given dataset, as shown below.

SSRS Cascading Parameters 6

Step 6: Last Step- Report Preview.

SSRS Cascading Parameters 7

SSRS Cascading Parameters Configure

We must use the filter conditions to demonstrate the SSRS Cascading Parameters feature. So, select the dataset from the Report data tab and right-click it to bring up the context menu. Please choose the Dataset Properties. Option from the drop-down menu. Select the characteristics icon in the setup window to ensure the following report parameters appear: category, sub-category, and product. We should establish a dataset for every reporting parameter and provide information for it at run time.

To demonstrate the solution, we have developed a basic report with three query parameters: @Product, @ProductCateg, and @ProductSubCateg. @ProductCateg is an independent parameter, @ProductSubCateg is a dependent parameter of @ProductCateg, and @Product is a dependent parameter of @ProductSubCateg.

1. The next step is to create a new dataset like the below:

new dataset

2. The “Main Dataset” has been made. Because this dataset has three query parameters defined, Reporting Services built three Report parameters automatically.

The "Main Dataset" has been made

3. Taking a Dataset Query by creating three new datasets. First, let’s see the Parameter Dataset for a Product.

Dataset Query

4. Dataset for Product Category options.

Product Category options

Similarly, we should do for the Sub-category parameter. After creating three datasets, the image would look like this:

SSRS Cascading Parameters 12

5. Right-click the first parameter @Product in the Report Data pane’s parameters directory, then click parameter settings. The Report Parameter Properties window will pop up. So because the @Product field can have many values, select “Allow multiple values.” As @Product would pass an integer number in the “Main Dataset,” modify the data type to Integer.

SSRS Cascading Parameters 13

6. Choose the “Get values from a query” via the radio button. Choose “Product” for “Data source,” “Product Key” in “Value field,” and “English ProductName” for “Label field,” but then press OK.

SSRS Cascading Parameters 14

The exact process is done for a third parameter sub-category product. Right-click on the third parameter @ProductSubCategin in the parameters directory in the Reports Data window, and afterward, click on parameter preferences. The Report Parameter Properties window will pop up. We won’t pick “Allow multiple values” since the @ProductSubCateg field only accepts single values. Because @ProductSubCateg will send an integer number in the “MainDataset,” modify the data type to Integer.

SSRS Cascading Parameters 15

As of now, all the parameters have been set. The @Product parameter relies on the @ProductSubCateg parameter, which implies that the variance inflation factor determines the @Product parameter’s list of entries in the earlier @ProductSubCategory parameter. Because the available values for the @Product parameter originate from the Product dataset, we have to add a query parameter to the Product Dataset to filter the items for the @ProductSubCateg parameter. As an outcome, this displays dataset Changes.

7. Lastly, a report preview is shown here; click the parameter options to hide the fields. And now the cascaded option looks like this.

preview of a report is shown here

Conclusion

As we’ve seen in this article, a parameter’s list of values is influenced by the values specified for the last parameter. Therefore, this is helpful when a parameter has a long set of results, and the attributes may be sorted based on the past parameter.

Recommended Articles

We hope that this EDUCBA information on “SSRS Cascading Parameters” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. SSRS Versions
  2. Ansible SSH Key
  3. Data Science Lifecycle
  4. Data Science Applications

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW