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

1. 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.

2. 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.

3. This invokes the Report Parameter Editor.
4. Name the new parameter ProductSubcategoryID.
5. Set the Text to Product SubCategory.
6. Set the Type of the parameter to Integer.
7. Set the Visible property to True.
8. Expand the AvailableValues.

9. 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.

10. 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.

11. This invokes the Report Parameter Editor.
12. Name the new parameter ProductCategoryID.
13. Set the Text to Product Category.
14. Set the Type of the parameter to Integer.
15. Set the Visible property to True.
16. Expand the AvailableValues.

17. Start the Data Source Wizard and set the DataSource for the parameter to the following query:

    SELECT
        ProductCategoryID,
        Name AS CategoryName
    FROM
        Production.ProductCategory
    

18. Click Next and Finish the Data Source Wizard.

19. Set the DisplayMember to = Fields.CategoryName column.
20. Set the ValueMember to = Fields.ProductCategoryID.
21. It is not compulsory to set the DataMember property when the data source contains only one table.
22. Click OK to close the Report Parameter Editor.
23. Click Next and Finish the Data Source Wizard for the ProductCategoryID parameter.
24. Select the Report Parameter Editor for the ProductSubcategoryID parameter.
25. Set the DisplayMember to = Fields.SubcategoryName column.
26. Set the ValueMember to = Fields.ProductSubcategoryID.
27. It is not compulsory to set the DataMember property when the data source contains only one table.
28. Click OK to close the Report Parameter Editor.
29. Click Next and Finish the Data Source Wizard for the ProductSubcategoryID parameter.
30. Preview the report. Use the Product Category and Product Subcategory parameters to filter the list of products shown in the report.