Lesson 2: Adding Cascading Parameters to a Report

New: 17 July 2006

Cascading parameters provide a way to filter available values for each parameter in a set of parameters that have a natural relationship. For example, in the AdventureWorks database, each reseller has an address at a specific location in a city (field City). Cities are grouped into states or provinces (field StateProvince). States and provinces are grouped into countries or regions (field CountryRegion). Countries or regions are grouped into sales territories (field TerritoryGroup). You can create four cascading parameters for these fields that have a dependent order starting with the broadest category, TerritoryGroup, to the most specific, City.

When cascading parameters appear on a report toolbar, the report reader selects a value from the first parameter, which determines the available values for the next parameter, and so on. In this way, you can filter potentially thousands of choices down to a manageable number for each cascading parameter.

Cascading parameters have an implied order. When you view report parameters in Report Designer, the parameters pane lists the parameters in order. The first parameter in a list is not dependent on any other parameter. The next parameter in the list, if it is used as a cascading parameter, is dependent on the parameter before it. You can reorder parameters with the up and down arrow buttons provided in the report parameters dialog box.

In this lesson, you will add four cascading parameters (TerritoryGroup, CountryRegion, StateProvince, and City) to the Sales Orders report created in the previous tutorial. You will create a dataset for each parameter to populate its available values list. When you create a query with a query parameter for a valid values dataset, a report parameter is automatically created. Because you are creating datasets for the valid values lists from the most general (TerritoryGroup) to the most specific (Cities), you will be creating report parameters in the correct order for dependencies. For the final valid values dataset (Cities), you will learn how to create a report parameter manually and associate it with the corresponding query parameter.

After creating all the datasets, you will modify the default properties for each report parameter, and set the valid values and default values properties to point to the appropriate dataset and field.

Finally, you will modify the original dataset query to include query parameters for each report parameter. When you run the report, you will select values for each cascading parameter in turn, and see the available values for the next parameter list only those values that are valid after the first one is selected.

Procedure

To open the ResellersWorldwide report

1. In SQL Server Business Intelligence Development Studio, open the AdvancedParametersTutorial report server project created in the previous lesson.

2. In Solution Explorer, double-click on the Resellers Worldwide report. The report opens in Layout view.

3. Click the Data tab.

To add a dataset for available values for the parameter TerritoryGroup

1. From the Dataset drop-down list, click <New Dataset>. The Dataset dialog box opens.

2. On the Query tab, in the Name text box, type ValidValuesforTerritoryGroup.

3. Verify that the Data source is Resellers.

4. Verify that the Command type is Text.

5. Paste the following query into the query pane.

   SELECT distinct [Group] as SalesTerritory
      FROM [AdventureWorks].[Sales].[SalesTerritory]
   

6. Click Run (!) to see the result set. The column SalesTerritory appears with three rows: Europe, North America, and Pacific.

To add a dataset for available values for the parameter CountryRegion

1. From the Dataset drop-down list, click <New Dataset>. The Dataset dialog box opens.

2. On the Query tab, in the Name text box, type ValidValuesforCountryRegion.

3. Verify that the Data source is Resellers.

4. Verify that the Command type is Text.

5. Paste the following query in the query pane.

   SELECT Distinct CR.Name AS CountryRegion
   FROM Sales.Store AS S
       JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
       JOIN Person.Address AS A ON A.AddressID = CA.AddressID
       JOIN Person.StateProvince SP ON 
           SP.StateProvinceID = A.StateProvinceID
       JOIN Person.CountryRegion CR ON 
           CR.CountryRegionCode = SP.CountryRegionCode
       JOIN Sales.Customer C on S.CustomerID = C.CustomerID
       JOIN Sales.SalesTerritory T on C.TerritoryID = T.TerritoryID
   WHERE (T.[Group] = (@TerritoryGroup))
   Order by CR.Name
   

6. Click Run (!). The Define Query Parameters dialog box opens.

7. Type Pacific.

   The result set appears with the CountryRegion column and one row with the value Australia.

   When you define a query parameter called @TerritoryGroup, a new report parameter called TerritoryGroup is created.

8. (Optional) Next to the Dataset drop-down list, click the Edit Selected Dataset (…) button and then the Parameters tab. Verify that the query parameter @TerritoryGroup is bound to the value of the report parameter TerritoryGroup (=Parameters!TerritoryGroup.Value).

To add a dataset for available values for the parameter StateProvince

1. From the Dataset drop-down list, click <New Dataset>. The Dataset dialog box opens.

2. On the Query tab, in the Name field, type ValidValuesforStateProvince.

3. Verify that the Data source is Resellers.

4. Verify that the Command type is Text.

5. Paste the following query in the query pane.

   SELECT Distinct SP.Name AS StateProvince
   FROM Sales.Store AS S
       JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
       JOIN Person.Address AS A ON A.AddressID = CA.AddressID
       JOIN Person.StateProvince SP ON 
           SP.StateProvinceID = A.StateProvinceID
       JOIN Person.CountryRegion CR ON 
           CR.CountryRegionCode = SP.CountryRegionCode
   WHERE (CR.Name = @CountryRegion)
   Order by SP.Name
   

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

