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

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 to display detail data for a sales order that you specify with a parameter. You will open an existing report and create a drillthrough action for the sales order number. When you run this report and click on the sales order number, the new report opens and displays the details of that sales order.

To add a new report to an open report server project

In Solution Explorer, right-click Reports, point to Add, and click New Item.

In the Add New Item dialog box, under Templates, click Report.

In Name, type Sales Order Detail.rdl, and then click Add.

Report Designer opens and displays the new report definition in Design view.

To create a reference to a shared data source

In the Report Data pane, click New, and then click Data Source.

Note

If you do not see the Report Data pane, from the View menu, click Report Data.

In Name, type AdventureWorks_Ref.

Select Use shared data source reference.

From the drop-down list, select AdventureWorks.

Click OK.

To create a new dataset

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

To add a report drillthrough action to your report

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

Click Action.

In Enable as a hyperlink, select Go to report.

Another section appears in the dialog box.

In Select a report from the list, from the drop-down list, select Sales Order Detail.

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

A new row is added to the grid.

In Name, from the drop-down list that shows the list of parameters defined for the report, select SalesOrder.

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

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

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

Click Font.

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

Next to Color, click (fx) to open the Expression dialog box.

Replace the default text Black with a different color, for example, Blue.

Note

To see valid values for font color, in the Values pane, click More colors. The Select Color dialog box opens. You can browse valid colors in this dialog box, and select the one that you want to use. Click OK.

Click OK twice.

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

Click Preview. The Sales Orders report displays sales orders grouped by date. The Order column displays sales order numbers that are drillthrough links.

Click a sales order number with a drillthrough link.

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.