Add Cascading Parameters to your Report

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.

Requirements

As a first step, launch the Bold Reports Enterprise server site, referring to these steps to create a report, and create a new report.

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.

Preview

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.

Conclusion

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.