Cascading Parameters in SSRS 2008

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.

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

 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

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.

Report Parameters in SSRS 2008

What is Report Parameters in SSRS?
In SSRS, parameters are used to specify the data to use in a report, narrow down your report data for better analysis, connect related reports together, and vary report presentation. Report parameters can be Single-Valued and Multivalued.

Using Parameters in Reporting Services:
The most common use of parameters is to vary report data retrieved by dataset queries. In this scenario, users are prompted for a value or values when they run the report, and the dataset query retrieves only the data that is requested. You can change the report parameter properties in the report design tools to include a valid values list that displays at run time.

You can also create cascading parameters, which retrieve hierarchical data from a data source. I'll explain cascading parameters in my nect post. First I want to explain creating a report parameter in SSRS 2008.

Below are the uses of report parameters:

• To Control Report Data - by selecting required parameters values to filter the report data.
• To Control Report Appearance - use parameters to change report appearance using expression-based properties, including conditionally hiding report items and conditionally changing text color.
• To Connect to Other Reports - use parameters to link to drillthrough reports, subreports, and linked reports.
• To Select Specific Data Columns - parameters can be used to select specific columns of a table/matrix at run time.

How to create Report Parameters:
Query parameters are added to a dataset query by way of the query designers or the Dataset Properties dialog box. After you create a query with parameters, Reporting Services automatically links query parameters to report parameters with the same name. Below are the steps to create parameters:

STEP 1:
To begin, start a new Report Server Project project in Visual Studio 2008 by clicking on Start --> All Programs --> SQL Server 2008 --> SQL Server Business Intelligence Development Studio. Then, from the menu, select File --> New --> Project to open New Project wizard. Now select Report Server Project from Visual studio installed templates and specify Name, Location and Solution Name.

I'll use the solution ctreated in my previous article. I'll use FirstReport.rdl for this example and I'll create a parameter Designation to filter report data for selected designations.

STEP 2:
Create a new Data Set for the report parameter. I will add a dataset dsDesignation for Employee's Designation using query
SELECT DISTINCT Desg AS Designation FROM Employee
as shown below:

STEP 3:
Now I will add a parameter Designation. In Report Data section, right click on Parameters node and click Add Parameter... as shown below:

STEP 4:
In Report Parameter Properties window, enter Name and Prompt of the parameter and select the Data type from dropdown box. For our example, enter Designation in Name and Prompt text boxes and select Text as data type.

STEP 4:
Now click on Avaliable Values to set available values for the parameter. Select Get values from a query. Select dsDesignation as Dataset, Designation as Value field and Label field.

STEP 5: Click on Default Values, Select Get values from a query. Select dsDesignation as Dataset, Designation as Value field. Click OK to save changes.

STEP 6:
Double click on your main dataset (dsMain) to open Dataset Properties. Now Select  Parameters and click on Add button to map Designation parameter.

STEP 7:
Now final step, just add parameter in your query as shown in below snapshot:
WHERE [Desg] = @Designation

Now preview the report to check the action of parameter used:

Note: This article describes onlu about single valued parameter. I'll explain multi-valued parameter in my next article.