In this blog, we will explain how to add a cascading parameter to your report. Before getting into the steps, let’s look at what cascading parameters are.
Cascading parameters means one parameter value will be filtered depending on the value chosen in another parameter.
In this example, we have two parameters the first parameter, Product Category is independent and has a list of product categories the second parameter, Product Subcategory is dependent on the value of the first parameter. When you choose a product category then the Product Subcategory parameter list has only those subcategories that belong to the selected product category.
When we view the report, the values for both the category and subcategory parameters are used to filter report data.
To create cascading parameters first, you define the data source and data set query and then include a query parameter for each cascading parameter that you need.
Add a data source for the report
To create a new data source for the report, click the data icon in the configuration panel on the right side of the Report Designer.
In the Data sources configuration panel, click the New Datasource button. Now, the list of supported data sources will be displayed. In that, click the Shared data provider connection.
Enter the data source name AdventureWorks and choose the AdventureWorks database from the drop-down. Then, click Save.
Create data set ProductCategory
Create a data set named ProductCategory in the report for providing data to the parameter Product Category. Paste the following query into the query designer. The query runs against the AdventureWorks sample database.
SELECT ProductCategoryID, Name AS ProductCategory FROM Production.ProductCategory ORDER BY Name
Click Finish to create the data set.
Create data set for ProductSubCategory
Create a data set named ProductSubCategory in the report for providing data to a cascading parameter for product subcategories. Paste the following query into the query designer. The query runs against the AdventureWorks sample database.
SELECT ProductSubcategoryID, ProductCategoryID, Name as [ProductSubcategory] FROM Production.ProductSubcategory WHERE (ProductCategoryID = @ProductCategory)
Click Finish to create the data set.
Create data set Product
Create a data set named Product in the report for providing filter data based on the product categories and product subcategories selected at run time in parameters. Paste the following query into the query designer. The query runs against the AdventureWorks sample database.
SELECT P.Name,P.ProductNumber, P.Size,P.Weight,PS.Name AS [ProductSubcategory], PC.Name AS [ProductCategory] FROM Production.Product P INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID=PS.ProductSubcategoryID INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID=PC.ProductCategoryID WHERE (PC.ProductCategoryID = @ProductCategory) AND (PS.ProductSubcategoryID IN (@ProductSubcategory))
Click Finish to create the data set
Now the parameters ProductCategory and ProductSubCategory will be created under the parameter panel
Assign the values to Product Category parameter
Click the Parameters pane to edit a report parameter, hover over the ProductCategory parameter, and click Edit.
Fill the Prompt with the parameter name Product Category. For better readability, we can change the parameter prompt text to two words, Product Category, to display it in the report view.
Click Assign Values in the Parameter panel. Select Query Value under the Available Value tab then choose ProductCategory from the data set drop-down. Select ProductCategoryID in the Value field and ProductCategory in the Label field from the drop-downs.
Then, click OK and save it.
Assign values to cascade parameter ProductSubCategory
Click the Parameter pane to edit a report parameter, hover over the ProductSubCategory parameter, and click Edit. Specify the parameter Prompt as Product Subcategory to display it in the report view with better readability.
Click Assign Values in the Parameter pane and Select Query Value under the Available Value tab, then choose ProductCategory from the data set drop-down.
Select ProductSubCategoryID in the Value field and ProductSubcategory in the Label field from the drop-down
Then, click OK and save it.
Add a table to report design
Drag a table report item from the item panel into the design surface.
Refer to the insert column and set header text documentation to add five columns to the table and set the header text as Product Category, Product Subcategory, Product Name, Product Number, Product Size, and Weight.
To customize the header appearance, select the header row, and open the properties panel. The common properties of table cells will be listed here. Customize them according to your requirement.
Refer to the assign data documentation, assign the data set Product from the data set drop-down, and add the appropriate data fields to the table cells.
We can preview the report by toggling from design to preview. The first parameter, Product Category, is independent and presents a list of product categories. The second parameter, Product Subcategory, is dependent on the value of the first parameter.
Now, let’s select the Product Category parameter value of Bikes from the drop-down. The cascading parameter Product Subcategory list values are updated based on the value Bikes chosen for the Product Category and it displays only models available in the Bikes category
Let’s select the subcategory Road Bikes in the drop-down. Click View Report.
Now the selected values of the Product Category and Product Subcategory parameters filter report data as shown in the table view.
Now, we can publish this designed report to the report server.
Congratulations! You have successfully added a cascading parameter to a report with the most common use case. You can download this report design here for your reference.
I hope in this blog you have learned how to add cascading parameters to reports and display parameter filter data in a report using Bold Report Designer. To explore further, I recommend you go through our sample reports and documentation.
If you have any questions about this feature, let us know in the comments section below. You can also contact us through the Bold Reports contact page or, if you have an account already, you can log in and submit your questions. Stay tuned to our official Twitter, Facebook, LinkedIn, Pinterest, and Instagram pages for announcements and blog post updates.