Integrating SQL Server 2008 R2 Reporting Services with SharePoint 2010

By:   |   Updated: 2010-09-28   |   Comments (17)   |   Related: > Reporting Services Development


Problem

I'm ready to install SQL Server 2008 R2 and SharePoint 2010 in a test environment. I'm trying to decide whether to run SQL Server Reporting Services in SharePoint Integrated mode or native mode. What are the advantages of running Reporting Services in SharePoint Integrated mode? Can you provide an overview of what I need to do to get Reporting Services installed and running in SharePoint Integrated mode?

Solution

The biggest advantage to running Reporting Services in SharePoint Integrated mode is that you can deploy data sources, reports, etc. to SharePoint document libraries instead of the Report Manager web application that Reporting Services creates for you. Since your users are probably familiar with SharePoint this makes sense. In addition your users can take advantage of the new version of Report Builder that came with SQL Server 2008 R2 and deploy their reports to SharePoint document libraries, leveraging SharePoint for security. This is easier for them than using the Report Manager. The users can be much more self-sufficient with SharePoint.

In this tip I will highlight the steps you will want to follow to get Reporting Services installed and integrated with SharePoint. I'll be using SQL Server 2008 R2 and SharePoint 2010 Foundation. SharePoint 2010 Foundation is the new name for what was formerly Windows SharePoint Services. It is the version of SharePoint that is included with your Windows 2008 server license. The steps for SharePoint 2010 Server are essentially the same. In order to get a test environment up and running as quickly as possible, I will install everything on a single server. I'm using Windows Server 2008 R2 64 bit; 64 bit is a requirement for SharePoint 2010.

Installing SQL Server 2008 R2

At a minimum, install the database engine and reporting services. By installing SQL Server first you have the option of specifying the database engine that SharePoint will use. Alternatively you can install SharePoint and allow it to install its own instance of SQL Server Express (now called SQL Server Embedded Edition). There are two steps I want to highlight in the SQL Server 2008 R2 installation: Setup Role and Reporting Services Configuration.

In the Setup Role step, choose SQL Server Feature Installation as shown below:

In the Setup Role step, choose SQL Server Feature Installation

In the Reporting Services Configuration step, choose Install the SharePoint Integrated mode default configuration as shown below:

In the Reporting Services Configuration step, choose Install the SharePoint Integrated mode default configuration

This allows the install to do the majority of the work, limiting the steps that you will need to perform to get Reporting Services integrated with SharePoint.

Installing SharePoint 2010

In order to successfully install SharePoint, there are a number of prerequisites that must be installed. When you launch the SharePoint 2010 setup, make sure to first click Install software prerequisites as shown below:

In order to successfully install SharePoint, there are a number of prerequisites that must be installed

After clicking Install software prerequisites the following screen is displayed:

this step will automatically install anything that you need from the list of required products and updates

Note that this step will automatically install anything that you need from the list of required products and updates. In order to run Reporting Services in SharePoint Integrated mode, you need the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in; this will be done for you.

Now we are ready to install SharePoint 2010 Foundation. Click Install SharePoint Foundation on the splash screen (shown at the beginning of this section). Since we are interested in a test environment choose Standalone as shown below:

In order to run Reporting Services in SharePoint Integrated mode, you need the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in

The Standalone option is perfect for getting going quickly; it installs the software with all default settings. While this approach requires the least amount of effort on your part, it does install an instance of the SQL Server Embedded Edition that can only be used by SharePoint; i.e. you can't leverage this SQL Server instance for the Reporting Services database. In addition you truly get a standalone instance of SharePoint; you cannot add any other SharePoint servers. Despite these limitations, I'm going to proceed with the Standalone option.

When the SharePoint 2010 Foundation installation is completed, you will be prompted to run the SharePoint Products Configuration Wizard. You need to do that to in order to complete the configuration steps required to have a working installation of SharePoint 2010 Foundation. When the SharePoint Products Configuration Wizard is finished, your browser will open to the SharePoint Team site that has been created for you.

Configuring Reporting Services SharePoint Integration

At this point we have Reporting Services 2008 R2 and SharePoint 2010 Foundation installed. The next step is to configure Reporting Services SharePoint integration using SharePoint Central Administration. Launch SharePoint Central Administration from the Start menu (it should be pinned to the Start menu; if not then select it from All Programs, Microsoft SharePoint 2010 Products group). Click General Application Settings as shown below:

