It’s Time to Work Your SharePoint Ninja Magic

As companies rely more and more on Sharepoint 2007 as a central collaboration and business intelligence (BI) platform, there comes a time when the big wigs might ask you – the local SharePoint Ninja – to develop some pretty charts and dashboards for their viewing and decision making pleasure.

“No problem”, you respond.  But you haven’t the slightest clue how to build a SQL Server report, and you sure don’t know how to make it centrally available to the power users within the company.

If you have data from line of business systems already in SQL Server 2008 or in SharePoint lists (like, for instance – a company outlook task list stored in sharepoint), you are already on your way to a solution.  This data is hungrily awaiting calculations, aggregations, and manipulations into colorful bar graphs, tables, and pie charts using SQL Server 2008 Reporting Services (SSRS) and SharePoint 2007 (WSS 3.0 or MOSS).

This series of articles will walk you through a basic setup and configuration of SSRS, SSRS extensions for SharePoint 2007, and how to build a report using line of business SQL data or data in a SharePoint list.  In my opinion, the SharePoint ninja’s best weapons for building SQL Server Reports are Report Builder 2.0, and SQL Server 2008 Management Studio.  With the tailored reports and SharePoint acting as the central path to business enlightenment, you and your peers will realize the robust capabilities of this often underutilized technology.

Some Pre-requisites

In order to integrate SharePoint 2007 and SQL Server Reporting Services 2008 you’ll need to have a few pieces already in place:

1. You’ll need to have a SharePoint 2007 (WSS 3.0 or MOSS 2007) farm in place and functioning.
2. You’ll need to have SQL Server 2008 up and running.
3. You’ll need to have already installed SQL Server Reporting Services (SSRS) on either a database or application server (I installed it on a SharePoint database server).  This should be an option on the SQL Server installation disc.
4. You’ll need to download the Microsoft SQL Server 2008 Reporting Services Add-in for SharePoint and copy the rsSharePoint.msi out to all web front ends in the SharePoint farm (Some web parts will be installed on each WFE).
5. Download Microsoft SQL Server 2008 Reporting Services Report Builder 2.0 to your local workstation.

Stay tuned for part two!