Working with Cascading Parameters in Reporting Services

Cascading parameters are used to organize and limit the number of available values for the user. For example, you may have one parameter that filters a product category, and the second parameter used to list products related to product category. 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. We will be using Report Builder 2.0 for this demo, but you may use the concepts presented below, while designing a report using BIDS.

This article assumes you have the following:

  • SQL Server 2008 and Reporting Services installed
  • Business Intelligence Development Studio (BIDS) or Report Builder 2.0
  • AdventureWorks database
  • Experience working with Reporting Services

Create report

1) Open Report Builder 2.0 from Start >> All Programs >> Microsoft SQL Server 2008 Report Builder 2.0.

2) From the upper left corner, click the red ribbon button, click Save As.

3) From within the left pane, select Recent Sites and Servers. Navigate to the location of where you would like to save your report.

4) Enter a Name for your report and click OK to save and close the Save As Report window. You should see a similar view.

reporting builder 2.0

Create Data Source

For this section, you may follow the steps from Introduction to Report Builder 3.0 Part 2 Creating a Report. This site will provide details along with images.

1) From the Report Data pane, create a new data source; select an existing shared data source.

2) You will be using the AdventureWorks sample database for this demo, please select this db as the source.

3) Click OK to save and close the Data Source Properties window.

Create Data Set and Parameters

1) From within the Report Data pane, select New >> Data Set

2) Make sure the Query item is selected in the left pane of the Dataset Properties window. Enter "TerritorySales" in the Name field.

3) Select "AventureWorks" from the Data Source dropdown, or the name you gave your data source.

4) Copy and paste the following T-SQL script to the Query textbox.

SELECT ST.Name AS Territory, SUM(DET.LineTotal) AS SalesAmount
FROM Sales.SalesPerson SP
    INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID
    INNER JOIN Sales.SalesOrderDetail DET ON SOH.SalesOrderID = DET.SalesOrderID
    INNER JOIN Sales.SalesTerritory ST ON SP.TerritoryID = ST.TerritoryID
    INNER JOIN Production.Product P ON DET.ProductID = P.ProductID
    INNER JOIN Production.ProductSubcategory PS ON P.ProductSubcategoryID = PS.ProductSubcategoryID      
    INNER JOIN Production.ProductCategory PC ON PS.ProductCategoryID = PC.ProductCategoryID
WHERE (PC.ProductCategoryID = @ProductCategory)
AND(PS.ProductSubcategoryID IN(@ProductSubcategory))
AND(SOH.OrderDate > @StartDate) AND(SOH.OrderDate < @EndDate)
GROUP BY ST.Name
ORDER BY SUM(DET.LineTotal) DESC

 You should have a similar view below

reporting builder 2.0

 5) Click OK to save changes and close the window.

6) From within the Report Data pane, expand the Parameters node. You'll notice several new parameters, as shown below.

reporting builder 2.0

Set Parameter Data Types and Default Values

1) From within the Report Data pane, right click @StartDate and select Parameter Properties.

2) From within the left pane of the Report Parameter Properties window, make sure General is selected.

3) Under Data Type, select Date/Time.

reporting builder 2.0

4) From within the left pane of the Report Parameter Properties window, select Default Values.

5) Select the Specify Values option.

6) Click Add.

7) Enter in 2001-01-01 and click OK.

reporting builder 2.0

8) Repeat the same steps for the @EndDate parameter, but set the default value to 2004-07-01

You are finished with setting the data types and default values.

Create Product Category Dataset and set Parameter values

1) From within the Report Data pane, select New >> Dataset...

2) Make sure the Query item on the left pane is selected; Enter ProductCategoryList.

3) Select the AdventureWorks from the Data Source dropdown list.

4) Copy and paste the following T-SQL script into the Query textbox:

SELECTDISTINCT PSC.ProductCategoryID AS ProductCategoryID, PSC.Name AS Category

FROM Production.ProductCategory AS PSC

      INNERJOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID 

 

 5) Click OK to save your changes and close the Datset Properties window.

 The ProductCategoryList dataset should be visible in the Report Data pane.

Set the Product Parameter available and default values

1) From within the Report Data pane, expand the Parameters folder and right-click @ProductCategory, and then click Parameter Properties.

2) Check "Allow Multiple Values"

3) Click Available Values from the left pane.

4) From the right pane, select “Get values from a query” option.

5) For the Dataset value, select ProductCategoryList from the dropdown list.

6) Value field = ProductCategoryID.

7) Label field = Product.

6) Click OK.

Create Product Subcategory Dataset and set Parameter values

1) From within the Report Data pane, select New >> Dataset...

2) Make sure the Query item on the left pane is selected; Enter ProductSubcategoryList.

3) Select the AdventureWorks from the Data Source dropdown list.

4) Copy and paste the following T-SQL script into the Query textbox:

SELECT DISTINCT ProductSubcategoryID AS ProductSubcategoryID, PSC.Name AS Subcategory

FROM Production.ProductSubcategory AS PSC

      INNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID

WHERE PSC.ProductCategoryID = @ProductCategoryID

5) Click OK to save your changes and close the Datset Properties window.

 The ProductSubcategoryList dataset should be visible in the Report Data pane.

Set the ProductSubcategory Parameter available values

1) From within the Report Data pane, expand the Parameters folder and right-click @ProductSubcategory, and then click Parameter Properties. Click Available Values from the left pane.

2) From the right pane, select “Get values from a query” option.

3) For the Dataset value, select ProductSubcategoryList from the dropdown list.

4) Value field = SubcategorySubcategoryID.

5) Label field = Subcategory.

6) Click OK.

7) Click Run, from the ribbon above the Report Data pane.

You should see the following

 

8) Select an item from the Product Category dropdown and notice how Product Subcategory is populated with values.

Next, you could go back to design mode and add a Table or Matrix to the report to render data.

Cheers!

 

Pingbacks and trackbacks (1)+

Comments are closed