Linked Report Drill-Through With SQL Server Reporting Services

We've got a fairly large Reporting Services installation in our organization. We've been using it for about eight or nine years, so we've accumulated several hundred reports in that time. Without going into undue detail about the server organization, we have all the deployed reports in a directory structure underneath "/Base Reports" that makes it easy for developers to see where (i.e. project, solution) a given report came from. All of these reports are then presented in separate directory structures where all the security is defined, e.g. a directory for the sales team, purchasing team, management, IT, and so on.

This allows us to present the same report to multiple groups of people by creating more than one linked report that points to the same base report in different locations. So far so good. It adds a little bit of extra work when deploying reports, but keeps the server a heck of a lot more manageable overall.

I've only come across one significant issue in all the years that we've been using this everything-is-a-linked-report approach: when configuring the on-click action for a text box as "Go to report" in order to implement some kind of drill-through, Reporting Services will attempt to directly load the underlying report that you're pointing to. In other words, it will not navigate to the appropriately named linked report within the same directory; it will attempt to load the target report from its real home within "/Base Reports", which the user probably doesn't have direct access to.

But as is usually the case, there is a workaround. Choose "Go to report" for the text box action as usual, but for the report name, use an expression like this:

=String.Format("{0}\{1}", Globals!ReportFolder.Replace("/", "\"), "My Detail Report")

This will cause Reporting Services to navigate to a report (or linked report) named "My Detail Report" that's within the same directory as the report the user is currently viewing.

And as is also usually the case with silly workarounds, there's a catch: this doesn't work when previewing the report in Visual Studio. So do it the old fashioned way by selecting the report name directly while you're designing, then switch to this little hack once you're ready to deploy.