7. In the Parameter Value text box, type Australia. Click OK.

   The column StateProvince appears with four rows: New South Wales, Queensland, South Australia, and Victoria.

To add a dataset for available values for the parameter City

1. From the Dataset drop-down list, click <New Dataset>. The Dataset dialog box opens.

2. On the Query tab, in the Name field, type ValidValuesforCity.

3. Verify that the Data source is Resellers.

4. Verify that the Command type is Text.

5. Paste the following query in the query pane.

   SELECT Distinct A.City 
   FROM Sales.Store AS S
       JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
       JOIN Person.Address AS A ON A.AddressID = CA.AddressID
       JOIN Person.StateProvince SP ON 
           SP.StateProvinceID = A.StateProvinceID
       JOIN Person.CountryRegion CR ON 
           CR.CountryRegionCode = SP.CountryRegionCode
       JOIN Sales.Customer C on S.CustomerID = C.CustomerID
       JOIN Sales.SalesTerritory T on C.TerritoryID = T.TerritoryID
   WHERE (
       T.[Group] = (@TerritoryGroup) AND
       CR.[Name] = (@CountryRegion) AND
       SP.[Name] = (@StateProvince)
       )
   Order by A.City
   

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

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

Parameter Name	Parameter Value
@TerritoryGroup	Pacific
@CountryRegion	Australia
@StateProvince	Victoria

1. Click OK.
   The column City appears with three rows: Melbourne, Seaford, and South Melbourne.

You have now created four cascading parameters. Next, you will edit the properties of the report parameters that have been created that correspond to the query parameters. You will set each parameter to use the appropriate dataset for retrieving the set of available values.

To set the available values for the TerritoryGroup report parameter

1. On the Report menu, click Report Parameters. The Report Parameters dialog box opens showing TerritoryGroup selected in the Parameters pane.

2. Verify that the Data type is String.

3. In the Prompt text box, type Select a Territory Group:.

4. Verify that all check boxes are unselected.

5. In the Available values section, select From query.

6. From the Dataset drop-down list, select ValidValuesforTerritoryGroup.

7. On the Value field drop-down list, select SalesTerritory.

8. On the Label field drop-down list, select SalesTerritory.

9. In the Default values section, select Non-queried.

10. In the text box, type North America.

    You can set a default value to a specific value or to default values from a dataset field. Because this parameter is type String, you can enter the value directly in the text box. For other data types, you would type an expression beginning with an equal sign (=).

11. Click OK.

12. (Optional) Click the Preview tab. The TerritoryGroup parameter appears with the default value Europe and the valid values from the SalesTerritory field from the ValidValuesforTerritoryGroup dataset.

    No changes in report data will be seen until you change the Resellers dataset query in the final procedure in this lesson.

To set the available values for the CountryRegion report parameter

1. On the Report menu, click Report Parameters. The Report Parameters dialog box opens.

2. In the Parameters pane, select CountryRegion.

3. Verify that the Data type is String.

4. In the Prompt text box, type Select a Country/Region:.

5. Verify that all check boxes are unselected.

6. In the Available values section, select From query.

7. From the Dataset drop-down list, select ValidValuesforCountryRegion.

8. On the Value field drop-down list, select CountryRegion.

9. On the Label field drop-down list, select CountryRegion.

10. In the Default values section, select From query.

11. On the Dataset drop-down list, select ValidValuesforCountryRegion.

12. On the Value field drop-down list, select CountryRegion.

13. Click OK.

14. (Optional) Click the Preview tab. Select a value for TerritoryGroup. Select a value for the CountryRegion parameter. Verify that the values you see for CountryRegion are valid for the Territory group you selected.

To set the available values for the StateProvince report parameter

1. On the Report menu, click Report Parameters. The Report Parameters dialog box opens.

2. In the Parameters pane, select StateProvince.

3. Verify that the Data type is String.

4. In the Prompt text box, type Select a State/Province:.

5. Verify that all checkboxes are unselected.

6. In the Available values section, select From query.

7. From the Dataset drop-down list, select ValidValuesforStateProvince.

8. On the Value field drop-down list, select StateProvince.

9. On the Label field drop-down list, select StateProvince.

10. In the Default values section, select From query.

11. On the Dataset drop-down list, select ValidValuesforStateProvince.

12. On the Value field drop-down list, select StateProvince.

13. Click OK.

14. (Optional) Click the Preview tab. Select a value for TerritoryGroup. Select a value for the CountryRegion parameter. Select a value for the StateProvince parameter. Verify that the values you see for StateProvince are valid for the TerritoryGroup and CountryRegion you selected.

To create the City report parameter and set its properties

1. On the Report menu, click Report Parameters. The Report Parameters dialog box opens.

