Delen via

  • Artikel

Lesson 4: Adding Cascading Parameters (SSRS)

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. Order is important for cascading parameters because the dataset query for a parameter later in the list includes references to parameters earlier in the list.

In this lesson, you will create a report with a main dataset query thatdefines three query parameters for category, subcategory, and product. You will define three additional datasets to provide available values for each cascading parameter.

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 CascadingParameters.rdl, and then click Add.

    Report Designer opens and displays the new .rdl file in Design view.

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_Ref

  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 query and query parameters

  1. In the Report Data pane, right-click the data source AdventureWorks_Ref, and click Add Dataset.

  2. In Name, type SalesbyCategory.

  3. In Data source, verify that AdventureWorks_Ref is selected.

  4. In Query type, verify that Text is selected.

  5. Below the query pane, click Query Designer.

  6. Click Edit as Text to switch to the text-based query designer.

  7. Paste the following query:

    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.[BusinessEntityID] = 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)
         AND PSC.Name = (@Subcategory)
         AND P.Name = (@Product))

    The query now includes the query parameters @Category, @Subcategory, and @Product.

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

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

    Parameter Name

    Parameter Value

    @Category

    Components

    @Subcategory

    Brakes

    @Product

    Front Brakes

  10. Click OK. 

    The result set contains a list of sales order numbers, grouped by date, for front brakes.

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

  11. (Optional) In the Report Data pane, expand the Parameters node, and verify the following report parameters appear: Category, Subcategory, and Product.

  12. (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 @Category, @Subcategory, and @Product.

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

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

Next, you will create a dataset to provide values for each report parameter at run time. The values will populate the available values and also provide default values so that the report runs automatically.

To define a valid values dataset for a report parameter

  1. In the Report Data pane, right-click AdventureWorks_Ref, and then click Add Dataset.

  2. In Name, type CategoryValues.

  3. In Data source, verify AdventureWorks_Ref is selected.

  4. In Query type, verify Text is set.

  5. Beneath the Query pane, click Query Designer.

  6. Click Edit as Text to switch to the text-based query designer.

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

    SELECT DISTINCT Name AS Category FROM Production.ProductCategory

    The SELECT DISTINCT command retrieves only unique values from a column.

  8. Click Run (!) to see the result set. The column Category appears with four values: Accessories, Bikes, Clothing, and Components.

  9. Click OK.

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

To set available values and default values for a report parameter

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

  2. In Name, verify that the name is Category.

  3. Click Available Values.

  4. Click Get values from a query. Three fields appear.

  5. In Dataset, from the drop-down list, select CategoryValues.

  6. In Value field, click Category.

  7. In Label field, click Category.

  8. Click Default Values.

  9. Click Get values from a query.

  10. In Dataset, from the drop-down list, select CategoryValues.

  11. In Value field, select Category.

  12. Click OK.

Next, you will modify the parameter @Subcategory to depend on the value selected for @Category.

To add the values dataset for the report parameter Subcategory

  1. In the Report Data pane, right-click AdventureWorks_Ref, and then click Add Dataset.

  2. In Name, type SubcategoryValues.

  3. 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 = (@Category)

  4. Click OK. 

  5. The dataset SubcategoryValues appears in the Report Data pane. If you open the query designer, run the query, and enter Components as the category, the result set displays 14 rows.

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 Get values from a query.

  9. In Dataset, from the drop-down list, click SubcategoryValues.

  10. In Value field, click Subcategory.

  11. Click OK.

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

To add the values dataset for the report parameter Product

  1. In the Report Data pane, right-click AdventureWorks_Ref, and click Add Dataset.

  2. In Name, type ProductValues.

  3. 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 = (@Category)
   AND PSC.Name = (@Subcategory))

  4. Click OK.

    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. Click Available Values.

  3. Click Get values from a query.

  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.

  8. Click Get values from a query.

  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 choosing a value for each cascading parameter.

To add a table to display the results

  1. In Design view, add a Table.

  2. In the Report Data pane, from the SalesbyCategory dataset, drag the following fields to the 3 cells in the details row for the table: SalesOrderNumber, OrderQty, LineTotal.

  3. Drag Category from the SalesbyCategory dataset to the RowGroups pane and drop it above the Details group.

  4. Drag Subcategory from the SalesbyCategory dataset to the Row Groups pane and drop it underneath Category.

  5. Drag Product from the SalesbyCategory dataset to the Row Groups pane and drop it underneath Subcategory.

  6. Drag OrderDate from the SalesbyCategory dataset to the Row Groups pane and drop it underneath Product.

  7. (Optional) Format the following cells: [LineTotal] as Currency, [OrderDate] as Date.

To test the cascading parameters

  1. Click Preview.

    The report runs automatically because you have set the default for each report parameter.

  2. From the Category drop-down list, select Components.

  3. From the Subcategory drop-down list, select Brakes.

  4. From the Product drop-down list, select Front Brakes.

    Notice that as you select each successive parameter, the drop-down list for the next parameter shows only the valid values that are based on your previous choices.

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

The report displays sales order numbers with order quantity and line totals for orders that include the "front brakes" product. The table displays the sales orders organized by category, subcategory, product, and order date.

Next Steps

You have successfully created a report that displays sales orders that include a specific product, by using cascading parameters that filter by product category, subcategory, and product name. 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 (SSRS).