Disabling hyperlinks when exporting SSRS Reports

There are times when we feel that the hyperlinks should be disabled when we export a SSRS report to other formats say Excel or PDF.

Prior to SSRS 2008 R2 this was not possible. But in SSRS 2008 R2, this is very much possible using a new global value called “RenderFormat”.

Note: You need Report Builder 3.0 or R2 BIDS.

I am creating a simple report on top of AdventureworksDW.dbo.DimEmployee (You can download the Adventure Works database from http://msftdbprodsamples.codeplex.com/)

The dataset query is as below

SELECT
 FirstName ,
 LastName ,
 Title,
 EmailAddress
FROM dbo.DimEmployee

 and in the report I added a table with four columns corresponding to the dataset fields. In the last column I have enabled “Go To URL” where I am passing the Email address to Google.co.in and searching for it. The expression for the same is  follows.

=”http://www.google.co.in/#hl=en&source=hp&q=” & Replace(Fields!EmailAddress.Value,”@”,”%40″)

When previewed it looks like

Clicking on the link will take you to the google result page for the particular email search.

When we export this report to Excel, the hyperlinks are also retained. But we don’t need it. So we need to modify the expression little bit as

=IIF(Globals!RenderFormat.Name = “RPL” , “http://www.google.co.in/#hl=en&source=hp&q=” & Replace(Fields!EmailAddress.Value,”@”,”%40″),NOTHING)

The Gloabls!RenderFormat returns the current render format. Using this I am enabling the hyperlinks only when it is rendered to browser (RPL) and for other formats I am setting it to NOTHING which means no hyperlinks.

Have a look at the below thread for more information on this

http://blogs.msdn.com/b/robertbruckner/archive/2010/05/02/globals-renderformat-aka-renderer-dependent-report-layout.aspx

One final thought, what is the advantage of removing hyperlinks?

If the hyperlinks are not needed in the exported files, better we remove it. Because hyperlinks cause the exported files to grow in size. In the above example, the size of excel, for 296 rows , with hyperlinks was 114KB whereas without it was just 64KB which means less data transfer across network.

Advertisements

About Suresh Kumar D

Hardcore SQL Developer and aspiring Database Architect.
This entry was posted in SSRS. Bookmark the permalink.

12 Responses to Disabling hyperlinks when exporting SSRS Reports

  1. Jason Thomas says:

    Informative article, thanks for this. Any idea why the size increases with hyperlinks?

  2. skamie says:

    Sorry, I could not find why the file size increases with hyperlinks.

  3. pulkit ojha says:

    good one Suresh….i was initially going for that meaningless report paramter driven excel export. but this is really very helpfull………

    thanks a lot for the brilliant article

  4. santosh S. says:

    Hi Suresh,
    The article is superb.But I have an concern. I have an report with hyperlinks. When I am clicking on the hyperlink it is showing some values .But my problem is I want to see the values when clicking on the hyperlink even after exporting to excel.
    Please help me out in this regard.

  5. Sean Hawkes says:

    What about drill through reports? Is there a way to disable the action on drillthough or sub-report based upon this as well?

    • Hi Sean,
      Yes, I was able to disable the action on the sub-reports also. In the “Go to Report” option, I wrote an expression like

      =IIF(Globals!RenderFormat.Name = “RPL”, “Report1”, NOTHING)

  6. HashamNiaz says:

    Thanks, it helped me alot.

  7. Khush says:

    This was a great article. Had been looking for this for quite sometime.

  8. Ravi Gupta says:

    You made my day, thanks 🙂

  9. Abhijay says:

    Thanks for the article. You made my day. Thanks again.

  10. Raja Shekhar Reddy K says:

    Hi Suresh,
    I am working on SSRS 2012. In main report having hyperlink. When click this hyperlink in browser (IE 11 or Chrome), it open sub report correctly. But once i download the main report to Excel 2013 and click hyperlink, it can’t open sub report.

    I am getting following error message in Excel file. Unable to open “http://…”. Cannot download the information you requested.

    In SSRS 2005, it is working fine.

    How can i fix this issue in SSRS 2012?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s