Adding Cascading Parameters to a Report
SQL Server 2005
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 (fieldStateProvince). 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.
1. In SQL Server Business Intelligence Development Studio, open theAdvancedParametersTutorial report server project created in the previous lesson.
2. In Solution Explorer, double-click on the Resellers Worldwide report. The report opens inLayout view.
3. Click the Data tab.
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.
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 CountryRegionFROM 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.TerritoryIDWHERE (T.[Group] = (@TerritoryGroup))Order by CR.Name6. 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 calledTerritoryGroup 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).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 StateProvinceFROM 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.CountryRegionCodeWHERE (CR.Name = @CountryRegion)Order by SP.Name6. 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.
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.TerritoryIDWHERE ( T.[Group] = (@TerritoryGroup) AND CR.[Name] = (@CountryRegion) AND SP.[Name] = (@StateProvince) )Order by A.City6. 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 |
- Click OK.
The column City appears with three rows: Melbourne, Seaford, and South Melbourne.
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.
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 theCountryRegion parameter. Verify that the values you see for CountryRegion are valid for the Territory group you selected.
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 theCountryRegion 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.
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, andStateProvince 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.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 TerritoryGroupFROM 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.TerritoryIDWHERE( (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 |
- 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. - (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.
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.
1. Click the Layout tab to change to Layout view.
2. Right-click in the text box that contains the report processing timestamp and selectExpression.
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.Value4. 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, clickView Report.
