EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials SSRS Tutorial SSRS Cascading Parameters
Secondary Sidebar
SSRS Tutorial
  • SSRS Basic and Advanced
    • What is SSRS?
    • SSRS Versions
    • SSRS Cascading Parameters
    • SSRS Parameter
    • SSRS Group
    • SSRS Alternate Row Color
    • SSRS SQL
    • SSRS Subreport
    • SSRS Lookup
    • SSRS LookUpSet
    • SSRS Date Format
    • SSRS Report Builder
    • SSRS ISNULL
    • SSRS Opinion Panel
    • SSRS linked report
    • SSRS DateDiff
    • SSRS Dashboard
    • SSRS IIF
    • SSRS Reports
    • SSRS Number Format
    • SSRS Page Break
    • SSRS multi-value parameter

SSRS Cascading Parameters

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.

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:

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Step 1: Create a data source process connection.

SSRS Cascading Parameters 1

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

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,629 ratings)

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

This is a guide to SSRS Cascading Parameters. Here we discuss the introduction, create SSRS cascading parameters, and configure. You may also have a look at the following articles to learn more –

  1. SSRS Versions
  2. Ansible SSH Key
  3. Data Science Lifecycle
  4. Data Science Applications
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more