Lesson 4: Adding Cascading Parameters (Report Builder 2.0)

Cascading parameters provide a way of managing large amounts of report data. With cascading parameters, the list of values for one parameter depends on the value chosen in preceding parameter.

In this lesson, you will create a new report with a main dataset query that defines query parameters for subcategory and product for the clothing category. You will define two additional datasets to provide available values for each cascading parameter.

To create a new report definition file and a save it on the report server

1. Click Start, point to Programs, point to Microsoft SQL Server 2008 Report Builder, and then click Report Builder 2.0.

2. On the design surface, click Click here to add title, and then type Cascading Parameters.

3. From the Report Builder button, click Save As.

4. Click Recent Sites and Servers.

5. Click the name of the report server where you have permission to save reports.

6. In Name, type Cascading Parameters.

7. Click Save.

   You have saved a new report to the report server.

To add a data source reference

1. In the Report Data pane toolbar, click New, and then Data Source.

2. Select Use a shared connection or report model.

3. Browse to or choose the data source for the AdventureWorks2008 sample database that you created in Tutorial: Creating a Basic Table Report (Report Builder 2.0).

4. Click OK.

   You have added a reference to the data source for the AdventureWorks2008 database. 

To create the main dataset with a query and query parameters

1. In the Report Data pane toolbar, click New, and then click Dataset.

2. In Name, type SalesbyCategory.

3. Below the query pane, click Query Designer.

4. Click Edit As Text.

5. In the query pane, paste the following query:

   SELECT
     SH.OrderDate
     ,DATENAME(weekday, SH.OrderDate) as Weekday
     ,SH.SalesOrderNumber
     ,SD.OrderQty
     ,SD.LineTotal
     ,P.Name AS [Product]
     ,PS.Name AS [Subcategory]
   FROM Sales.SalesPerson SP 
     INNER JOIN Sales.SalesOrderHeader AS SH 
         ON SP.BusinessEntityID = SH.SalesPersonID
     INNER JOIN Sales.SalesOrderDetail AS SD 
        ON SH.SalesOrderID = SD.SalesOrderID
     INNER JOIN Production.Product AS P
      ON SD.ProductID = P.ProductID
     INNER JOIN Production.ProductSubcategory AS PS
      ON PS.ProductSubcategoryID = P.ProductSubcategoryID
     INNER JOIN Production.ProductCategory AS PC
      ON PC.ProductCategoryID = PS.ProductCategoryID
   WHERE PC.Name = 'Clothing' 
      AND (SH.OrderDate BETWEEN (@StartDate) AND (@EndDate))
      AND PS.Name = (@Subcategory)
      AND P.Name IN (@Product)
   

   Query parameters for @StartDate, @EndDate, @Subcategory, and @Product have been added to create the main query for a set of cascading values.

6. Click Run (!) to see the result set. The Define Query Parameters dialog box opens.

7. In the Parameter Value column, type a value for each query parameter by using the table below.

   Parameter Name	Parameter Value
   @StartDate	20010101
   @EndDate	20030101
   @Subcategory	Gloves
   @Product	Full-Finger Gloves, M

8. Click OK. 

   The result set contains a list of sales order numbers for the specified type of glove.

   When you ran the query, each query parameter produced a corresponding report parameter. Click OK twice to exit the query designer and dialog box.

9. (Optional) In the Report Data pane, expand the Parameters node, and verify that the following report parameters appear: StartDate, EndDate, Subcategory and Product.

10. (Optional) The value of each dataset query parameter is bound to a report parameter of the same name. To verify this, in the Report Data pane, right-click SalesbyCategory, and then click Dataset Properties.

    1. Click Parameters.

    2. In the column Parameter Name, verify that the names are @StartDate, @EndDate, @Subcategory, and @Product.

    3. In the column Parameter Value, verify that the values are [@StartDate], [@EndDate], [@Subcategory] and [@Product].

       These simple expressions refer to the report parameters that you see in the Report Data pane.

    Next, you will create a dataset to provide values for @Subcategory and @Product. Each new dataset will populate both the available values and the default values for the parameter.

To set date parameter data types and default values

1. In the Report Data pane, right-click StartDate, and then click Parameter Properties.

2. In Data type, select Date/Time.

3. Click Default Values.

4. Select the Specify values option.

5. Click Add.

6. In Value, type 2001-01-01

7. Click OK.

8. In the Report Data pane, right-click EndDate, and then click Parameter Properties.

9. In Data type, select Date/Time.

10. Click Default Values.

11. Select the Specify values option.

12. Click Add.

13. In Value, type 2003-01-01

14. Click OK.

The data parameter data types and default values are set.

To add the values dataset for the report parameter Subcategory

1. In the Report Data pane toolbar, click New, and then click Dataset.

2. In Name, type SubcategoryValues.

3. Click Query Designer, and then click Edit As Text.

4. Paste the following query text in the Query pane:

   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 = 'Clothing'
   

5. Click OK twice to exit the query designer and dialog box.

6. The dataset SubcategoryValues appears in the Report Data pane. The field Subcategory has 8 rows that list subcategories for the clothing category.

   Next, set the properties for the report parameter @Subcategory to use values from this query for both its available values and its default values.

To set available values and default values for report parameter Subcategory

1. In the Report Data pane, in the Parameters folder, right-click Subcategory, and then click Parameter Properties.

2. Click Available Values.

3. Click Get values from a query.

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.

8. Click Specify values.

9. Click Add.

10. In Value, type Gloves.

11. Click OK.

    Next, create a parameter @Product that depends on both the value of @Subcategory.

To add the values dataset for the report parameter Product

1. In the Report Data pane toolbar, click New, and then click Dataset.

2. In Name, type ProductValues.

3. Click Query Designer, and then click Edit As Text.

4. Paste the following query text in the Query pane:

   SELECT DISTINCT P.Name AS Product
   FROM Production.Product 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 = 'Clothing'
         AND PSC.Name = (@Subcategory))
   

5. Click OK twice to exit the query designer and dialog box.

   A dataset named ProductValues with one field named Product is added to the Report Data pane.

Next, set the properties for the parameter @Product to use values from this query for both its available values and its default values.

To set available values and default values for report parameter Product

1. In the Report Data pane, in the Parameters folder, right-click Product, and then click Parameter Properties.

2. Select Allow multiple values.

3. Click Available Values.

4. Click Get values from a query.

5. In Dataset, from the drop-down list, click ProductValues.

6. In Value field, click Product.

7. In Label field, click Product.

8. Click Default Values.

9. Click Get values from a query.

10. In Dataset, from the drop-down list, click ProductValues.

11. In Value field, click Product.

12. Click OK.

13. Click Run to preview the report.

14. From the Subcategory drop-down list, select Jerseys.

    Notice that the drop-down list for Product shows only products that are Jerseys.

15. On the report viewer toolbar, click View Report.

    The table and chart in the report display sales orders that include the products that you selected, and which are limited by the report parameters sales order date, and days of the week.

Next Steps

You have successfully created a new report that displays sales orders for specific products, by using cascading parameters that filter by subcategory. In the next lesson, you will learn to pass a parameter to a drillthrough report. See Lesson 5: Adding Parameters to Pass to a Drillthrough Report (Report Builder 2.0).

See Also

Concepts

Adding Parameters to Your Report (Report Builder 2.0)