SSRS dynamic hyperlink

Sometimes it’s quite useful to use a hyperlink in a report to drill through or drill across to more detail or to a related report. I’ve also found it useful to dynamically generate links to various websites where the URI is predictable.

Amazon.com uses a fairly predictable set of URLs to find books that relies on the amazon standard identification number (ASIN).

Take for example Itzik Ben-Gan’s book, Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions

The URL format is like this with the Amazon book reference number at the end:

http://www.amazon.com/gp/product/0735658366

Based on that Wiki article, the gp means general page. You can replace that with dp for the details page.

If you had a database of books or movies, and you combined this with a dataset of ISBN and ASIN numbers you could then produce a report with links to the appropriate information page on Amazon.

In a project I’m currently working on, the company uses a web application as it’s main operational system. Just to provide context, it’s heavy on Java and uses an Oracle 11g database. One of the various reports I am creating for this system will run from the data warehouse and is designed almost as a meta-data report with its aim being to improve data quality and take the pressure off the ETL process. Its mode of action is to show records by division where certain key fields have not been entered. Mostly it is essential customer data that will enable very powerful analysis and modelling, without which the results will not be as useful.

This ‘exception’ report is a fairly basic table clearly sorted and showing exactly where the data is missing. The custmer-contact ID number as used in the web application is used on this report to generate a hyperlink to the customer’s details edit page in the web application.

There are 3 essential considerations.

  1. The link will open a webpage and the default action is to open in the same tab/page which then forces you to run the report again to see the links. The answer to that is to inject some javascript in there with the _blank option. This is like the HTML:
    Target = “_blank”
    This will usually open the link in a new window but possibly in a new tab depending on the user’s browser settings.
  2. The ID number you are dynamically adding to the URL must be converted to a string before appending it to the URL. This is fairly well known and obvious but it’s important to mention. You can’t concatenate a string with a numeric type without converting the numeric type to a compatible string type unless the language you are using handles the appropriate type conversion implicitly.
  3. The formatting of the hyperlink in the report will not happen automatically. I recommend setting it to blue and underlining it just out of convention, unless you have some corporate standard or style guide that covers this. Then you don’t have to explain functionality. Just ask any UX expert about that one and I’m sure you’ll hear every possible opinion!

The actual URL of the page is not quite what I am using here, rather it is a request handler page with the appropriate paramters added to the end.

The format is like this:

http://webapplication.company.com/WebApp/ext_request.html?action=requestEntity&entityId=123456

where 123456 is the customer ID.

In visual studio, various items have the ‘Action’ option available. In my case I am doing it on a cell in a tablix but you can add actions to charts or various other things to.

Right click on the cell –> Text Box Properties –> Action –> choose the “Go to URL” radio button

In the [fx] formula editor there, the URL takes this format:

=”‘http://webapplication.company.com/WebApp/ext_request.html?action=requestEntity&entityId=” & CStr(Fields!CustomerID.Value

BUT, that is only good if you want it to open in the same window. The general form as described by Erik Monchen for doing  a javascript link to open a new window is like this

=”javascript:void(window.open(‘”+ Fields!ReferURL.Value + “‘,’_blank’))”

Applying this to my situation yields this:

=”javascript:void(window.open(‘http://webapplication.company.com/WebApp/ext_request.html?action=requestEntity&entityId=” & CStr(Fields!CustomerID.Value) & “‘,’_blank’))”

Notice the apostrophe after the second ampersand. The javascript “open” function is taking two parameters each delimited by single quotes.

 

In summary,I provide the list of incomplete customer records complete with links so you can go fix the data.

Closing the loop is always fun.

 

2 thoughts on “SSRS dynamic hyperlink

  1. =SWITCH(Fields!InvoiceDescription.Value=”Single Phase Meters”,iif((Fields!TotalUnits.Value)0,”javascript:void(window.open(‘” +Parameters!ProjectELink.Value”‘,’_blank’))”,””),
    Fields!InvoiceDescription.Value=”Poly Phase Meters”,iif((Fields!TotalUnits.Value)0,”javascript:void(window.open(‘http://xxx.com/Reporting/InstalledSitesReport.aspx?service=w’,’_blank’))”,””))

    i tried like this but always returning blank page

Leave a Reply

Your email address will not be published. Required fields are marked *