SSRS Scheduled Subscription hyperlinks not working

I am Currently using Reporting Services 2014. I am using the following code to be able to use a javascript pop-up window on the sharepoint site itself. The code also addresses the export to Excel link through an iif statement that evaluates whether the RenderFormat is being viewed on the site or if it is another format (Excel, Word, PDF etc). The iif statement uses a variable that replaces the ReportServerURL with the appropriate site. Everything works fine in when using the links on the site and when exporting to Excel from the site.

The problem occurs when I set up a scheduled subscription which sends an Excel file of the report. The ReportSerURL is different form the above situations. We have two sites that use http://gsp1/ReportServer and http://gsp2/ReportServer in the scheduled subscription excel file.

This is the code that I am currently using for using hyperlinks on the site and Export to Excel from the site. Is there a way that I can also incorporate some logic to address the scheduled subscription files?

SSRS URL Action (Uses Variable below)

=iif(
(Globals!RenderFormat.Name = "RPL"), 
"javascript:void(window.open('"+ Variables!RxDrill.Value + "&rv:ParamMode=Hidden&rv:Toolbar=None&rv:HeaderArea=None&rp:StoreKey=" + Fields!StoreKey.Value.ToString + " &rp:RxNumber=" + Fields!RX.Value.ToString + "&rp:RefillNumber=" + Fields!RefillNumber.Value.ToString + "', 'RXOVERVIEW','width=1335,height=450,location=no'))",
Variables!RxDrill.Value + "&rp%3aStoreKey=" & Fields!StoreKey.Value & "&rp%3aRxNumber=" & Fields!RX.Value & "&rp%3aRefillNumber=" & Fields!RefillNumber.Value & "&rs%3aParameterLanguage=")

Variable

=Replace(Globals!ReportServerUrl,"/_vti_bin","/_layouts/15") + "/RSViewerPage.aspx?rv:RelativeReportUrl=/SSRS%20Library/Rx Transaction Detail.rdl"

This is the error I currently get when trying to open a link in a subscription excel email file.

I dropped a [&ReportServerUrl] textbox and found that the path is not the same in the scheduled subscription as it is in the browser or during export to excel. I have found that there are 2 possible ReportServerURLs that the subscription uses. http://gsp1/ReportServer or http://gsp2/ReportServer. In the Browser it is always https://abc.myinfocenter.net/_vti_bin/ReportServer, but as you can see from the variable code above the _vti_bin is replaced.

Solution

It appears that the problem lies in the fact that when you load the report via sharepoint, you are retrieving the sharepoint server information using the Globals!ReportServerUrl value. However, with the subscription on a different SSRS server, you're retrieving a different server value: the server generating the subscription. SharePoint knows what server it's on and can tell SSRS that when you access the report via the SharePoint site. However, when you're generating the report on the SSRS server directly, the SSRS server has no idea the SharePoint site even exists.

To solve this, then, you will need to tell the SSRS server the URL for the SharePoint site. You could hard-code the SharePoint URL into an expression for the variable or elsewhere, but then that's a little harder to maintain. I would recommend solving this problem in the following way that makes maintenance a bit easier and more transparent:

Create a new parameter in your report called SharePointURL. Set the default value to https://abc.myinfocenter.net/_vti_bin/ReportServer or whatever it is when the report generates correctly in the browser. The reason you're doing this as a parameter is so that it's easier to maintain should your SharePoint site change. Set this parameter to be hidden or internal.
Change the expression for your variable to incorporate the SharePointURL parameter.

New Variable Expression:

=Replace(IIF(Globals!RenderFormat.Name = "RPL",Globals!ReportServerUrl,Parameters!SharePointURL.Value),"/_vti_bin","/_layouts/15") + "/RSViewerPage.aspx?rv:RelativeReportUrl=/SSRS%20Library/Rx Transaction Detail.rdl"

Hopefully that will fix your issue.