Export (0) Print
Expand All

Lesson 4: Adding Cascading Parameters (Report Builder 2.0)

SQL Server 2008

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.

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).

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft