I’ve had this request from multiple clients. The scenario is usually that they have a PerformancePoint dashboard that contains a mix of PerformancePoint Analytic Chart reports and Reporting Services reports. The Reporting Services reports are usually just gauges or some kind of chart, but they want the ability to click on them and show the underlying detail data.
The problem is usually that the detail data they want to see is far larger than the space allotted in the dashboard for the original SSRS gauge or chart, so we need the detail data to open in a new window.
(More on PerformancePoint: PerformancePoint: ‘I’m not dead yet’)
Unfortunately, there isn’t really an option for “Open report in new window” in the settings (but that sure would be nice if anyone from Microsoft is reading this). So, to make this happen, we’re going to need to write a little JavaScript.
Assumptions for this walkthrough:
• SSRS is in SharePoint integrated mode (although you can do this in native mode as well).
• SSRS is either 2008 or above.
The short version of this post is that you basically need to add the JavaScript shown below to the “Action” property of whatever it is your user is going to click on in the report.
=”javascript:void(window.open(‘http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl’, ”, ‘width=1000, height=800, top=0, left=0, resizable=yes’))”
Where “http://mastvmbase…” is the URL to your report, and “width=1000…” is the dimensions of the new window.
The first thing we need to know is the URL to the report that we want to show in our new window. To get the URL, we’re just going to open the report in SharePoint and then copy it from the browser address bar. However, you’ve probably got some stuff at the end that you don’t need. For instance, my report is called BlogExampleReport, and the URL is:
http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl&Source=http%3A%2F%2Fmastvmbase%2Fsites%2FTavisBIDemo%2FSSRS%2FForms%2Fcurrent%2Easpx&DefaultItemOpen=1
We want to remove everything after “reportname.rdl” so it looks like this:
http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl
#!
Now that we have our report URL, we just add it to the URL in the JavaScript as shown above. Now we need to add our JavaScript to the report that the user will click on in the dashboard. In my case, this report is a gauge. So we’ll need to:
1. Right-click on the Gauge in design mode and select Properties.
2. Select Action from the Properties window.
3. Select Go to URL as the action.
4. Click the Fx button next to the Select URL field, and copy in our JavaScript.
That should do it. Just click OK on all the open windows, and then deploy the report to test it. NOTE: The report must be deployed in order for the JavaScript to work. It will not work in the preview window of your report designer.
Now let’s say we want to pass a parameter from our original report to the report that opens in the new window. We can also do this through the URL by adjusting our JavaScript like so:
=”javascript:void(window.open(‘http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/BlogExampleReport.rdl&rp:p_ReportParameter1=ParameterValue’, ”, ‘width=1000, height=800, top=0, left=0, resizable=yes’))”
In the above example, we’re passing a value of “ParameterValue” into our parameter named “p_ReportParameter1”.
(More on working with SharePoint: Which SharePoint business intelligence tool is right for you?)
It’s worth noting that you can also control several aspects of your report viewer toolbar by passing parameters in via the URL. There is an excellent post about doing so here.
While it’s a little messy and kind of a pain to manage through environments, the functionality is possible. If you have several reports that need to be moved between dev/uat/prod, I highly suggest leveraging shared data sets to store your base report URL: “http://mastvmbase/sites/TavisBIDemo/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/TavisBIDemo/SSRS/”. Doing so will allow you to change it in one place, instead of having to do so in each individual report when you deploy to a new environment.
Tavis Lovell is a member of the SharePoint at Rackspace team.
