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
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
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.