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 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 will be the focus of 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). 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 we can use to make the page load with specific data we want:

• https://www.veroscribe.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

The goal is to use this technique in SSRS to build dynamic URLs behind 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. The provider's known NPI is used to dynamically load the correct reference page on the official NPI Registry website for additional information.

A simple statement that will return the provider information includes a field called 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 for navigation.

Pro Tip: The author finds 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 string building skills in SQL! Best practice would be to store these URLs in a reference table that could be easily edited and organized if they happen to change.

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 specifying the url_ProviderNpi field as the URL. Also don't forget to set the foreground color of the cell to blue so users will instinctively know to click on it!

When the NPI hyperlinked field in the report is clicked, it takes users directly to the NPI Registry site automatically loading the reference page for that provider. The URL in the address bar 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. By adding some javascript to the expression used for the URL property, you can have it open in a new browser tab instead. The initial expression (which references the field name) can be modified by adding the javascript command along with delimiters before and after the field reference.

Now when the NPI field from the report is selected, a new tab opens and goes to the linked website, passing the data that was provided.

Wrapping Up

This article teaches techniques to add to SSRS skills. In part 2 of this blog, a closer look is taken at using this same URL Action property to navigate between different Visual Studio projects.