• Article

Lesson 5: Adding Parameters to Pass to a Drillthrough Report (Report Builder 2.0)

Drillthrough reports are a type of report that you access by clicking a link in the current report. When you click a text box that has a drillthrough action, you open the drillthrough report. If the drillthrough report has parameters, you must pass parameter values to each report parameter.

In this lesson, you will design a new report that accepts a sales order parameter and displays product name, quantity, and total for each line item in a sales order. The data comes from the AdventureWorks2008 sample database. You will then modify the report that you created in Tutorial: Creating a Basic Table Report (Report Builder 2.0) or one of the subsequent lessons, by adding a drillthrough link for a sales order. When the user clicks the link in the main report, the sales order detail report opens and displays each line item for the sale.

To create a new report definition file and a save it on the report server

  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 Sales Order Details.

  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, Sales Order Details.

  7. Click Save.

    You have saved a new report that will be the target of a link in the main report.

To launch the table wizard and add a connection

  1. On the design surface, under Click to add data, click Table or Matrix.

    The New Table/Matrix wizard opens and displays the Choose a connection to a data source page.

  2. 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). 

  3. Click Next.

To define a Transact-SQL query for report data

  1. On the Design a query page, click Edit As Text. The text-based query designer opens.

  2. Paste the following Transact-SQL query into the Query pane.

    SELECT P.Name AS Product, SD.OrderQty AS Quantity, SD.LineTotal 
FROM Sales.SalesOrderDetail AS SD 
   INNER JOIN Production.Product AS P 
   ON SD.ProductID = P.ProductID 
   INNER JOIN Sales.SalesOrderHeader AS SH 
   ON SD.SalesOrderID = SH.SalesOrderID
WHERE (SH.SalesOrderNumber = (@SalesOrder) )
ORDER BY SD.SalesOrderDetailID

  3. Click OK.

    Notice that to create the field collection, you do not need to run the query in the Query Designer with a query parameter value. If you do want to run the query, you can use the following sales order number: SO43661.

  4. Click Next.

    You have added a dataset to the report definition.

To organize data in groups

  1. On the Arrange fields page, drag Product to Row groups.

  2. Drag Quantity to Values.

  3. Drag LineTotal to Values and place below Quantity.

  4. Click Next.

    You have added a row group and two data values to display in the table.

To add subtotals and totals, and choose a style

  1. On the Choose the layout page, under Options, verify that Show subtotals and grand totals is selected.

  2. Clear the Expand/collapse groups option.

    The Preview pane shows a table with 3 columns and 3 rows.

  3. Click Next. On the Choose a style page, select a style.

  4. Click Finish.

    The table is added to the design surface. The table has 3 columns and 3 rows. The Row Groups pane shows one group: Product and no Detail data.

  5. Click Run to preview the report.

To format the table

  1. Switch to Design view.

  2. Click the cell in the second row and drag down to select the two cells that contain [Sum(LineTotal)].

  3. On the Home tab, in the Number group, click the Currency button.

    The numbers in both cells are formatted as currency.

  4. On the Report Builder button, click Save to save this report on the report server.

    Next, open the report that is the main report and add the drillthrough action to specify this report.

To add a report drillthrough action to your report

  1. On the Report Builder button, click Open.

  2. Navigate to the Sales Order report that you created in a previous lesson, and click Open.

  3. In the SalesOrderNumber column, right-click the text box that contains [SalesOrderNumber], and then click Text Box Properties.

  4. Click Action.

  5. In Enable as a hyperlink, select Go to report.

    Another section appears in the dialog box.

  6. In Specify a report, click Browse.

  7. In Recent Sites and Servers, browse to the Sales Order Detail report you created in the previous procedures, and then click Open.

  8. In Use these parameters to run the report, click Add.

    A new row is added to the grid.

  9. In Name, from the drop-down list, select SalesOrder.

  10. In Value, from the drop-down list, select [SalesOrderNumber].

    This binds the value from the main report to the parameter that the target report expects.

    Next, change the text style and color for the drillthrough link.

  11. Click Font.

  12. In Effects, from the drop-down list, select Underline.

  13. In Color, from the drop-down list, click Blue.

  14. Click OK.

    The text box you added now has underlined text that is the color that you chose. These visual indicators help report readers understand that there is an active link to another report.

To preview the report

  1. Click Run to preview the report. The Sales Orders report displays sales orders grouped by date. The SalesOrderNumber column displays sales order numbers that are drillthrough links.

  2. Click a sales order number.

    The report specified by the drillthrough link runs and displays the order details for the sales order number that you clicked.

    Note

    To return to the main report, use the back arrow on the report viewer toolbar.

Next Steps

You have successfully completed the Adding Parameters to a Report tutorial. To learn more reporting techniques, see Tutorials (Report Builder 2.0).