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

  • Article

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
  1. Click OK.
  2. (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
  1. 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

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

 

  1. 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:
  2. Click View Report, you will get below report:

 

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

 

  1. 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:
  2. Click View Report, you will get below report:

 

Reference:

Cascading Parameters

 

[Download Sample]