Cascade Parameters with Applied Filtering on Data Source Level
To create cascading report parameters with applied filtering on data source level follow the steps below:
Cascading Parameters with applied filtering on Datasource level
-
Using the Data Source Wizard, bind the report to SqlDataSource with query:
SELECT Production.Product.ProductNumber, Production.Product.Name AS ProductName, Production.ProductSubcategory.Name AS SubcategoryName FROM Production.Product INNER JOIN Production.ProductSubcategory ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID WHERE (Production.Product.ProductSubcategoryID = @ProductSubcategoryID)
Note that there is a WHERE clause that filters the datasource based on the ProductSubcategoryID parameter.
Click the Next button and the "Configure Data Source Parameters" step of the SqlDataSource appears. Set the DbType of the ProductSubcategoryID parameter to Int32 and select "New Report Parameter" for the Value.
- This invokes the Report Parameter Editor.
- Name the new parameter ProductSubcategoryID.
- Set the Text to Product SubCategory.
- Set the Type of the parameter to Integer.
- Set the Visible property to True.
- Expand the AvailableValues.
-
Start the Data Source Wizard and set the DataSource for the parameter to the following query:
SELECT ProductSubcategoryID, Name AS SubcategoryName FROM Production.ProductSubcategory WHERE (ProductCategoryID = @ProductCategoryID)
Note that there is a WHERE clause that filters the datasource based on the ProductSubcategoryID parameter.
Click the Next button and the "Configure Data Source Parameters" step of the SqlDataSource appears. Set the DbType of the ProductCategoryID parameter to Int32 and select "New Report Parameter" for the Value.
- This invokes the Report Parameter Editor.
- Name the new parameter ProductCategoryID.
- Set the Text to Product Category.
- Set the Type of the parameter to Integer.
- Set the Visible property to True.
- Expand the AvailableValues.
-
Start the Data Source Wizard and set the DataSource for the parameter to the following query:
SELECT ProductCategoryID, Name AS CategoryName FROM Production.ProductCategory
Click Next and Finish the Data Source Wizard.
- Set the DisplayMember to = Fields.CategoryName column.
- Set the ValueMember to = Fields.ProductCategoryID.
- It is not compulsory to set the DataMember property when the data source contains only one table.
- Click OK to close the Report Parameter Editor.
- Click Next and Finish the Data Source Wizard for the ProductCategoryID parameter.
- Select the Report Parameter Editor for the ProductSubcategoryID parameter.
- Set the DisplayMember to = Fields.SubcategoryName column.
- Set the ValueMember to = Fields.ProductSubcategoryID.
- It is not compulsory to set the DataMember property when the data source contains only one table.
- Click OK to close the Report Parameter Editor.
- Click Next and Finish the Data Source Wizard for the ProductSubcategoryID parameter.
- Preview the report. Use the Product Category and Product Subcategory parameters to filter the list of products shown in the report.