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.