Cascade Parameters with Applied Filtering on Report Level

To create cascading report parameters with applied filtering on report level follow the steps below:

1. Using the DataSource Wizard, bind the report to SqlDataSource with query:

   SELECT
       Production.Product.ProductNumber,
       Production.Product.Name AS ProductName,
       Production.Product.ProductSubcategoryID,
       Production.ProductSubcategory.Name AS SubcategoryName
   FROM
       Production.Product
       INNER JOIN Production.ProductSubcategory
           ON Production.Product.ProductSubcategoryID = Production.ProductSubcategory.ProductSubcategoryID
   

2. Click the ellipses on the Report.ReportParameters property. This invokes the ReportParameter Collection editor.

3. Add new Report Parameter.
4. Name it ProductCategoryID.
5. Set the Type of the parameter to Integer.
6. Expand the AvailableValues.

7. Set the DataSource using the Data Source Wizard to SqlDataSource with query:

   SELECT
       ProductCategoryID,
       Name AS CategoryName
   FROM
       Production.ProductCategory
   

8. It is not compulsory to set the DataMember property when the data source contains only one table.

9. Set the DisplayMember to = Fields.CategoryName column.
10. Set the ValueMember to = Fields.ProductCategoryID.
11. Set the Text to Product Category.
12. Set the Visible property to True if needed.
13. Add new Report Parameter.
14. Name it ProductSubcategoryID.
15. Set the Type of the parameter to Integer.
16. Expand the AvailableValues.

17. Set the DataSource using the Data Source Wizard to SqlDataSource with query:

    SELECT
        ProductCategoryID,
        ProductSubcategoryID,
        Name AS SubcategoryName
    FROM
        Production.ProductSubcategory
    

18. It is not compulsory to set the DataMember property when the data source contains only one table.

19. Set the DisplayMember to = Fields.SubcategoryName column.
20. Set the ValueMember to = Fields.ProductSubcategoryID.
21. Click on the ellipsis on the Filters property.
22. Add new filter.
23. As Expression choose =Fields.ProductCategoryID.
24. As Operator choose equals(=).
25. As Value choose =Parameters.ProductCategoryID.Value.
26. Click OK.
27. Set the Multivalue to false (or to true if you want to be able to select more than one subcategory at a time).
28. Set the Text to Product Subcategory.
29. Set the Visible property to True if needed.
30. Close the ReportParameter Collection Editor.
31. Click on the ellipsis on the Filters property of the report to open the Edit Filters dialog.
32. Add new filter.
33. As Expression choose =Fields.ProductSubcategoryID.
34. As Operator choose equals(=) (or to IN operator if you have set ProductSubcategoryID parameter as multivalue parameter).
35. As Value choose =Parameters.ProductSubcategoryID.Value.
36. Click OK.
37. Preview the report. Use the Product Category and Product Subcategory parameters to filter the list of products shown in the report.