SharePoint and SQL Server Reporting Services - Installation and Setup

Posted on 12/23/2007 @ 1:34 AM in #SharePoint by | Feedback | 45424 views


  1. SharePoint and Reporting Services - Introduction
  2. SharePoint and Reporting Services - Installation and Setup.
  3. SharePoint and Reporting Services - Setup your database.
  4. SharePoint and Reporting Services - Authoring a simple report.
  5. SharePoint and Reporting Services - Enabling end users - the report model.

First of all, why bother with SQL Server Reporting Services?

If your organization isn't diving into the data to answer critical questions about the business it is running, it is missing out on a LOT.

There is an interesting simile. In the 1950's the United States was mostly a manufacturing economy. We used to produce cars for instance, our car factories were the envy of the world. Car manufacturers used to play a poker game with the steel mills. The steel suppliers would be able to charge higher prices if they knew that a particular car manufacturer has very little steel left, and vice versa. As a result, the car manufacturer had to horde steel, and the steel manufacturer had to guess what the market really needed.

Over time, we moved to a "just in time" production scheme, where your business knew exactly what was in store, and what was to be ordered, and the market drove the prices - rather than the poker game.

As our economies continue to produce goods that are more and more conceptual in nature (the United States is no longer a manufacturing economy), such just in time production, is made possible by up to date business intelligence and data mining. Thus, products such as SQL Server Reporting Services, Analysis Services etc. are super important.

Okay good, but then, why bother with SharePoint & Reporting services?

Have you worked with reporting services? Did you see the web based portal it produces? Okay great - now security tie that down, and prepare a dashboard in conjunction with bunch of other valuable data and maybe even collaborative content maintainence areas. Most SQL server gurus would have a heart attack here, because the web based reports delivery mechanism that comes with SQL Server reporting services, albeit useful, is not known for it's high customizability :-).

That is where SharePoint steps in. SharePoint lets you create a portal that lets you store the various data source views (?), and reports inside sharepoint document libraries. It then lets users manage reports subscriptions, delivery, generation, rendering, and snapshotting - all via the sharepoint UI, which by the way is 100% brandable.

DANG!! This is something that was impossible to acheive on the Microsoft platform, without this combination, and yet such a minor thing to ask as far as the end business user is concerned.

Wow, I am impressed. So how do I setup my SharePoint farm to make use of SQL Server reporting services?

There are 2 steps - Installation and Setup.

Installation

Okay, you can mix up everything on a single server, but I am going to talk of a typical production setup - with 3 servers.

  1. Your MOSS Web front end (these may be multiple) (call it MOSSWFE)
  2. Your SQL Server (call it SQLDB)
  3. A machine with reporting services + developer box. (Call it MOSSReports)

What you need to do is,

a) Install SQL Server SP2 on your SQL Server & MOSSReports.

b) Install the SQL Server reporting services add-in (SharePointRS.msi) on the SharePoint 2007 web front ends.

Execute the following command on all WFE's -

stsadm.exe -o addwppack -filename "C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab"

This command will give you the necessary webparts.

c) Finally, install reporting services on MOSSReports, and also have the Business Intelligence Development studio installed either on the same machine, or on a seperate developer box.

That's it. Installation is done.

The next step is setup.

Setup

Okay, SSRS (SQL Server reporting services) can be used with SharePoint in 2 manners -

a) Native, b) SharePoint integrated mode.

Native is .. well .. you're still enamored to the ugly reports server UI, and you are using the HTML Viewer web part to render reports. IMO not very compelling.

SharePoint integrated mode is where you ditch the web UI that comes with reporting services, and instead rely on sharepoint to hold data sources and reports in it's document libraries, and offer all functionality via sharepoint. The functionality is very rich and can be compared with any top end reporting/BI platform.

Native mode - you can't really talk much about it, except - blah here is a report. So I'm going to leave it to you to play with.

Instead, I am going to talk about the cooler stuff - the SharePoint integrated mode.

In order to setup SharePoint integrated mode, run the Reporting Srevices Configuration Manager, and under database setup, create a new database under sharepoint integrated mode. Your sceen should look a bit like this -

 

Great. Now that your SQL Server side is setup, you need to perform some further configuration on your MOSS box. Use the following steps:

Step #1:

Go to central administration, under application management, go to the section that says "Reporting Services", and click on the first link that says "Configure Reporting Services Integration". Over here, you need to type in the Report Web Service URL, in our case http://mossreports:8080/ReportServer. Also, you need to specify the kind of authentication you intend to use. For convenience in a dev environment, you may use Windows Authentication, but to avoid the zillion connection pools in a production environment, you must choose Trusted Connection - so it allows you to use SQL Server auth.

