Not logged in - Login

SSRS Drilldown in Linked Reports

In SQL Server Reporting Services, you can assign actions items such as text boxes. This is commonly used to create a "drill down", where clicking on the item will take you to another report that has detail information for the item that was selected.

In the most basic case, you simply specify the name of the report in the Action tab of the Text Box Properties dialog. This report should be in the same folder as the report you are working on. (It can be in other folders, but you must specify a relative path.) This works fine as is, but what happens if you create a linked report in Report Manager for the main report? Turns out, this is a problem.

Better to explain by example. Say you have a report, and the detail report in a folder on Report Manager called Source. Then you link the two reports out to a user folder called Public. When you run the report in Public, and click on the item with the action, it still attempts to link to the drill down report in Source. This might be a problem because the public users don't have permissions to the Source folder. What you really want is for the drill down action to use the detail report (linked) in the Public folder. So how do you do that?

Open the Text Box Properties dialog. Click on the Action tab. Select Go To Report. In the text box labelled Specify a Report, you normally enter the report name. But notice the button to the right? This property can be an expression.

Text Box Properties

Click the expression button and enter the following expression:

=Globals!ReportFolder & "/DrillDownReportName"

The ReportFolder global variable contains the current report's path. With this expression, the drill down link will work correctly no matter where you use it. If you access the main report from the Source folder, then the drill down report will come from the Source folder. If you access the linked report in the Public folder, then the drill down report will be the linked one in the Public folder.