Using URLs in SQL Server Reporting Services - Part 2

In part 1 of this blog, we discussed how to build a URL that could navigate to various resources on the internet. We learned how to build the URL string based on a combination of the website path and the data within our results. In part 2, we’ll look at a similar example of how to navigate to another SSRS report on our local network that resides in a different project folder. Using the same technique, we can build the navigation link and pass parameters into another report instead of using the default SSRS method which only links to reports in the same folder.

This is part 2 of a 2-part blog.

Navigating between reports

I have two reports that exist in different SSRS project folders. I want the user to click on the medical record number column on Discharges by Date Range report and navigate to the central Patient Dashboard report.

Discharges by Date Range

The first report is a simple discharge report simply listing accounts that have been discharged between a certain time period. It has a field called MRN which I want to use as my link to the patient dashboard report. This report resides in a Visual Studio project folder called "Samples."  (Visit the DR Network to download a free version of this report!)

Patient Dashboard
The second report is an example of a patient-level dashboard. It's built to show a variety of information at the patient account level including demographics, insurance, and visit history. This is a handy report that would be useful to access from other reports whenever we need to learn more about a patient. This report accepts a single parameter (the patient's Medical Record Number). Note that this report's .rdl file is stored with a different Visual Studio project called "Utilities." (Visit the DR Network to download a free version of this report!)

The key challenge here is that the Patient Dashboard report resides in a different VS project folder from the parent report. Therefore, I must make use of the “Go to URL” action to get around the SSRS limitation of only pointing to reports within the same project. I can do this by building the URL that will be used when clicking on the patient's MRN value from the Discharge report. I can also build the URL to pass in the parameter value that the Patient Dashboard requires (in this case, the MRN).

Find the URL

The first step to build the URL is to find the "base" address of the published Patient Dashboard report (our target). We can do this by navigating to the report via the Report Server as shown below. (You can also use Report Manager which will return a slightly different URL from that pictured.)

So on my server, the complete path (URL) to this report is:

http://lima/ReportServer_DEV/Pages/ReportViewer.aspx?%2fHowardTest%2fUtilities%2fPatientDashboard&rs:Command=Render

This looks messy, so I find it easier to clean up the browser symbols for slashes and spaces; it’s also safe to trim off the &rs:Command=Render option. So the much cleaner URL for my Patient Dashboard is:

http://lima/ReportServer_DEV/Pages/ReportViewer.aspx?/HowardTest/Utilities/PatientDashboard

It may look convoluted, but it should be intuitive enough to see that it is merely the deployment path of the report. (We can copy this into a browser address bar to test and make sure it works as expected.)

Add the parameter(s) to the URL 

The next step is to append the MRN parameter which the Patient Dashboard needs before it can run. The name of my parameter is defined as "MRN" in the dashboard report, so the syntax for this is:

&MRN=<<MedicalRecordNumber>>

So a complete URL along with an actual MRN parameter value would look this:

http://lima/ReportServer_DEV/Pages/ReportViewer.aspx?/HowardTest/Utilities/PatientDashboard&MRN=N000001533

Be patient and ready for some trial and error. It may take some tweaking to get this right! Test this as you go by pasting the URL in the browser as you build it.

 →  Pro Tip

If there were more than one parameter, the URL would just append them in order with an ampersand (&) and the next parameter name, like this:

[URL]&MRN=N000001532&OtherParam=12345

Build the URL in your data query

I mentioned this tip in part 1 of this blog, but this is another reminder that it is MUCH easier to build the URL string in the T-SQL stored procedure as a field instead of fumbling around with SSRS expressions. If you want to go the extra mile, a true best practice would be to store these URLs in a reference table that could be easily edited and organized if they happen to change.

Notice in my query above how I build the URL in a column called url_PatientDashboard. I start with the base URL, add the &MRN= for the parameter, and then the UnitNumber field which completes the URL I need in the correct format. I can now easily tweak and change this in the SQL stored procedure without touching the report.

Add the new URL field to the Action

The last step to navigate to this URL is to modify, in Visual Studio, the click action of the text box properties. It’s simply a matter of selecting the Go to URL option and pointing it to our properly formatted field, url_PatientDashboard.

We're done

Now when we click on the MRN field in our Discharges report, it takes us directly to the Patient Dashboard. Notice the URL in the address bar which is passing the MRN parameter for this report to use.

This technique allows you to "talk" to any deployed SSRS report in your network. Planning for and building in this type of report interactivity really helps your users get to the information they need in a flexible and dynamic way.

Don't forget permissions

Also keep in mind that report users must have permissions to access reports in any folder you send them to via URL. Since permissions are set at the folder level in Report Manager, some users may or may not have the appropriate privileges when bouncing across SSRS reports.

Thanks for reading this blog, and I hope you've learned a new trick or two for your SQL Server Reporting Services skills!