Step #2:

Again, under central admin, application management, click on "Grant Database Access". This is hella important, because this gives the various accounts, SQL Server or MOSS, whatever database access they need for the integration to work. Specify the DB details, hit OK, and a dialogbox should pop up as shown below:

Enter the user id/password for the Config Account (farm account?) over here, and hit OK. This makes configuration so - less - painful :).

Step #3 (optional):

If you are using sharepoint integrated mode, you can set various defaults by clicking on "Reporting Services Defaults".

Step #4:

Provision a website, I provisioned port 80.

Create a site collection at / (root), I created one using the blank site template.

Go to Site collection features, and activate the "Report Server Integration Feature".

Create two document libraries

a) One a simple document library, call it "Reports" <-- this is where we will store our reports.

b) One a data connection library, call it "Data Connections" <-- this is where we will store our data connections.

GREAT! Now we're all ready to start authorin' reports!! w00t!

But, before you can author reports, you've gotta setup a database.

Sound off but keep it civil:

Older comments..


On 1/15/2008 8:29:05 AM phil said ..
Hi there,

I am implementing SSRS 2005 Sharepoint integration mode in a stand alone server and we are having problems with basic authentication.

When the MOSS web application and the Report Server application is in Windows Integrated authentication Everything works fine and we can upload and view reports in the sharepoint site.

But when we switch the MOSS web application into basic authentication and RS stays the same with windows integrated authentication the reports in sharepoint throw 401 unauthorized errors. It looks like the error occurs when accessing the _vti_bin/ReportServer/ReportService2006.asmx web service??

Anyone have any fix on this????

Does SSRS 2005 Sharepoint integration mode support Basic authentication???


On 2/7/2008 8:23:29 AM Raj Phalke said ..
nice article


On 2/9/2008 7:35:16 AM Raj Phalke said ..
i have Install everything now i'm getting below error:

Report Server has encountered a SharePoint error. (rsSharePointError) Get Online Help


User cannot be found.

my configuration is:


1 WebServer - MOSS with complete installation


1 Application Server (Central admin host) - MOSS with complete installation + Reporting Server SP2


1 SQL Server SP2

any solution?


Thanks in advance :)


-Raj


On 2/15/2008 3:54:57 PM Steve Schlipf said ..
It all looks very reasonable. I'm trying to implement on a single Dev box running SharePoint (Moss), SQL (sp2) and SQL Reports. I've run the add-in and set up the Reporting Srevices Configuration Manager. When I go to central administration there is no section that says "Reporting Services" under application management. I thought that perhaps it was implemented as a feature that hadn't been activated, but I don't see it in the list of features. I've uninstalled and re-installed the addin a couple of times... same thing. What am I missing???


On 2/20/2008 11:52:22 AM Rita Tavares said ..
I have the same problem: no "Reporting Services" under application management in Central Admin.

Any clues?


On 2/21/2008 6:50:11 AM Rita Tavares said ..
Found the solution in "MSDN SQL Server 2005 Books Online (September 2007) -> How to: Activate the Report Server Feature in SharePoint Central Administration":

"By default, the Reporting Services Add-in is activated for the root site collection of the local SharePoint Web application. If the root site is not enabled or if you do not have a site collection defined when the Reporting Services Add-in is installed, you must activate the report server integration settings manually.

To activate Reporting Services integration on a Site Collection:


1.Click Start, click Administrative Tools, and then click SharePoint 3.0 Central Administration.


2.Click Site Actions.


3.Click Site Settings.


4.Click Site Collection Features.


5.Find Report Server Integration Feature in the list.


6.Click Activate.


On 3/17/2008 4:22:49 PM Jeff said ..
Fantastic article! Thank you. I do have a question though. Is there a way, and maybe I am just missing it, but in the report builder UI, as far as I can tell it only references the actual SQL column names. This makes it very hard for me to figure out which SQL Column references which SharePoint column due to SP Architecture. I would have thought being there is this nifty SharePoint integration that it would be smart enough to find the correct SQL column and all I have to do is select a list, the column from the list and import it into my report. Am I missing something?

Thanks!


Jeff


On 3/19/2008 11:53:59 AM Andy said ..
There are other ways to integrate SRS and MOSS.

http://www.codeproject.com/KB/reporting-services/ReportFromSharePoint.aspx


