Telerik Reporting
Contents

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:
    CopySQL
    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:
    CopySQL
    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:
    CopySQL
    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.