2. In the Parameters pane, note that there is no report parameter named City.

   Report parameters are created automatically when a query that contains parameters is defined for a dataset. None of the dataset queries created so far contain an @City query parameter. You can create a report parameter named City, and when a query is defined that does contain an @City query parameter, the query parameter will be bound automatically to a report parameter if it has a corresponding name (the name without the query parameter designator "@").

3. Click Add. A report parameter with default properties is created.

4. In the Properties section, in the Name text box, type City.

5. Verify that the Data type is String.

6. In the Prompt text box, type Select a City:.

7. Verify that all check boxes are unselected.

8. In the Available values section, select From query.

9. From the Dataset drop-down list, select ValidValuesforCity.

10. On the Value field drop-down list, select City.

11. On the Label field drop-down list, select City.

12. In the Default values section, select From query.

13. On the Dataset drop-down list, select ValidValuesforCity.

14. On the Value field drop-down list, select City.

15. Click OK.

16. (Optional) Click the Preview tab. Select values for the TerritoryGroup, CountryRegion, and StateProvince parameters. Verify that the parameter values you see for City are valid for the selections you chose.

Up to this point, you have created the cascading parameter values. Now you need to include these values in the dataset query for the table data region, so that the parameters you select are included in the query that retrieves the table data.

To modify the query for the reseller salesorders dataset to include existing query parameters

1. In Data view, in the Dataset drop-down list, select Resellers. The original query string created in the previous lesson appears in the query pane.

2. Replace the text in the query pane with the following query:

   SELECT S.CustomerID, SO.SalesOrderNumber, SO.OrderDate, SO.TotalDue,
       S.Name AS Store, A.City, SP.Name AS State, CR.Name
       AS CountryRegion, 
       SC.ContactID As StoreContactID, T.[Group] As TerritoryGroup
   FROM Sales.Store AS S
       JOIN Sales.CustomerAddress AS CA ON CA.CustomerID = S.CustomerID
       JOIN Person.Address AS A ON A.AddressID = CA.AddressID
       JOIN Person.StateProvince SP ON 
           SP.StateProvinceID = A.StateProvinceID
       JOIN Person.CountryRegion CR ON 
           CR.CountryRegionCode = SP.CountryRegionCode
       JOIN Sales.SalesOrderHeader AS SO ON 
       S.CustomerID = SO.CustomerID
       JOIN Sales.StoreContact SC ON  S.CustomerID = SC.CustomerID
       JOIN Sales.Customer C on S.CustomerID = C.CustomerID
       JOIN Sales.SalesTerritory T on C.TerritoryID = T.TerritoryID
   WHERE(
       (T.[Group] = (@TerritoryGroup))
       AND
       (CR.Name = (@CountryRegion))
        AND
        (SP.Name = (@StateProvince))
       AND
       (A.City = (@City))
      )
   ORDER BY S.CustomerID 
   

   The query now includes query parameters that use the report parameter values.

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

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

Parameter Name	Parameter Value
@TerritoryGroup	Pacific
@CountryRegion	Australia
@StateProvince	Victoria
@City	Melbourne

1. Click OK.
   The result set contains sales for resellers in the city of Melbourne.
   When you added query parameters to the query definition for the Resellers dataset, they automatically were set to get their values from the corresponding report parameter values.
2. (Optional) Next to the Dataset drop-down list, click the Edit Selected Dataset (…) button and then the Parameters tab. Verify that the query parameters @TerritoryGroup, @CountryRegion, @StateProvince, and @City are bound to the values of the corresponding report parameters.

To set the NoRows value

1. Click the Layout tab to change to Layout view.

2. Click in the table so the table handles appear. Click the corner handle of the table to select it. The table appears with a grayed outline.

3. In the Properties window, find the NoRows property. Paste the following text into the adjacent text box.

   There are no resellers in this area.

To create the main report title and parameter value text boxes

1. Click the Layout tab to change to Layout view.

2. Right-click in the text box that contains the report processing timestamp and select Expression.

3. Click the expression (Fx) button next to the Value text box. The Edit Expression dialog box opens. Replace timestamp expression with the following:

   ="Report Processed Date: " & 
     Globals!ExecutionTime.ToShortDateString() & " " & 
     Globals!ExecutionTime.ToShortTimeString() & vbCrLf & 
     "Sales Territory for: " 
   & Parameters!TerritoryGroup.Value & ", " 
   & Parameters!CountryRegion.Value & ", "
   & Parameters!StateProvince.Value & ", "
   & Parameters!City.Value
   

4. Click Preview. Try selecting different parameter values. Notice that as you select each successive parameter, the drop-down list of the next parameter shows only the available values based on your selection. The report data does not change even though you are selecting new parameters. To reprocess the report with the newly selected parameters, click View Report.

Next Steps

You have successfully created a report with cascading parameters that displays reseller sales by territory. In the next lesson, you will learn to use parameters to change the way table detail rows and table groups are sorted. See Lesson 3: Changing Initial Sort and Interactive Sort Using Parameters.

See Also

Other Resources

Working with Parameters in Reporting Services
Using Parameters to Control Report Data

Help and Information

Getting SQL Server 2005 Assistance