1

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. enter image description here

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.

Istaley
  • 359
  • 1
  • 6
  • 24
  • Let me see if I understand your issue, when you are viewing the report via a browser, the links in the report point to the correct drilldown report and render the desired report correctly. When you receive an Excel version of the report via a subscription, the links in the report do not work properly. When you click on these links in the Excel file, what happens? What error do you get? Please amend your question with this information. – Christopher Brown May 18 '16 at 20:19
  • That is correct, the links work fine in the browser and when I export the report to Excel they work as well. The problem is when I receive the excel file as a subscription. I will go ahead and amend my question because I am unable to put a screen shot in the comment – Istaley May 19 '16 at 12:58

1 Answers1

2

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:

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

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

Christopher Brown
  • 2,170
  • 1
  • 13
  • 19
  • Thanks so much for your quick response. I initially tried to make a parameter in the past to determine whether to use javascript for a popup window (in the browser) or to simply navigate to a new tab from Export to Excel. That new parameter caused errors when scheduling subscriptions. I am sorry, I can't remember the error description off hand. I ended up taking that code an altering it and making it a variable. Is there any reason I couldn't do what you stated above as another variable, instead of as a parameter? – Istaley May 19 '16 at 14:37
  • I have actually never used variables, but I suppose you could. Keep in mind that hidden parameters do not require a default value. However, if they have a default value, then that is what the subscription would use.Furthermore, for hidden parameters, I don't believe a subscription will give you the option to change the value. – Christopher Brown May 19 '16 at 15:08
  • Unfortunately RenderFormat is not allowed to be used as a Variable. I get the following error message: "The Variable expression for the report 'body' refers to global variable RenderFormat, which is not valid for this type of report item expression. I also should have mentioned that we use the same SSRS solution for two separate production sites, so not only is there an abc site there is also a def site – Istaley May 19 '16 at 16:47
  • 1
    An alternative solution would be to build a reference or lookup table. I'm in the process of doing this for my organization. Any time one server looks for another, I query a table and say what framework/application I'm in and what framework/application I'm looking for along with the environment (i.e., TEST or PROD), and it returns the URL or whatever other value I store. – Christopher Brown May 20 '16 at 17:16
  • Thanks so much, I went this route and after a slow start, I was able to get it working! – Istaley Jun 01 '16 at 18:38