Progress® Telerik® Reporting R3 2017

How to: Cascading Parameters with applied filtering on data source level

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

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 data source based on the ProductCategoryID 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.