Lesson 7: Creating a Drillthrough Report

Article
12/03/2008

New: 17 July 2006

One of the ways that parameters are used is to drill through to other reports. In this lesson, you will add a parameter to the AdventureWorks sample report "Territory Sales Drilldown" that filters the report data by sales territory group. To provide available values for the parameter, you will create a TerritoryGroup dataset that retrieves the distinct values available for sales territory groups from the AdventureWorks database.

In the next lesson, you will use this report as the target of several kinds of navigation actions.

To open the Advanced Parameters Tutorial report server project

In SQL Server Business Intelligence Development Studio, open the Advanced Parameters Tutorial report server project created in the previous lesson.
In Solution Explorer, double-click on the Resellers Worldwide report. The report opens in Layout view.
Click the Data tab.

To import the AdventureWorks sample report Territory Sales Drilldown

In Solution Explorer, right-click on the Reports folder, and choose Add, and then Existing Item. The Add Existing Item dialog box opens.

Note

The Import Reports command imports existing Microsoft Access reports, not existing Reporting Services reports.
Browse to the AdventureWorks sample reports folder. The default location is *<drive>:\*Program Files\Microsoft SQL Server\90\Samples\Reporting Services\Report Samples\AdventureWorks Sample Reports. Double-click the Territory Sales Drilldown.rdl report to add it to the Advanced Parameter Tutorial project.
In Solution Explorer, right-click Territory Sales Drilldown.rdl and choose Rename. Type Parameterized Territory Sales Drilldown.rdl. Press ENTER.
Double-click the Parameterized Territory Sales Drilldown report. The report opens in Layout view.

Next, you need to change the data source for this report to use the shared data source you created in Lesson 1.

To bind a dataset to a different data source

Click the Data tab to switch to Data view. You will get the following error: "A connection cannot be made to the database. Set and test the connection string."

The Parameterized Territory Sales Drilldown report uses a data source named "AdventureWorks". You need to change this to the shared data source "Resellers".
In the Datasets drop-down list, select the TerritorySales dataset and click the Edit Select Dataset button.
On the Query tab, from the Data source drop-down list, select Resellers.
Click OK.
Click Run (!) to see the result set.

You should see thousands of rows with values for six columns: Name, SalesPersonID, FirstName, LastName, SalesOrderNumber, TotalDue.

To parameterize this report, you will create a new dataset for the valid values. You will then create a new report parameter SalesTerritoryGroup, and sets its valid values to be retrieved from this dataset.

To create a dataset for available values

From the Dataset drop-down list, click <New Dataset>. The Dataset dialog box opens.
On the Query tab, in the Name text box, type ValidValuesforTerritoryGroup.
Verify that the Data source is Resellers.
Verify the Command type is Text.
Paste the following query in the query pane.
```
SELECT DISTINCT [Group] AS SalesTerritoryList
   FROM [AdventureWorks].[Sales].[SalesTerritory]
```
The column Group from the table Sales.SalesTerritory in the AdventureWorks database contains the values for territories.
Click Run (!) to see the result set.

The column SalesTerritoryList appears with three rows: Europe, North America, and Pacific. These will be the three available values for the multivalue parameter SalesTerritoryGroup that you create in the next procedure*.*

To add a new multivalue report parameter

Click the Layout tab.
From the Report menu, choose Report Parameters. The Report Parameters dialog box opens.
Click Add. A new report parameter with default values is created.
In the Properties section, in the Name text box, type SalesTerritoryGroup. Verify that the data type is String.
Select the Multivalue option.
In Prompt, type Sales Territory Group:.
Clear the Allow blank option.
In the Available values section, click From query.
From the Dataset drop-down list, select TerritoryGroup.
From the Value field drop-down list, select SalesTerritory.
From the Label field drop-down list, select SalesTerritory.
In the Default values section, click From query.
From the Dataset drop-down list, select TerritoryGroup.
From the Value field drop-down list, select SalesTerritory.

This sets the default values for SalesTerritoryGroup to all the values retrieved for the dataset ValidValuesforTerritoryGroup.
Click OK.

You have defined a report parameter and its set of available values. In the next procedure, you will modify the TerritorySales dataset query to include the column for sales territory group from which you just created the set of valid values. You will then modify the query so you can limit the result set to data for the selected parameter values for SalesTerritoryGroup.

To bind the report parameter to the dataset query

Click the Data tab.
From the Dataset drop-down list, select TerritorySales. The TerritorySales query appears in the query pane.

Modify the existing query by adding the sales territory group, as you did in the valid values query. Add the restriction clause HAVING (ST.[Group] IN (@SalesTerritoryGroup)) to limit the results to only the data for the sales territories selected.

Replace the existing query with the following:

SELECT ST.[Group] AS SalesTerritory, ST.Name, SP.SalesPersonID, 
  C.FirstName, C.LastName, SOH.SalesOrderNumber, SOH.TotalDue 
FROM  Sales.SalesTerritory ST INNER JOIN
 Sales.SalesPerson SP ON ST.TerritoryID = SP.TerritoryID INNER JOIN
 HumanResources.Employee E ON SP.SalesPersonID = 
     E.EmployeeID INNER JOIN
 Person.Contact C ON E.ContactID = C.ContactID INNER JOIN
 Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID
GROUP BY ST.[Group],ST.Name, SP.SalesPersonID, C.FirstName, 
    C.LastName, SOH.SalesOrderNumber, SOH.TotalDue
HAVING (ST.[Group] IN (@SalesTerritoryGroup))
ORDER BY ST.Name

(Optional) Verify the query parameter is bound to the report parameter. Click the Edit Selected Dataset button, and click the Parameters tab. The parameter named @SalesTerritoryGroup should be set to the expression =Parameters!SalesTerritoryGroup.Value.
Click Run (!) to see the result set. The Define Query Parameter dialog box appears. Type Europe.
The result set appears containing only values for Europe.

Note

The query designer does not support testing multivalue parameters.

The Parameterized Territory Sales Drilldown report has a preexisting hyperlink action defined on the SalesOrderNumber text box that addresses a drillthrough report in the same report folder. In the next procedure, you will change the hyperlink to use the default sample folder.

To point the Sales Order Number drillthrough to the sample report folder

Click the Layout tab.
Right-click in the SalesOrderNumber text box and select Properties. The Textbox Properties dialog box opens.
Click the Navigation tab.
In the Hyperlink section, for the Jump to Report option, replace the report name with this fully qualified report address:
```
/AdventureWorks Sample Reports/Sales Order Detail
```
Click OK.
On the File menu, click Save All.

To modify the report description

From the Report menu, select Report Properties. The Report Properties dialog box opens.
Replace the text in the Description text box with the following: Adventure Works sales by territory with territory parameter.
Click OK.

Note

When you deploy a report to the report server, changes you make to the description in Report Designer will not overwrite the description published on the report server. To replace a published description, delete the description using Report Manager, and then redeploy the report from Report Designer. To delete the description in Report Manager, browse to the report and click the Properties tab. Highlight the Description text and delete. Click Apply.

Next Steps

You have successfully added hyperlinks to report item text boxes. In the next lesson, you will create a linked report based on the published Resellers Worldwide to provide a report that presets values for the report parameters for a target audience. See Lesson 8: Adding a Linked Report with Customized Parameter Values.