Configure Reporting Services to Run in SharePoint Integrated Mode

By: Ray Barley   |   Updated: 2008-10-29   |   Comments (6)   |   Related: > SharePoint

Problem

We have heard about running SQL Server Reporting Services in SharePoint Integrated Mode but are not really sure how to go about configuring this.  Can you provide the details on what capabilities this provides as well as how to do it?

Solution

Probably the biggest benefit of running Reporting Services in SharePoint Integrated Mode is that you can deploy and manage reports directly in SharePoint rather than the Report Manager web application.  This is probably easier for our business users.  As a matter of fact you no longer use the Report Manager after you switch to SharePoint Integrated Mode.  The highlights of the capabilities provided by SharePoint Integrated Mode are the following:

• A Report Viewer Web Part that provides report viewing capability, export to other rendering formats, page navigation, search, print and zoom.
• Web application pages so that you can create subscriptions and schedules as well as manage reports, models, and data sources.
• Support for using standard Windows SharePoint Services features including document management, collaboration, security, and deployment with report server content types.
• A new delivery extension that you can use in subscriptions to deliver reports to SharePoint libraries.

You can review all of the details of SharePoint Integrated Mode in SQL Server Books Online.

This tip will walk through the steps to configure Reporting Services to run in SharePoint Integrated Mode.  The following assumptions will be made:

• You have installed Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server (MOSS) 2007
• You have installed Reporting Services 2005
• Both of the above are on a single server.  This is not a requirement; it just simplifies the installation.  You can get the details on the additional steps for a distributed install here.

Create a Report Server Database for SharePoint Integrated Mode

In order to run Reporting Services in SharePoint Integrated mode, you need to create a new reporting services database.  Start Reporting Services Configuration (click Start, All Programs, Microsoft SQL Server 2005, Configuration Tools, Reporting Services Configuration).  Click Database Setup and you will see the following:

Note that the Server Mode is Native; this is the default when you install Reporting Services.  Click the Change button and you will be prompted: Changing the report server mode requires creating a new report server database.  Do you want to continue?  Click Yes and the SQL Server Connection Dialog will appear:

Fill in the Database Name, make sure that Create the report server database in SharePoint Integrated mode is checked, then click OK.  The Task Status dialog will be displayed; click the Apply button to complete the database setup:

Leave Reporting Services Configuration open; we will return to it after the next step.

Install Reporting Services Add-In for SharePoint Technologies

The SharePoint code required for running Reporting Services in SharePoint Integrated mode is contained in the Reporting Services Add-In for SharePoint Technologies.  The add-in is packaged in the file SharePointRS.msi; you can download it here.  Launch the msi file to install the add-in accepting the wizard defaults.

Configure Reporting Services Integrated Mode in SharePoint

The final step in setting up Reporting Services in SharePoint Integrated mode is to perform configuration steps in SharePoint itself.  Return to Reporting Services Configuration and click on SharePoint Integration.  You will see the following:

Click the link Click here to go to SharePoint Central Administration.  This will launch a new browser window and navigate to the SharePoint Central Administration web site.  Click on the Application Management tab and you will be presented with quite a few options; the only options we are interested in are under the Reporting Services heading as shown below:

Click Manage integration settings.  You will be prompted to enter the Report Server Web Service Url and Authentication Mode; e.g. http://bi-moss:5150/reportserver and Windows Authentication.  Note that your Report Server Web Service Url will be different than what is used in this example.  In my case I installed SQL Server 2005 and allowed the installation to automatically configure Reporting Services.  I installed SharePoint on a new IIS web site but allowed it to run on its default port of 80.  Since the automatic Reporting Services configuration uses the default IIS web site and port 80, I changed the default web site to use port 5150 to eliminate the conflict.  Another point about the default configuration of Reporting Services is that the application pool identity will be set to Network Service.  You should change it to a domain account (e.g. the same one as the SharePoint application pool).

Click Grant database access.  You will be prompted to specify the server for the report server database.  In this example my server is BI-MOSS.  Note that your server will be different that what is used in this example.

You can just go with the defaults in the Set server defaults page for now.

Testing Reporting Services in SharePoint Integrated Mode

To test running Reporting Services in SharePoint Integrated Mode, we will use the Adventure Works sample reports that come with SQL Server 2005.  Open the sample project then click Project, Properties from the top-level menu.  Edit the properties as necessary for your environment; e.g.:

For TargetDataSourceFolder and TargetReportFolder I have selected a document library in my SharePoint site.  TargetServerURL is set to the URL of my SharePoint site collection.  This would normally be something like http://servername/reportserver.  However when you are running in SharePoint Integrated Mode, the URL must be the SharePoint site collection URL.

Right click the project in the Solution Explorer and select Deploy from the context menu.  Open a browser and navigate to the document library specified in the project properties above; you will see the following:

Note that you can also deploy reports to the document library by clicking on Upload.  Click on one of the reports to display it; e.g. Product Line Sales:

The Actions button provides the following capabilities:

You can also render a report by using the Report Viewer web part which is part of the Reporting Services Add-In for SharePoint Technologies.  With the web part you have additional control over the toolbar; the above report shows the full toolbar.  You can render the report with just the navigation part of the toolbar:

You can render the report without the toolbar:

Next Steps

• Give some consideration to running Reporting Services in SharePoint Integrated Mode.  As users become acclimated to SharePoint they may be more comfortable managing reports in integrated mode rather than having to resort to the Report Manager web application.
• From a security standpoint, you can leverage SharePoint security rather than having to replicate that security in the Report Manager.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips