SQL Server Reporting Services 2005 and 2008 added a new SharePoint Integration mode, which allows reports to be stored and managed in a SharePoint 2007 portal. Data connection, reports, security, report subscriptions and notifications are all managed in SharePoint 2007.
Advantages Of SharePoint Integrated Mode
In SharePoint Integration mode, reports can be rendered using Reporting Services web parts. These web parts can be added to pages throughout your site. You can link report web parts to filter web parts, to provide parameters to one or more reports on a page, which allows you to create Reporting Services dashboards. You can even mix and match them with Excel Services charts, spreadsheets, and pivot tables on a single dashboard page. Powerful stuff!!
RSViewerPage and the Report Viewer Web Part
When you configure Reporting Services to run in SharePoint Integration Mode, reports are displayed using the /_layouts/ReportServer/RSViewerPage.aspx page by default. This page provides an a SharePoint look and feel, and integrated navigation back to your SharePoint site. It leverages the Report Viewer Web Part to display the report. The Report Viewer Web Part has some very nice features:
- An integrated SharePoint look/feel
- Cookie crumb navigation back to your site, MySite, and MyLinks
- A collapsible parameter panel to the right of the report
- A nice toolbar with zoom and page navigation
- An Actions menu on the toolbar, allowing users to:
- Export to various formats
- Customize the report using Report Builder
- Create report subscriptions
HTML Viewer in Native Mode and SharePoint Integrated Mode
When you configure Reporting Service to run in Native Mode, reports are displayed using an HTML Viewer interface that is built into Reporting Services. The HTML Viewer provides all of the basic functionality users required to view, navigate, and print reports. It also provides many Url parameter options to control report parameters and rendering rendering – options that are not available when viewing reports using the RSViewerPage page and the Report Viewer Web Part.
Yes, You Can Use the HTML Viewer in SharePoint Integrated Mode
When you configure Reporting Services to run in SharePoint Integrated Mode, it’s your choice. You can use either the RSViewerPage or the HTMLViewer to view your reports. The look and feel of the HTML Viewer isn’t as nice as the RSViewerPage interface, but it’s equally functional, and it provides a lot of Url options that can’t be duplicated using the RSViewerPage.
In the HTML Viewer, the report parameter options are displayed at the top of the page (the Report Viewer Web Part shows them on the right-hand side of the report). You can use the HTML Viewer user interface to view the report, change parameters, collapse parameters, zoom, print, export, etc. When you use the HTML Viewer to view your report, you lose the SharePoint UI look and feel, cookie crumb navigation, MySite and MyLinks integration, and Report Builder and Subscriptions integration. You gain the Url options we’ll be discussing later in this post – options that are not supported by the RSViewerPage or the Report Viewer Web Part.
How To Display Reports Using the HTML Viewer Page
You still have the ability to call reports using the HTML Viewer page by providing a link to the ReportServer Virtual Directory with a full path to the rdl file, like so…
When you view your report in this manner, you are bypassing the SharePoint Integrated RSViewerPage.aspx page and the web part, and viewing the report using the native mode HTML Viewer. The rs:Command=Render Url parameter instructs reporting services to display the report.
Passing Report Parameters in the Url
When you provide a link to the HTML Viewer, you can use the parameter support in the HTML Viewer to control the display of the report. Assume a report that accepts 3 parameters:
- Region (integer)
- StartDate (date)
- EndDate
By adding Url parameters on the end of the HTML Viewer Url, you can easily pre-populate the report parameters for the user.
As you can see, with this sort of report, you can provide a flexible report that can be used for multiple purposes. You can easily swap out these links in code to insert the department and dates appropriate in the context of the application displaying the links. Parameter values in the Url override default parameter values.
If all the parameters are supplied by Url or default parameter settings, the report is rendered automatically, without any further action required from the user. Nice!!
Passing Report Rendering Instructions in the Url
Using the HTML Viewer, you can also control the rendering of the report. You can set the rendering format (HTML, PDF, EXCEL, CSV, etc.), the zoom level, and a bookmark or default page displayed in the report. You can also toggle the appearance of toolbars, parameters, and the document map. You can even provide default search criteria to display and modify the style sheet used to render the report, or set the report to display a specific page.
As an example, the following link will show the report at Whole Page zoom level, with parameters collapsed and document map displayed, with the last page displayed by default.
In the HTML Viewer, there are many options available that can be used to controlling report rendering. This is a short list of the of the ones you’re likely to find useful:
- rs:Format – Rendering modes you can pass are HTML3.2, HTML4.0, MHTML, IMAGE, EXCEL, WORD, CSV, PDF, XML, defaults to HTML4.0
- rc:Zoom – Specified in percentages, supports Page%20Width and Whole%20Page, defaults to 100%
- rc:Toolbar – True/False, used to show/hide the toolbar, defaults to true
- rc:Parameters – True/False/Collapesed, used to show/hide/collapse the parameters in the toolbar, defaults to true
- rc:DocMap – True/False, used to show/hide document map, defaults to true (not shown unless report has document map)
- rc:Section – Specifies default page number to display, defaults to 1
- rc:BookMarkID – Jumps to a specific bookmark in a report
- rc:FindString – Provides search criteria to the report and finds the first instance of the string specified
Links For More Information
For more information on Url parameters available for the Reporting Services HTML Viewer and Report Viewer Web Part, check out these links:
- TechNet – Reporting Services Url Access – Using URL Access Parameters
- TechNet – Reporting Services Url Access – HTML Device Information Settings
- MSDN Magazine – Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
 del.icio.us it!