configure Reporting Services SharePoint integration using SharePoint Central Administration

Click Reporting Services Integration as shown below:

Click Reporting Services Integration

Note that the Reporting Services options shown above will only appear after the SQL 2008 R2 Reporting Services SharePoint 2010 Add-in is installed. I installed the add-in by running the Install software prerequisites option before installing SharePoint.

There are just a couple of things that you need to enter to complete the integration as shown below:

get the Report Server Web Service URL from the Reporting Services Configuration Manager

The following are the main points about the integration settings:

  • You can get the Report Server Web Service URL from the Reporting Services Configuration Manager (in the Microsoft SQL Server 2008 R2, Configuration Tools program group). Click on Web Service URL in the menu on the left.
  • When you have everything running on a single server as I do in this example, choose Trusted Account for Authentication Mode.
  • For Credentials you do need an account that is in the local Administrators group. With everything on a single server it can be a local account, but I'm using a domain account.
  • Choose Activate feature in all existing site collections.

After entering the required information and clicking OK, you should see the Reporting Services Integration Summary as shown below:

you should see the Reporting Services Integration Summary

At this point Reporting Services is configured in SharePoint Integration mode.

Preparing a Document Library

There is one last step that needs to be performed in order to create Reporting Services artifacts (e.g. shared data sources, report models, reports) in a SharePoint document library. We need to add the appropriate content types to the document library and customize the New Document menu options. For this example I created a brand new document library called Reports.

Navigate to the document library then click Library Settings as shown below:

Navigate to the document library then click Library Settings

SharePoint 2010 has a new ribbon menu. You have to click on the Library tab in order to see the above (the other option is the Documents tab). Click Advanced Settings on the page as shown below:

SharePoint 2010 has a new ribbon menu

Click the Yes radio button under Allow management of content types then click OK:

Click the Yes radio button under Allow management of content types then click OK

You will be returned to the library settings page; click Add from existing site content types:

click Add from existing site content types

Select the Report Builder Model, Report Builder Report, and Report Data Source content types from the Available Site Content Types List, then click the Add button:

the Report Builder Model, Report Builder Report, and Report Data Source content types

Click OK to return to the library settings page; click Change new button order and default content type:

click Change new button order and default content type:

Change the Visible property and Position from Top as shown below:

Change the Visible property and Position from Top

Click OK, then return to the document tab for the document library. Click New Document and you will now see the menu options as we have configured them:

Click New Document and you will now see the menu options as we have configured them

Click Report Builder Report to launch Report Builder. You should see Report Build 3.0 launch and you can now author reports, save them to a SharePoint document library, and of course run reports by simply clicking on them in the SharePoint document library.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley 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


Article Last Updated: 2010-09-28

Comments For This Article




Friday, March 22, 2013 - 6:13:33 PM - kartheek Back To Top (22976)

Thank you Raymond This is the answer that I am expecting. 

Friday, March 22, 2013 - 2:22:30 PM - Raymond Barley Back To Top (22971)

You may be able to deploy to sharepoint but in order to run a report that is stored in a sharepoint document library you need to have a report server installed in sharepoint integrated mode.  

Friday, March 22, 2013 - 2:12:10 PM - kartheek Back To Top (22970)

Thank you so much Raymond for ur Response

Now Lets consider My production Mssql server Is installed in native mode and My dev too.

Then still we can deploy the reports to Sharepoint library from Either of these systems i.e. with out any of these reporting services is Integrated in sharepoint mode

 

Thanks

Friday, March 22, 2013 - 1:30:52 PM - Raymond Barley Back To Top (22968)

Here's the MSDN link for publishing reports to SharePoint:

http://msdn.microsoft.com/en-us/library/bb283155.aspx

 

Friday, March 22, 2013 - 1:22:47 PM - Raymond Barley Back To Top (22967)

You can deploy reports from BIDS to reporting services in native mode or SharePoint Integrated mode; you just have to set the project properties appropriately.  Take a look at this post for an example of the settings for deploying to SharePoint Integrated mode:

http://blogs.msdn.com/b/bimusings/archive/2007/01/16/publishing-reporting-services-reports-to-moss.aspx

Friday, March 22, 2013 - 12:50:40 PM - kartheek Back To Top (22965)

Hi all

I have been working on Sending Bids(ssrs) reports directly to sharepoint folder

We know it will be possible through Sharepoint Integrated mode. We went through lots of documentations and we are succesfull with that part.