On 5/27/2008 6:24:07 PM SuckerPunch said ..
Before installing, MUST READ Mike Walsh's article. States why the "Configure Resporting Services" may not display in central admin. Here goes the woes: http://mikewalsh.bilsimser.com/PermaLink,guid,3ff1194c-3b48-4f35-9797-a03327db8d00.aspx


On 7/2/2008 8:44:17 AM yang said ..
I've implemented on a single Dev box running SharePoint (Moss), SQL (sp2) and SQL Reports. I've run the add-in and set up the Reporting Srevices Configuration Manager.

i'm getting below error:

Report Server has encountered a SharePoint error. (rsSharePointError) Get Online Help


User cannot be found.

any help?


On 8/13/2008 3:46:36 PM jc said ..
shouldn't this step be performed on the SSRS server and not the WFE?

b) Install the SQL Server reporting services add-in (SharePointRS.msi) on the SharePoint 2007 web front ends.

I keep getting errors about the install being interrupted when I attempt to install in the WFE.


On 8/15/2008 3:05:28 PM JC said ..
Confused. Does my MOSS SSRS DB need to be on the WFE? Can my SSRS DBs on more than server? Can I use the SSRS server to Serve reports outside of MOSS?


On 10/8/2008 7:48:49 PM iti said ..
Can we view reports in MOSS 2007 without integrating it with SQL reporting services.


Please tell me all the ways to create reports in MOSS 2007.


On 3/4/2009 4:25:34 PM Sriram said ..
Hello, very nice article. I am having a problem doing the install. we have one web server with MOSS and one sql server which has all the content dbs. I did install the reporting services on the db server and when I go to the reporting server site I get an error message


"Report Server has encountered a SharePoint error. (rsSharePointError) Get Online Help


User cannot be found. "

Can you please let me know what is causing the problem.

Thanks,


Ram


On 3/28/2009 1:58:09 AM Akshaya said ..

On 4/16/2009 3:20:12 PM Kyle said ..
FYI you can find Mike Walsh's article at the updated link (since the one above no longer works):


- http://mikewalsh.bilsimser.com/PermaLink,guid,3ff1194c-3b48-4f35-9797-a03327db8d00.aspx


On 7/10/2009 2:04:35 AM siddiqui ashraf said ..
Thank's Malik Bhai


Because of you i have completed the Reporting services.


Thank's a alot.

Regard's


Siddiqui ashraf


On 7/30/2009 5:15:29 PM Zullu said ..
Hey,


When I try to deploy my Visual Studio report to the Report Center sharepoint site, i get this error:


"Report server has encountered a SharePoint error."

I have granted the App Pool Identity (in my this case it is "NETWORK SERVICE") access to my sharepoint site as a site collection admin, but still the same error.

Am I missing something obvious.

thanks.


Zullu.


On 5/24/2010 5:56:22 PM Obenash said ..
Is it possible to move Access 2003 database to a SharePoint and allow users access to the data?


On 5/31/2010 11:00:46 AM sirwan said ..
integrated sql server 2005 or 2008


On 7/9/2010 11:02:33 AM frist44 said ..
In configuring Reporting in Central Administration, when I click the grant database access, I choose my SQL server and then a username/password box appears, however, nothing I type will take. What UN/PW should this be?


On 7/23/2010 4:52:50 AM Hari said ..
HI,


I am running ssrs 2008 with sharepoint 2007 sp2. when i create a dataconnection from


sharepoint, it works fine for that day and agin next day the login details keeps on dropping from data connection and says pops up with the error that dataconnection has been disabled.


Any help on this would be really appriicated.

Thanks


Hari


On 12/16/2010 10:13:18 AM Stever said ..
Hi - I have set up a SSRS 2008 R2 Report Server in native mode - so I can send out a link to users and they can run reports from those links. I am thinking that I would like to switch this to a Share Point 2010 Integrated mode - where do I go from here - Do I need to reinstall the SQL Server 2008 R2 for Integrated mode? Do i install the Sharepoint on the same box - Windows 7 Pro? Can I do that?


On 5/11/2011 12:20:09 AM Sebastien said ..
For a tool to move reports / data source from one environment to another, see this blog post : http://goo.gl/adEiy


On 4/10/2012 5:02:43 PM Sindhu said ..
I have a situation wherein my reports are integrated with sharepoint. In one of the reports which has a drill down to another report, for the first time when we drill down it drills down to the correct location but when we go back to the parent report using the back arrow and then again drill down it doesn't drill down to the correct location, the link seems to have broken. How do I fix this issue?