Reporting Services « Stuart Cox’s Tech Punch

Entries categorized as ‘Reporting Services’

SQL Server Reporting Services – Url Parameters in SharePoint Integrated Mode

September 17, 2008 · 16 Comments

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

SSRSReportViewerWebPart

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.

SSRSHTMLtViewer

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…

http://myrsserver/reportserver?http://portal/reports/reportslibrary/regional%20sales.rdl&rs:Command=Render

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.

http://myrsserver/reportserver?http://portal/reports/sample%20reports/departmental%20sales.rdl&rs:Command=Render&region=2&startdate=9/16/2008&enddate=9/17/2008

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.

http://myrsserver/reportserver?http://portal/reports/sample%20reports/departmental%20sales.rdl&rs:Command=Render&rc:Zoom=Whole%20Page&rc:Parameters=collapsed&rc:DocMap=true&rc:Section=999

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