1. we had sharepoint integrated mode in the production, but in the development server it is in Native mode, But still from Dev mode I can able to deploy reports to Prod. How is it possible?

Thanks In advance

kartheek

Friday, August 3, 2012 - 5:09:54 AM - vik Back To Top (18899)

tats great hELPED ME ALOT ..THNX for ur help....and I have installed db engine and ssrs services at the same time...wer im getting option for native and integration mode...so before i did separatly wer i didnt get the option as its disabled dont know y Bar....Thnx for ur help...need ur help for my future ..too..great man ur.

Thursday, August 2, 2012 - 1:46:06 PM - Ray Barley Back To Top (18892)

Are you getting this error when you try to install SharePoint by running SharePoint.exe?  If so you should use a command line like this specifying the folder where you want the contents of SharePoint.exe to be extracted:

SharePoint /extract:c:\SharePointFiles

Then go to c:\SharePointFiles and run the setup.


In the tip I used the .iso file to install so I forgot about SharePoint.exe.

Thursday, August 2, 2012 - 11:41:09 AM - vik Back To Top (18890)

Hi ,

     Im getting error after starting the configuration wizard...telling me that the file already exists in so so folder...i went back to the folder deleted the file but wen i opened wizard again its creating another file...still getting same error telling the file with this name already exists...im confused...im trying to do this morning but im failinmg ....pls help me out

Friday, February 17, 2012 - 1:10:18 PM - Ray Barley Back To Top (16057)
Trying to answer again - my previous one got cut off You get redirected to _layouts/templatepick.aspx when you haven't selected a site template for your site collection. On this page pick a template (e.g. Team Site) then you can navigate to the site. You will see lists and libraries based on the template you chose.

Friday, February 17, 2012 - 1:08:02 PM - Ray Barley Back To Top (16056)
Typically you get redirected to _layouts/templatepick.aspx when you've created a site collection and you haven't specified the site template. For instance if I create a site collection in central administration and choose

Friday, February 17, 2012 - 12:36:17 PM - smiley Back To Top (16055)
sorry to brother you with this simple question. My SP is a fresh new install. when going to the SP from the Win08R2 server using URL http://localhost. It redirect me to http://localhost/_layouts/templatepickl.asp . So, I don't have the Projects and etc. What am I missin to see the Projects and et

Thursday, February 16, 2012 - 6:23:03 AM - Ray Barley Back To Top (16035)
The steps you are referring to are not performed in Central Administration. You do use Central Administration to create a web application and a site collection. The site collection is what normal users access; e.g. you may have an intranet with the url http://intranet.mycompany.com. You navigate to that url in your browser, click on some site (e.g. Projects), then click on Shared Documents which is a document library. Shared Documents displays a list of documents (e.g. Microsoft Word) and allows you to upload new documents, open/edit documents, etc.

Thursday, February 16, 2012 - 2:23:07 AM - smiley Back To Top (16032)
I am a bit loss on two areas. 1) In "Preparing a Document Library", what are the sequence of click in Central Admin to "Navigate to the document library then click Library Settings ..." 2) After "Change the Visible property and Position from Top... and click OK", what do I need to click to be able to "Click New Document and you will now see the menu options as we have configured them" Thanks.

Wednesday, June 1, 2011 - 10:53:34 AM - Ray Barley Back To Top (13952)

If you install Reporting Services on its own server you need to install the SharePoint Web Server front end components (i.e. WFE) on that server.  In addition installing Reporting Services on a different server than the database engine will require its own SQL Server license.  You should confirm with your Microsoft rep but I believe that is the case.

Here is the MSDN link for how to install and configure SharePoint Integration on multiple servers: http://msdn.microsoft.com/en-us/library/bb677365.aspx  You'll probably need to review if you want to install with multiple servers.

 

Wednesday, June 1, 2011 - 9:56:39 AM - Matt Back To Top (13950)

Question on what is required on a multiple server environment?
If SQL is installed on another system do I need to install SharePoint on the SQL server or do I just point it at my farm?  How does this interact with licencing?

I really do like your artical and it does make me want to jump right in and get started :).

Tuesday, September 28, 2010 - 7:30:14 PM - Samson J. Loo Back To Top (10213)
Well done! I was just going to ask if I could install sharepoint on my x64 Win7 system.... thanks for answering that!!!














get free sql tips
agree to terms