Telerik Reporting R1 2017

How to: Cascading Parameters with applied filtering on Report level

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

  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.