Your Federal Quarterly Tax Payments are due April 15th Get Help Now >>

SharePoint and SQL Server Reporting Services by baligalery


									SharePoint and SQL Server Reporting Services

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.


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

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.


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

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.
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.

Okay, now that you have setup SSRS + MOSS on your farm, and have setup a database. Time to write a report.

I am going to author an uber simple report - something that shows me all customers in the Northwind DB, (Select * from Customers),
and they are grouped by Country.

Use the following steps:

#1: Fire up the Business Intelligence Studio, and create a new project based on the "Report Server Project" template.

#2: Once the project is setup, you should see two folders in your solution explorer - Shared Data Sources, and Reports. Begin by right
clicking on the shared data sources folder and choosing "Add a new Data source". Setup a new Data Connection like as shown below:
For the smarter ones of you, you may have noticed that I am developing on a different machine (moss2007), and using windows auth.
This is because I am not developing directly on the production server, I intended to demonstrate how you can also change the data
connection afterwards.

Go ahead and save the new shared data source.

#3: Add a new report - Right click on the Reports tab, and add a new Report.

       Choose the Shared Data Source "Northwind"
       Specify "Select * from Customers" in the query. Usually you'd want to write a better query than that :).
       Choose a tabular report
       Add Country to Page, City to Group, and everything else to Details as shown below -

       Format as necessary, Call the report "Customers".
       Hit Finish, BINGO you have your report.

Now there is plentiful else you can do here to make your report even more compelling, I'm going to skip over that, and jump straight to


Right click --> properties on your BI project, and change the properties to as shown below -
Note again - I have specified my dev environment variables above. These can easily be moved/changed in a production environment
later, and I will cover that shortly.

With the above values filled in, and assuming you're running and authoring these reports in the BI studio as a user who has rights to the
above site, right click on your project, and choose Deploy. If you have followed all steps properly, the report should have deployed

You may verify that by peeking into the two document libraries you setup earlier, and you will note that the data source, and the report -
both have been successfully deployed.

Viewing your report

Just go to the document library (http://moss2007/reports), and click on the report - and the report should run. :-)

You can also choose to drop the "SQL Server Reporting services report viewer" webpart, and configure it to show the report from the
document library using the steps below.

a) Add the SQL Server Reporting services report viewer webpart -

b) Point the webpart to the appropriate report using the dialogbox below -
c) Hit OK/Apply/Exit Edit Mode.

You should see the report running inside sharepoint as below -

AWESOME !!!! So I can author a report in BI studio, and run it inside a branded sharepoint site - complete with security, and bunch of
other stuff? WWWOWWWWWW!!!! :-)

What bunch of other stuff ;-).

Here goes -

Deploying to Production:

You can now happily move the Customers.rdl file and Northwind.rsds file to production. In fact, you can also easily change either the
rsds definition by simply clicking on Northwind.rsds and editing it using the web based form as shown below -
You can also choose to manage data sources on a per-report-level from the web UI as shown below.
But there are a number of other interesting options above -

Manage Subscriptions: You can choose to subscribe either a file system folder, a document library, an email address, or custom
anything, to have copies of the report be run and stored as say a PDF at regular intervals.

Manage Parameters: If in case you were using a parameterized report, you could use this option to control parameter values.

Manage Processing Options: Use this option to control various settings such as, running the report on live or cached data, processing
time, snapshotting options etc.

Edit in Report Builder: This is a REALLY REALLY cool click once application, that is best demonstrated using a Report Model - which
is what I am going to talk about next.

To top