Lesson 4: Adding Cascading Parameters (Report Builder 2.0)

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.

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. 

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.

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.

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.

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.

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.

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.