Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Configure Reporting Services to Run in SharePoint Integrated Mode


By:   |   Last Updated: 2008-10-29   |   Comments (6)   |   Related Tips: > 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.


Last Updated: 2008-10-29


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, February 07, 2011 - 10:24:43 AM - Bob Back To Top

Okay ... discovered on another website an obscrure yet game-stopper prerequisite ... you must be logged on to the server as the same user that installed SharePoint.  Now I suppose that is common sense, but the fellow that did the original install used his logon and I was using the SharePoint admin one.


Friday, February 04, 2011 - 1:37:10 PM - Bob Back To Top

Thanks for the quick response ... I have installed the correct download.  Before the installation started it gave an error to do with version.  I did a bit of research and found that uninstalling the SMO from SQL Server 2005 would help.  It did and the install of the SharePointRS.msi file was successful.  But at that point there was still nothing in Administration/Applications.

Next up I found somethin on running stsadm to install the webparts.  Again that was successful and likely why I am seeing the useless web parts.

Can you tell me if what the virtual directory and web service setups should match please?  I am not looking for an exact answer just confirmation of where they should all be the same or how linked.  Should there be a unique high level sharepoint url for the library?

Thanks

 


Friday, February 04, 2011 - 12:12:45 PM - Ray Barley Back To Top

This tip was based on SharePoint 2010 and SQL Server 2008 R2.  Try reviewing the earlier tip that was based on SharePoint 2007 and SQL Server 2005: http://www.mssqltips.com/tip.asp?tip=1615

One thing to keep in mind is that there are different versions of the Reporting Services Add-In for SharePoint and these are based on which version of SharePoint and which version of SQL Server Reporting Services you are using.  Here are two of the older ones:

SQL 2005/SharePoint 2007: http://www.microsoft.com/downloads/en/details.aspx?FamilyId=%20f4d4d0ae-e5d4-4ed1-8d78-7137578161ce&displaylang=en

 

SQL 2008/SharePoint 2007: http://www.microsoft.com/downloads/en/details.aspx?FamilyId=200FD7B5-DB7C-4B8C-A7DC-5EFEE6E19005&displaylang=en

 

 


Friday, February 04, 2011 - 11:59:36 AM - Bob Back To Top

We ar a bit late to the game but as they say better late then never.  I have kicked this installation around for serveral days and here is where I am stuck.  In Sharepoint MOSS 2007 on the application tab there is no Report Services section.  If I go to an individual site then I do have the report web parts. End of day I cannot connect a report.

I have a feeling that my error is in Reporting Services Configuration likely in the virtual directory or web service identity.  In IIS we had three Application Pools (Sharepoint - 80, Sharepoint -32169, and Sharepoint Central Administration v3) and three Web Sites (Sharepoint - 80, Sharepoint -Team, Sharepoint - Team Site, and Sharepoint Central Administration v3).  What to use or add I've got myself sufficiently confused.

I am relatively new to the company and Sharepoint.  They installed it a couple of years ago and have never used it.  the main reason we are back into it is the Project Server side, but we are planning on rolling out more sites to individual departments.  Any assistance would certainly be appreciated.

Thanks

 

 

 

 

 


Thursday, August 12, 2010 - 4:46:14 PM - Ray Barley Back To Top
Unfortunately when you configure reporting services you have to choose either native mode (standard with Report Manager, the way it's always been) or SharePoint integrated mode.  An instance of reporting services can only support one mode. 

 


Thursday, August 12, 2010 - 12:43:42 PM - Robert Bishop Back To Top
Can a single instance of Reporting service house both SharePoint integrated and Standard reporting services?  basically if I want both Standard MS reporting services and SharePoint integration do I need two installations of sql server reporting services


Learn more about SQL Server tools