Walkthrough: Implement Optional Cascading Parameters with "" in Reporting Services

By default, if we create cascading Parameters, Reporting Service will force us to select one value for every parameter, otherwise Report will not run. Sometimes, our users need to select the cascading parameters optionally, they can choose whichever they like or just leave it be <Select All>, if there is only one available value in the list for one parameter depends on the value chosen in preceding parameter, this available value still need to be selected as its default value automatically.

This article will walk you through the processes of implementing Optional Cascading Parameters in Reporting Services. The sample in this article uses the sample AdventureWorks database. The report displays order quantity and line totals organized by category, subcategory and product.  

To add a new report to an open report server project

1. In Solution Explorer, right-click Reports, point to Add, and click New Item.
2. In the Add New Item dialog box, under Templates, click Report.
3. In Name, type OptionalCascadingParameters.rdl, and then click Add. 

To create a reference to a shared data source

1. In the Report Data pane, click New, and then click Data Source.
2. In Name, type AdventureWorks
3. Select Use shared data source reference.
4. From the drop-down list, select AdventureWorks.
5. Click OK. 

To create the main dataset with a Store Procedure

1. In the Report Data pane, right-click the data source AdventureWorks, and click Add Dataset.
2. In Name, type SalesbyCategory.
3. In Data source, verify that AdventureWorks is selected.
4. In Query type, verify that Store Procedure is selected.
5. In drop-down list of Select or enter stored procedure name: , select SalesbyCategory which is created by following T-SQL:

USE [AdventureWorks] GO   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   Create PROCEDURE [dbo].[SalesbyCategory]       @Category varchar(256)='Select All' ,       @SubCategory varchar(256)='Select All',       @Product varchar(256) ='Select All' AS BEGIN         SET NOCOUNT ON;       SELECT    PC.Name AS Category,    PSC.Name AS Subcategory,    P.Name AS Product,    SOH.[OrderDate],    SOH.SalesOrderNumber,    SD.OrderQty,    SD.LineTotal    FROM [Sales].[SalesPerson] SP       INNER JOIN [Sales].[SalesOrderHeader] SOH       ON SP.[SalesPersonID]= SOH.[SalesPersonID]       INNER JOIN Sales.SalesOrderDetail SD       ON SD.SalesOrderID = SOH.SalesOrderID       INNER JOIN Production.Product P       ON SD.ProductID = P.ProductID       INNER JOIN Production.ProductSubcategory PSC       ON P.ProductSubcategoryID = PSC.ProductSubcategoryID       INNER JOIN Production.ProductCategory PC       ON PC.ProductCategoryID = PSC.ProductCategoryID       WHERE ((PC.Name = (@Category) or @Category='Select All' )          AND (PSC.Name = (@Subcategory) or @Subcategory='Select All')          AND (P.Name = (@Product) or @Product='Select All'))        END

6. Click OK.
7. (Optional) In the Report Data pane, expand the Parameters node, and verify the following report parameters appear: Category, Subcategory, and Product. 

To add the values dataset for the report parameter Category

1. In the Report Data pane, right-click AdventureWorks, and then click Add Dataset.
2. In Name, type CategoryValues.
3. In Data source, verify AdventureWorks is selected.
4. In Query type, verify that Store Procedure is selected.
5. In drop-down list of Select or enter stored procedure name: , select CategoryValues which is created by following T-SQL:

USE [AdventureWorks] GO   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   Create PROCEDURE [dbo].[CategoryValues] AS BEGIN   Select 'Select All' AS Category union all SELECT DISTINCT Name AS Category FROM Production.ProductCategory       END

6. Click OK. 

To set available values and default values for parameter Category

1. In the Report Data pane, in the Parameters folder, right-click Category, and then click Parameter Properties.
2. Click Available Values tab.
3. Click Get values from a query option. Three fields appear.
4. In Dataset, from the drop-down list, select CategoryValues.
5. In Value field, click Category.
6. In Label field, click Category.
7. Click Default Values tab.
8. Click Get values from a query option.
9. In Dataset, from the drop-down list, select CategoryValues.
10. In Value field, select Category.
11. Click OK. 

To add the values dataset for the report parameter Subcategory

1. In the Report Data pane, right-click AdventureWorks, and then click Add Dataset.
2. In Name, type SubCategoryValues.
3. In Query type, verify that Store Procedure is selected.
4. In drop-down list of Select or enter stored procedure name: , select SubcategoryValues which is created by following T-SQL:

USE [AdventureWorks] GO   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   ALTER PROCEDURE [dbo].[SubcategoryValues]         @Category varchar(256)='Select All'       AS BEGIN         SET NOCOUNT ON;       Declare @Count int             Select @Count=COUNT(DISTINCT PSC.Name) FROM Production.ProductSubcategory AS PSC INNER JOIN                          Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID                         WHERE ((PC.Name = @Category) or (@Category='Select All'))         If @Count>1             begin                     select 'Select All' as SubCategory                   union all                   Select DISTINCT PSC.Name as SubCategory FROM Production.ProductSubcategory AS PSC INNER JOIN                                                  Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID                          WHERE (PC.Name = @Category)             end         else             Select DISTINCT PSC.Name as SubCategory FROM Production.ProductSubcategory AS PSC INNER JOIN                                                  Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID                         WHERE (PC.Name = @Category)           END

5. Click OK.

To set available values and default values for parameter Subcategory

1. In the Report Data pane, in the Parameters folder, right-click Subcategory, and then click Parameter Properties.
2. Click Available Values tab.
3. Click Get values from a query option.
4. In Dataset, from the drop-down list, click SubcategoryValues.
5. In Value field, click Subcategory.
6. In Label field, click Subcategory.
7. Click Default Values tab.
8. Click Get values from a query option.
9. In Dataset, from the drop-down list, click SubcategoryValues.
10. In Value field, click Subcategory.
11. Click OK.

To add the values dataset for the report parameter Product

1. In the Report Data pane, right-click AdventureWorks, and then click Add Dataset.
2. In Name, type ProductValues.
3. In Query type, verify that Store Procedure is selected.
4. In drop-down list of Select or enter stored procedure name: , select ProductValues        which is created by following T-SQL:

USE [AdventureWorks] GO   SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO   ALTER PROCEDURE [dbo].[ ProductValues]       @Category varchar(256)='Select All',       @SubCategory varchar(256)='Select All' AS BEGIN         SET NOCOUNT ON;       Declare @Count int             select @Count= Count(DISTINCT P.Name) FROM Production.Product AS P INNER JOIN                          Production.ProductSubcategory AS PSC ON P.ProductSubcategoryID = PSC.ProductSubcategoryID INNER JOIN                          Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID                          WHERE (PC.Name = @Category or @Category='Select All' ) AND (PSC.Name = @Subcategory or @Subcategory ='Select All')     If @Count>1             BEGIN             SELECT 'Select All' AS Product             UNION ALL             SELECT DISTINCT P.Name AS Product FROM  Production.Product AS P INNER JOIN                                            Production.ProductSubcategory AS PSC ON P.ProductSubcategoryID = PSC.ProductSubcategoryID INNER JOIN                                            Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID             WHERE (PC.Name = @Category) AND (PSC.Name = @Subcategory )         END         ELSE             BEGIN             SELECT DISTINCT P.Name AS Product FROM Production.Product AS P INNER JOIN                                            Production.ProductSubcategory AS PSC ON P.ProductSubcategoryID = PSC.ProductSubcategoryID INNER JOIN                                            Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID             WHERE (PC.Name = @Category ) AND (PSC.Name = @Subcategory)                       END       END

5. Click OK. 

To set available values and default values for parameter Product

1. In the Report Data pane, in the Parameters folder, right-click Product, and then click Parameter Properties.
2. Click Available Values tab.
3. Click Get values from a query option.
4. In Dataset, from the drop-down list, click ProductValues.
5. In Value field, click Product.
6. In Label field, click Product.
7. Click Default Values tab.
8. Click Get values from a query option.
9. In Dataset, from the drop-down list, click ProductValues.
10. In Value field, click Product.
11. Click OK.

Next, add a table so you can see the effect of optional cascading parameter.

To add a table to display the results

1. In Design view, add a Table with two columns.
2. In the Report Data pane, from the SalesbyCategory dataset, drag the following fields to the 2 cells in the details row for the table: OrderQty, LineTotal.
3. Change their expression to =SUM(Fields!OrderQty.Value) and =SUM(Fields! LineTotal.Value) separately.
4. Drag Category from the SalesbyCategory dataset to the RowGroups pane and drop it above the Details group.
5. Drag Subcategory from the SalesbyCategory dataset to the Row Groups pane and drop it underneath Category.
6. Drag Product from the SalesbyCategory dataset to the Row Groups pane and drop it underneath Subcategory.
7. Right-click the Details in the Row Groups pane, and then select Group Properties.
8. Click add button, then select Product in the group’s drop-down list.
9. Click OK.
10. (Optional) Format the following cells: [LineTotal] as Currency. 

To test the optional cascading parameters

1. Click Preview, <Select All> in cascading parameters’ drop-down list is their default value as following:

 

2. From the Category drop-down list, select Components, the drop-down list for Subcategory parameter shows all the valid values that are based on Category choices, you could select whichever you like or  leave it be <Select All>, as following:
3. Click View Report, you will get below report:

 

3. Continue to select Wheels in Subcategory drop-down list, leave Product be <Select All> as following:
4. Click View Report, you will get below report:

 

4. If you select Accessories  for Category, select Locks for Subcategory, as there is only one value <Cable Lock> for product, so <Cable Lock> will be selected to replace <Select All> automatically, just as below:
5. Click View Report, you will get below report:

 

Reference:

Cascading Parameters

 

[Download Sample]

Comments

• Anonymous
  July 23, 2014
  I am having problems finding the SP 'SubcategoryValues ' and 'ProductValues'  The syntax has an ALTER statement so I can find the current procedure in the Adventureworks DB?  I am using the AdvenureWorks2012 version.  Are you using a different DB?  If so, where can I find it?