Cascading parameters provide a way of managing large
amounts of report data. You can define a set of related parameters so
that the list of values for one parameter depends on the value chosen in
another parameter. For example, the first parameter is independent and
might present a list of product categories. When the user selects a
category, the second parameter is dependent on the value of the first
parameter. Its values are updated with a list of subcategories within
the chosen category. When the user views the report, the values for both
the category and subcategory parameters are used to filter report data.
In this post we will walk through the steps of building a report and with query parameters for both product category and subcategory items. Then we will develop individual datasets to provide values for the cascading parameters.
STEP 1:
Open your existing Report Server project or create a new Report Server project. Add a new report and rename it as CascadingParameters.rdl. Now create a Data Source for AdventureWorksDW2008R2 database. For more information about creating shared Data Source, Click Creating Shared Data Source in SSRS 2008.
STEP 2:
Create three data sets as mentioned below:
1. dsProductCategory: This Data Set will be used to create a parameter for Product Categories. Use below query for this data set:
SELECT ProductCategoryID,Name AS ProductCategory
2. dsProductSubcategory: This Data Set will be used to create a Cascading Parameter for Product Subcategories. Use below stored procedure for this data set.
CREATE PROC procProductSubcategory
(
@ProductCategoryID varchar(1000)
)
AS
BEGIN
SELECT ProductSubcategoryID,Name AS ProductSubcategory
FROM Production.ProductSubcategory (NOLOCK)
WHERE ProductCategoryID IN (SELECT Value
FROM [dbo].[SplitMultivaluedString](@ProductCategoryID,','))
ORDER BY Name
END
-------- UNIT TESTING ------------------
-- EXEC procProductSubcategory '1'
-- EXEC procProductSubcategory '1,2'
-- EXEC procProductSubcategory '1,3'
----------------------------------------
GO
CREATE PROC procProductsList
(
@ProductSubcategoryID varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
SELECT
PC.Name ProductCategory
,PS.Name ProductSubcategory
,P.Name ProductName,ProductNumber
,Color,Size,[Weight]
FROM Production.Product P (NOLOCK)
INNER JOIN Production.ProductSubcategory PS
ON PS.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE P.ProductSubcategoryID IN (SELECT Value
FROM [dbo].[SplitMultivaluedString](@ProductSubcategoryID,','))
END
-- EXEC procProductsList '6,7,8,18,30,36'
GO
STEP 3:
Thats all. We are done with a report having one cascading parameter. Click on Preview to view the report. Now you can see that values of Parameter Product Subcategory depends on the parameter Product Category. By Default, Product Category parameter has (Selected All) so Product Subcategory will also display all the values.
In this post we will walk through the steps of building a report and with query parameters for both product category and subcategory items. Then we will develop individual datasets to provide values for the cascading parameters.
I will use AdventureWorks2008R2 database for this article. You can install this database from codeplex.
STEP 1:
Open your existing Report Server project or create a new Report Server project. Add a new report and rename it as CascadingParameters.rdl. Now create a Data Source for AdventureWorksDW2008R2 database. For more information about creating shared Data Source, Click Creating Shared Data Source in SSRS 2008.
STEP 2:
Create three data sets as mentioned below:
1. dsProductCategory: This Data Set will be used to create a parameter for Product Categories. Use below query for this data set:
SELECT ProductCategoryID,Name AS ProductCategory
FROM Production.ProductCategory (NOLOCK)
ORDER BY Name
ORDER BY Name
CREATE PROC procProductSubcategory
(
@ProductCategoryID varchar(1000)
)
AS
BEGIN
SELECT ProductSubcategoryID,Name AS ProductSubcategory
FROM Production.ProductSubcategory (NOLOCK)
WHERE ProductCategoryID IN (SELECT Value
FROM [dbo].[SplitMultivaluedString](@ProductCategoryID,','))
ORDER BY Name
END
-------- UNIT TESTING ------------------
-- EXEC procProductSubcategory '1'
-- EXEC procProductSubcategory '1,2'
-- EXEC procProductSubcategory '1,3'
----------------------------------------
GO
Note: Function SplitMultivaluedString is used to split comma seperated values of parameter @ProductCategoryID. Click Function to Split Multi-valued Parameters to get T-SQL script of this function.
Result of EXEC procProductSubcategory '1,3' is shown below:
3. dsProduct:
This data set will give the list of all the products based on the
Product SubCategories selected at run time. Use below stored procedure
for this data set:
CREATE PROC procProductsList
(
@ProductSubcategoryID varchar(1000)
)
AS
BEGIN
SET NOCOUNT ON
SELECT
PC.Name ProductCategory
,PS.Name ProductSubcategory
,P.Name ProductName,ProductNumber
,Color,Size,[Weight]
FROM Production.Product P (NOLOCK)
INNER JOIN Production.ProductSubcategory PS
ON PS.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory PC
ON PC.ProductCategoryID = PS.ProductCategoryID
WHERE P.ProductSubcategoryID IN (SELECT Value
FROM [dbo].[SplitMultivaluedString](@ProductSubcategoryID,','))
END
-- EXEC procProductsList '6,7,8,18,30,36'
GO
Result of EXEC procProductsList '6,7,8,18,30,36' is shown below:
STEP 3:
Once you create above data sets, you can see two parameters created automatically - ProductCategoryID and ProductSubcategoryID as shown below:
Now double click on parameter ProductCategoryID to open Report Parameter Properties window. In General, change the Prompt value as Product Category and check Allow multiple values checkbox. In Available Values, Select Get values from a query option and select dsProductCategory in Dataset, ProductCategoryID in Value field and ProductCategory in Label field. Now click on Default Values, Click on Get values from a query option and select dsProductCategory in Dataset, ProductCategoryID in Value field. Finally click OK to save changes.
Similarily change the properties of parameter ProductSubcategoryID. In General, change the Prompt value as Product Subcategory and check Allow multiple values checkbox. In Available Values, Select Get values from a query option and select dsProductSubcategory in Dataset, ProductSubcategoryID in Value field and ProductSubcategory in Label field. In Default Values, Click on Get values from a query option and select dsProductSubcategory in Dataset, ProductSubcategoryID in Value field.
Finally click OK to save changes.
STEP 4:
In report Design, drag and drop a table control and map with dsProduct data set. select all the columns from dsProduct in the tables and format this as shown below:
Thats all. We are done with a report having one cascading parameter. Click on Preview to view the report. Now you can see that values of Parameter Product Subcategory depends on the parameter Product Category. By Default, Product Category parameter has (Selected All) so Product Subcategory will also display all the values.
Values of Product Subcategory parameter will be filtered as per selection of the values of Product Category parameter, which will filter the actual report data because Product Subcategory parameter is used to filter report data.
