URL Access to SSRS Integrated Mode Reports

We’ve got an active project at work to migrate our many (many!) SSRS reports from a 2012 native mode instance to a 2012 instance integrated into SharePoint 2010. There are a number of benefits to migrating to SharePoint, which I’ll cover in a future post, but there are also functions we need to replicate from native mode. Several of our reports get displayed on LCD screens around a development team’s area so they can track sprint burndown rates and progress towards software upgrade goals. These reports are displayed via SSRS URL Access, which allows us to render the report in its own window and with certain parameter and display configurations. David Peterson and I found that the configuration between native and integrated mode wasn’t necessarily a 1:1 swap, but our suffering is your gain, as you now get to cheat off our homework.

Exceptions and Provisos

• URL access behaves differently across browsers and even within browser versions. I haven’t tested every combo. I’ll wager even Microsoft hasn’t.
• My default environment is Windows 7 with IE 9 and Chrome, therefore that’s what I’ve tested with.
• I have noticed quirkiness between IE 9 for Windows 7 and IE 10 for Windows 8. The rendering behavior for a report with a default parameter works just fine in Win 7/IE 9, but in Win 8/IE 10 you have to manually click “Apply” to show the report. I haven’t determined how to get around this yet.

Report Viewer Display

Click on this screenshot so we’re all on the same page about what sections we’re working with today.

Scenario 1: Display the Report with Parameter Window Shown

Let’s start with something simple: a report with a parameter that we want to show with the available toolbar and parameter window. We’ll need to pass the ReportViewer (RSViewerPage.aspx) two parameters via the “rv:” querystring parameter:

1. The relative SharePoint site URL
2. The Parameter window mode. In our case, we want the default behavior – display it for use

Here’s the syntax:

https://myreports.mycompany.com/sites/SiteName/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/SiteName/ReportLibraryName/Report+Name.rdl&rv:ParamMode

Scenario 2: Display the Report with Parameter Window Collapsed

Let’s say we have a default parameter set, but we may want to leave our user the option of changing the parameter. Most of the time we don’t want valuable real estate devoted to the parameter window, but we want it handy if someone needs to tweak the parameter. We’ll use the same components as Scenario 1, except we’ll make a change to that parameter mode portion of the querystring.

https://myreports.mycompany.com/sites/SiteName/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/SiteName/ReportLibraryName/Report+Name.rdl&rv:ParamMode=Collapsed

Scenario 3: Display the Report with Parameter Window and Toolbar Hidden

What if we want just want the report – no toolbars, no breadcrumb header, no parameters, just that lovely chart to show on a SharePoint site page or on an LCD mounted for all the team to see. We need to tell the parameter mode, toolbar, and the header to go away and hide.

https://myreports.mycompany.com/sites/SiteName/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/SiteName/ReportLibrary/Report+Name.rdl&rv:ParamMode=Hidden&rv:Toolbar=None&rv:HeaderArea=None

Scenario 4: Mac & Chrome Variation for Textbox Parameters

What’s good for the goose isn’t always good for the gander, and that’s certainly true when it comes to browsers. If you have folks who prefer Chrome or Safari, you had better test like crazy. A quirk we found is that – and only for TEXT parameters – the ParamMode option gets ignored and the parameter window is always collapsed. We were not amused. Here’s a workaround I put together that exploits putting a generic text phrase in the parameter via “rp”. Basically, since the text parameter has a value – albeit a dumb one – the parameter window has to be shown. Take that, alternate browsers!

https://myreports.mycompany.com/sites/SiteName/_layouts/ReportServer/RSViewerPage.aspx?rv:RelativeReportUrl=/sites/SiteName/ReportLibrary/Report+Name.rdl&rp:ParamName=FillInHere

As you can see, you can combine “rp” and “rv” in the same URL, as the “rp” portion is controlling the parameter. You could fill in a whole string of real parameter values if you so chose. Go nuts, what’s it going to hurt? But please test. And not on prominently displayed LCD’s, either.

Scenario 5: Provide a “Click Here to Download the Report” link

Let’s say you want a link on a SharePoint site page that lets the user download a PDF of the report without having to devote the site real estate to rendering the report itself. You can leverage giving them a report off of live data and in a format that’s friendly across many devices and platforms – PDF’s are mobile friendly, which is what we all want to be, riiight?? Never mind the squinting, they can open it on their iPhone, all rejoice!

Back to the syntax. This time, we’re not going to use the ReportViewer commands. Rather, we’re using the ReportServer (rs) commands, specifically Render and Format.

https://myreports.mycompany.com/_vti_bin/ReportServer?https://myreports.mycompany.com/sites/SiteName/ReportLibrary/Report+Name.rdl&rs:Command=Render&rs:Format=PDF

Happily, this command setup seems to be browser-agnostic.

Additional References

Here are several sites/articles I found most helpful for translating the different querystring options for URL access. While they’re not all specific to SSRS 2012, they suited our purposes anyway.

• Passing URL report parameters to Reports in SharePoint Document Library in SSRS 2008 & R2
• Passing URL parameters to Reports in SharePoint
• URL Access Parameter Reference

Now go forth, make URL’s, and be merry!