Using URLs in SQL Server Reporting Services

Part 1 - Navigating to an Internet site

One of the most obvious features of SQL Server Reporting Services is that the reports are presented to the user with a web browser. This means that SSRS natively has access to many of the same functional principles as most web pages and web sites. Specifically, this would include the use of hyperlinks (URLs) to easily navigate to other reports, web pages, or other web-based resources. How do we do that? With the Action property.

This is part 1 of a 2-part blog.

The Action property

If you’re not already familiar with it, the Action property allows us to control what happens when a user selects a text box, table cell, and most other objects within a report. Let's look at the options available to us.

Go to report
Use this action is to link to another report. Most likely this is a detail report or other related report that provides more information on what you’re drilling into from the parent. SSRS provides a handy way to do this by simply selecting the report, assign the needed parameters to the linked report, and you’re done! However, the biggest gotcha to this method is that you are limited to reports that reside within the same project.

Go to bookmark
This is used to navigate to a predefined location on your report. Most if not all objects within SSRS have a Bookmark property. Tag the Bookmark property of an object, and you can use this functionality to easily navigate and jump to them much like a standard website bookmark.

Go to URL
This is the option that we’ll be focusing on in this article. It is a bit more technical to use properly, but provides much more flexibility on what’s happening in the click action. By understanding how URLs work and having the ability to construct them dynamically from your data, this technique opens up MANY navigation possibilities and can add tremendous value to your reports. Imagine the benefits of having common utility reports or dashboards that could be easily accessed from ANY report on your network.

Understanding the URL

Most people already know that websites are accessed via hyperlink or URL (Uniform Resource Locator). But what may not be obvious is that hyperlinks (which are nothing more than simple strings) can contain elements that will control how the web page loads and operates. Depending on the platform used to build a site, in most cases this is how pages "talk" to each other within a site: via URLs that contain parameters or values which will be read by the page’s internal back-end code and affect how it runs.

Here are some examples of website URLs that contain some sort of field or text that that we can use to make the page load with specific data we want. I’ve highlighted the values to see what text we will need to dynamically change as we build our URL strings later. (Go ahead and try them if you like.) You'll see the links take you not just to the site, but to a specific page with an already-loaded searched value.

https://icdcodelookup.com/icd-10/codes/Z45.0

https://npiregistry.cms.hhs.gov/registry/provider-view/1750381133

https://www.findacode.com/code.php?set=CPT&c=71045

You can even feed values into Google and automatically launch a search:

https://www.google.com/search?q=CPT+71045

Our goal is to use this technique in SSRS to build dynamic URLs behind our report results. This will immediately add some inter-web functionality that we can directly control from our data.

Scenario

Let’s build a simple provider dictionary report which will have a live link to the actual NPI registry website for that provider. We’ll use our provider’s known NPI to dynamically load the correct reference page on the official NPI Registry website for additional information.

We begin with an example of a simple statement that will return the provider information that will be used as the main data source in our report. Notice that we have a field we are going to call: url_ProviderNpi. This field is a combination of a hard-coded literal value (in this case, a website address), concatenated with a value (Npi) from the DMisProvider table. Together these comprise the URL string which will be the actual path we will be navigating to.

→  Pro Tip
I find it easier to build the complete URL string within the SQL procedure instead of fumbling around with SSRS expressions on the report side. This makes it MUCH easier to control as well as making changes without editing the report every time. It is also a good excuse to brush up on your string building skills in SQL! You may or may not want to hard-code the base URL here too. Best practice would be to store these URLs in a reference table that could be easily edited and organized if they happen to change.

Data output looks good! 

Notice our dynamically-built url_ProviderNpi field which uses the value of the Npi field.

Let's start with a basic SSRS report built from this same dataset (without the url_ProviderNpi in the output) then take a look at how we can modify it with a new Action property.  Here's our starting point (yes, these are fictional names and NPI numbers):

Setting the Action Property

The final step is to configure the data from the Npi field on the report as a hyperlink to launch the URL. This is done by editing the Action property for the Npi column and specifiging the url_ProviderNpi field as the URL (a good illustration of why it’s easier to build the entire URL string in the SQL statement):

Also don’t forget to set the foreground color of the cell to blue so users will instinctively know to click on it! Our updated report should look something like this:

Now when we click on the NPI hyperlinked field in our report, it takes us directly to the NPI Registry site automatically loading the reference page for that provider. Notice the URL in the address bar which is what actually drives the content for the page:

Add more functionality to the Action

By default, when you click on a link in an SSRS report it opens the page in the same window, replacing the parent report you were viewing. Wouldn't it be nice if we could have it open in a new browser tab instead? Well we can, by adding some javascript to the expression used for our URL property. We're going to modify the Visual Basic expression used to link to the URL data field. To do this, first click the expression box next to the Select URL dropdown:

Then change the initial expression (which references the field name) by adding the javascript command along with delimiters before and after the field reference:

Now when we select the NPI field from our report, a new tab opens and goes to the linked website, passing the data we provided. Neat!

Wrapping up

Hopefully you've learned a new trick or two to add to your SSRS skills. In part 2 of this blog, we'll take a closer look at using this same URL Action property to navigate between different Visual Studio projects.