SSRS: Dynamically Linking Reports Together

This blog post provides a step-by-step guide with a worked example of how to link together two reports without hard-coding report paths (allowing you to migrate reports from Development > Testing > Live without having to worry about changing code to make sure, for example, your Live report is not linking to a Development report).


By far the easiest way to work through this is to look at a specific example.

Background
Let us assume we have two Reports "Journal Lines" (General Ledger) and "Invoice Details" (Accounts Payable). The user has defined a link between the "Journal Lines" report that, if the Journal is the result of an invoice, the user can click on the Invoice Number in the Journal Lines report and be taken to all Invoices that match that Invoice Number in the Invoice Details report.

Our Reporting Services server is structured so that at the Root there is a series of "Live" folders as well as hidden DEV (Development) and TST (Testing) folders which contain reports being worked on with the same folder structure as the Live folders;

What we need to be able to do is code the link between the two reports in the DEV folder in such a way so that when the reports are moved to the TST folder the links automatically re-point themselves.

Linking The Reports
Open the main report (in this case Journal Lines) and select the cell you wish to make clickable to perform the link. Right click it and select "Text Box Properties ...";

Select the "Action" item on the left panel (bottom entry), and then select "Go to URL"

Click on the function button next to the "Select URL" entry box and enter the formula;

=Globals!ReportServerUrl + "?" + Replace(Replace(Globals!ReportFolder, "General Ledger", "Accounts Payable") + "/Invoices Between Two Dates&rs:Command=Render", "/", "%2f")

Click "OK" back through the dialogs to save the change.

This formula uses the global variables ReportServerUrl and ReportFolder to build a URL. The inner replace function swaps the "General Ledger" directory (see first image) with "Accounts Payable" while the outer one replaces all the "/" characters with their HTML equivalent.

Run the report, the link this creates will now map directly to the new report.