Planning for SharePoint Integration

SQL Server 2008 R2

Reporting Services supports two levels of integration with SharePoint products. Full integration is supported through the SharePoint integrated mode deployment scenario. Partial integration is supported through a pair of Web Parts that you install on a SharePoint site and point to a remote report server instance.

SharePoint integrated mode enables Reporting Services to integrate with the SharePoint databases and security model. These features become available when you configure a report server to run within a larger deployment of a SharePoint 3.0 product or technology. This mode requires additional software and configuration; you download and install an add-in component, create a report server database to store cross-server content and application data, and configure integration settings on both servers. This mode requires that you dedicate a report server instance for integrated operations. The advantage of doing this is a rich level of integration that allows you to access and manage report server content types using the application pages and data stores of a SharePoint Web application. For more information, see Configuring Reporting Services for SharePoint 2010 Integration.

Partial integration is supported through the Report Explorer and Report Viewer Web Parts that were first introduced in SQL Server 2000 Reporting Services Service Pack 2. These Web Parts continue to be available and enable you to select and view reports from a report server that is configured to use native mode.

A report server in SharePoint integrated models supported with Windows SharePoint Services 3.0 or Office SharePoint Server 2007. If you are using an earlier release of a SharePoint product or technology, you can still access report server content from a SharePoint site using the two Reporting Services Web Parts that work with a native mode report server, but you cannot run in SharePoint integrated mode. You can also use the Web Parts with a 3.0 release of a SharePoint product but you must add the Web Part assemblies to the global assembly cache (GAC).

To help you select a deployment strategy for integrating a report server with a SharePoint Web application, this topic provides a comparison of the integration features that Reporting Services supports for the 2.0 and 3.0 releases of SharePoint products and technologies. For more information about deployment modes, see Planning a Deployment Mode.

The following table compares newer and older levels of integration features. If you are planning a new deployment, or evaluating the new integration features for your organization, the table can help you weigh the advantages and disadvantages of each implementation.

Comparison point

SharePoint integrated mode

SharePoint 2.0 Web Parts

Key integration features

Provides a user interface (UI) to administer, secure, manage, view, and deliver reports by using SharePoint products and technologies. Reports, data sources, and data models are stored, accessed, and managed in a SharePoint library.

Integrates Reporting Services with SharePoint databases and security. This configuration enables SharePoint to be the front-end server that users access, and Reporting Services to be the back-end server that provides reporting capability.

A single, all-new Report Viewer Web Part displays reports in either full-page view or within a SharePoint Dashboard. It supports the filter and row consumer interfaces, which connect to SharePoint filter Web Parts or other SharePoint Web Parts, such as the Reports Library Web Part or Shared Document Library Web Part. The viewer also provides properties to customize the Report Viewer Web Part.

A free Web download of the Reporting Services Add-in provides a setup program that adds report server application pages to a SharePoint Web front-end.

Support for data-driven subscriptions and the ability to manage jobs programmatically and by using SQL Server Management Studio in SQL Server 2008.

Two Web Parts provide access and view capability, but no management features:

Report Explorer Web Part shows a list of reports that are stored on a report server.

Report Viewer Web Part displays a report.

These Web Parts cannot be connected to other SharePoint Web Parts.

Data storage

Integrated storage:

Users publish or upload reports, models, and data sources to a SharePoint library.

Reports, data sources, and data models in a SharePoint document library are stored as files in the SharePoint content database for that SharePoint document library. Files are stored in the .rdl, .rsds, and .smdl extensions. When a user requests to view or manage a report, data source, or data model, Reporting Services synchronizes its contents with the SharePoint content database. For more information, see Storing and Synchronizing Report Server Content With SharePoint Databases.

Report metadata, such as schedules, subscriptions, and the cache, are stored only in the report server database.

No storage integration:

Report server provides sole storage for items processed on and managed by the report server.

Security and permissions

Integrated security:

Authentication providers and permissions defined in the SharePoint Web application are used to control access to report server operations and content. To align and secure Reporting Services items and operations, the report server performs security mapping of SharePoint permissions to Reporting Services permissions. For more information, see Security Overview for Reporting Services in SharePoint Integrated Mode.

Separate security:

Report server provides security for items and operations managed by the server. Report access and content management is performed through the report server tools.

Content creation

Publish reports, models, and data sources directly to a SharePoint library by uploading them in SharePoint or from Report Designer or Model Designer.

Generate models, secure models, and link reports to entities in a model from a SharePoint Web application.

Create and edit reports using Report Builder from a SharePoint Web application.

Create data sources by using the Report Data Source content type in the SharePoint document library.

All report server content is created in client tools and in Report Builder that launches from Report Manager.

All content creation and management is handled exclusively through Reporting Services client tools.

Product and version requirements

SQL Server 2008 or SQL Server 2005 SP2.

You must also have Windows SharePoint Services 3.0 or Office SharePoint Server 2007.

Earlier releases of Reporting Services also provided integration support for SharePoint products. Specifically, SQL Server 2000 Reporting Services SP2, SQL Server 2005, and SQL Server 2005 SP1 provided two Web Parts (Report Explorer and Report Viewer) that you can register and use in a SharePoint Web application. The Web Parts were designed for SharePoint version 2.0 technologies. The Web Parts continue to be available in SQL Server 2008 R2 and can be used in a deployment of a SharePoint 3.0 product or technology. The Web Parts have not been updated to include new features or capabilities; if you are currently using those Web Parts, they will continue to provide the same level of functionality as before.