Drillthrough Reports (Report Builder and SSRS)
Applies To: SQL Server 2016
A drillthrough report is a report that a user opens by clicking a link within another report. Drillthrough reports commonly contain details about an item that is contained in an original summary report. For example, in this illustration, the sales summary report lists sales orders and totals. When a user clicks an order number in the summary list, another report opens that contains details about the order.
The data in the drillthrough report is not retrieved until the user clicks the link in the main report that opens the drillthrough report. If the data for the main report and the drillthrough report must be retrieved at the same time, consider using a subreport. For more information, see Subreports (Report Builder and SSRS).
To get started quickly with drillthrough reports, see Tutorial: Creating Drillthrough and Main Reports (Report Builder).
A drillthrough report typically contains parameters that are passed to it by the summary report. In the sales summary report example, the summary report includes the field [OrderNumber] in a text box in a table cell. The drillthrough report contains a parameter that takes the order number as a value. When you set the drillthrough report link on the text box for [OrderNumber], you also set the parameter for the target report to [OrderNumber]. When the user clicks the order number in the summary report, the target detail report opens and displays the information for that order number. To view instructions about customizing drillthrough reports based on parameter values, see Report Parameters (Report Builder and Report Designer) and the InScope Function (Report Builder and SSRS).
To create a drillthrough report, you must design the drillthrough report first, before you create the drillthrough action in the main report.
A drillthrough report can be any report. Typically, the drillthrough report accepts one or more parameters to specify the data to show, based on the link from the main report. For example, if the link from the main report was defined for a sales order, then the sales order number is passed to the drillthrough report.
You can add drillthrough links to text boxes (including text in the cells of a table or matrix), images, charts, gauges, and any other report item that has an Action property page. For more information, see Add a Drillthrough Action on a Report (Report Builder and SSRS).
You can create the drillthrough action in the main report as a report action or a URL action. For a report action, the drillthrough report must exist on the same report server as the main report. For a URL action, the report must exist at the fully qualified URL location. The way that you specify a report might differ for a report server or a SharePoint site that is integrated with a report server. If the report server is configured in SharePoint integrated mode, only URL actions are supported.
For more information, see Add a Drillthrough Action on a Report (Report Builder and SSRS) and Specifying Paths to External Items (Report Builder and SSRS).
To view a summary report with drillthrough links after it is published, you must ensure that the drillthrough reports reside on the same report server as the summary report. In all cases, the user must have permissions on the drillthrough report to view it.