|  digg it!
|  reddit!
|  technorati!
|  yahoo! |
Stuart's Tech Punch 
Nice article to come across. This article is very helpful to bring about the awareness of SQL server reporting services and url parameters in sharepoint. A collection of tutorials, Web page design and layout, and general advice distilled from Dmitry’s experience as a Web site designer. Though written some years back, his advice is still on-point and insightful.
Have you tested passing in URL parameters while in Sharepoint Integration Mode? In 2005 SSRS, you had to use a Sharepoint Filter Web Part Connection to send the URL parameters to the Report Viewer Web Part while in Sharepoint Integration Mode.
Yes, I have used this in SharePoint Integrated mode. It woks fine, but you have to get the url correct to bypass SharePoint and calling the ReportViewer.aspx page directly. This bypasses the Report Viewer Web Part, offering much more flexibility than you get with the web part + filter web parts.
I’ve described the url you need to use in SharePoint Integration mode in the blog posting. Try it, and if you have trouble getting it working, please post another comment on my blog so we can get a solution out there for the SharePoint community.
great post “rc:Parameters = True” came in really handy while trying to prevent the parameter area from collapsing.
I tried in Sharepoint Integration mode with the following URL, but it doesn’t work:
http://localhost/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=/Reporting/Reports/FP.rdl&rs:Command=Render&rc:Parameters=Collapsed&CC=8&Device=48
any idea is welcomed
Your url is incorrerct. Check your url against the examples above. Your url should not include _layouts.
http://localhost/ReportServer?http://localhost/Reporting/Reports/FP.rdl&rs:Command=Render&rc:Parameters=Collapsed&CC=8&Device=48
this link is ok, but it is not integrated with Sharepoint. Without _layouts it doesn’t work.
Great post!
I was wondering – is it possible to modify the RSViewerPage.aspx? I am working in SharePoint integrated mode and I would like to add the company logo to all reports. Or to be more precise with the question: have you found some recommendations on how to modify the RSViewerPage.aspx (it may be possible but not reccommended)?
I know I can use the RS web part and create custom pages but I would like to know what the options are.
Thank you!
Sure, you can modify the RSViewerPage.aspx. Just be aware that you’re taking a dive into unsupported territory, and that any changes you make there will be global for all reports, which sounds like what you want anyway.
Take a look at this post. Be sure to make a backup copy of the file first, in case you need to revert back.
Good luck!
Thank you very much!
Very useful information!
Sometimes I think Microsoft should learn a lot of things from guys like you.
I have dealt with MS Support 3 times for the last couple of weeks and I am not happy with what they show us. Being a consultant myself, I do recognize good work from bad work most of the times….
Thanks again!
Me again! 🙂
I am looking for comments in Reporting Services 2008 – the kind of comments the end users can put into certain cells/lines in the reports and add.
I know PerformancePoint has this OOB.
Have you ever found something in this area?
Thank you!
No, there’s no functionality like that in reporting services. Would be nice though!
I’m new to sharepoint development – I was given a task to create a site definition to automate creation of project sites. One of the pages in the site I am to mimic contains several Report Viewer webparts. I am able to create the page with the webparts in the onet.xml of the site definition. However, in the site provisioning code, which runs after the page is created, I am having difficulty accessing the properties of the Report Viewer webpart, specfically the ReportPath property. The Report Viewer webpart just doesn’t seem to be accessible through code. The SPLimitedWebpartManager sees it as an “Error Web Part”. Any ideas on how to get at the Report Viewer webpart through code?
Sorry, I’ve never tried that myself, so can’t say. It my not be available yet as the site is being instantiated. You may have some luck by stapling a feature to the site, and have the stapled feature modify the web part. In some cases, you have better access to web parts on the page that way. Google “Feature Stapling” for some samples. Worth a shot anyway. Hope that helps!
I notices my URL
http://devmoss2007vpc/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=/reportingservices/level1.rdl&rs:Command=Render&Services=4,5,3,6
does not work, the reason being I’m using SSRS 2005. Passing URL paramters works fine with SSRS 2008.
Probably you need to use HTML viewer when using SSRS 2008 in sharepoint mode
http://devmoss2007vpc/_layouts/ReportServer/RSViewerPage.aspx?RelativeReportUrl=/reportingservices/level1.rdl&rp:Services=4,5,3,6
URL does not work with SSRS 2005 hosted in sharepoint mode
Using same url with SSRS 2008 works fine.