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:
Step 1: Create a data source process connection.
After including a data source, the connection process is given here.
Step 2: Creating a new database.
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.
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.
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.
Step 6: Last Step- Report Preview.
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:
2. The “Main Dataset” has been made. Because this dataset has three query parameters defined, Reporting Services built three Report parameters automatically.
3. Taking a Dataset Query by creating three new datasets. First, let’s see the Parameter Dataset for a Product.
4. Dataset for Product Category options.
Similarly, we should do for the Sub-category parameter. After creating three datasets, the image would look like this:
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.
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.
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.
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.
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 –
