By: Christian Holslin

Back in the Day

Back in the day of SharePoint 2007 and SQL Server 2008 (before R2) it was very difficult to create reports using native SharePoint lists and libraries.  Short of hand-coding a SOAP package for the SharePoint web services layer, or dealing with the immaturity of PerformancePoint Server 2007, you really had very few options.  If you needed the power of SQL Server Reporting Services you might as well just shadow-copy the List data into a SQL Server data mart using an Event Receiver.  This method, while effective, was simply a sleazy work-around to an otherwise unmanageable problem.  With SharePoint 2010 and SQL Server 2008 R2 this problem is gone.  Reporting Services can now natively communicate with SharePoint 2010 Lists.

Set Yourself Up

To see how easy it is now you will need a couple things:

(1) SharePoint 2010, running on

(2) SQL Server 2008 R2

SharePoint doesn’t have to be running on SQL Server 2008 R2, but if you’re planning an installation for development, testing, experimentation, or otherwise, this is what we recommend.  Using the Business Intelligence Development Studio on the SQL Server machine I will show you just how easy it is now to natively report against SharePoint list data.

Create a Problem

Not something we should regularly do – who needs more problems anyway? – but for testing new technology it’s always nice to have a problem to solve as it will give you much needed direction.  My problem (or question I need answered), which I will be solving for this blog post is: How Much Work Do My Employees Have Left?  As a manager for a Systems Integrator, a sustainable utilization percentage is the key to success.  Work your employees to death and they quit.  Sit around doing nothing and go broke.  Simple, right?

Understanding utilization as being a measure of volume of work performed by billable employees, which is directly proportional to the success of the company, you can imagine how important it is to know how much work remains to be done.  To model this data, I create a simple SharePoint 2010 List called Resources.

Figure 1: Resources List

List Columns

Our list is very basic: in simple terms it captures the Resource (person), Project, Time (estimated duration of work to be done), and when to begin (Begin Work).  In data warehousing terms this is a single Fact Table with three Dimensions: Resource, Project, Start Date and one Measure: Time.  The Time measure will help us compute utilization.  Now let’s fill up the list with data points:

Figure 2: Resources Data

Now that we have our data we can create a report to figure out how much work we have left.  We’ll also compute the utilization percentage in the meantime.

Create a New Report

Open BIDS (Visual Studio 2008) and create a new Report Server Project.  Select the Business Intelligence Projectscategory first, then select Report Server Project.

Figure 3: Report Server Project

Add a Shared Data Source

This is the part of this solution which is so much easier than it used to be.  Right-click on the Shared Data Sources folder in your new Project and click Add New Data Source.  In the Type menu select Microsoft SharePoint List.  Wow, this seems so much easier now!  Yes, it really is.

Type in the Connection string: this is the URL to the SharePoint site where your List resides.

Figure 4: Data Source

Add a Shared Dataset

To get the data from your list you need to add a dataset.  Right-click on the Shared Datasets folder in the Project and click Add New Dataset.  You will see the familiar dataset query window, except in this case, rather than painstakingly hand-coding a CAML query, click on the Query Designer… button.  You will see this handy dialog which allows you to simply click the check box next to the list you want to use.

Figure 5: SharePoint List Query Designer

By simply clicking on the check box next to the Resources list I automatically get all the Fields contained within the list.  Now I can use them to build my Utilization Report.  Wasn’t that easy?  After you hit OK and close the Query Designer you will see the CAML query show up in the Query box.  Click OK to save the new Shared Dataset you created.

Create a Report

Now it’s just a matter of creating a new Report, adding the shared dataset, and throwing it up on the design surface.  For this report I created a simple Table then dragged the Resource, Time (Hours), and Begin Work fields onto the table.  I then added a parameter called Current Date which allows me to set the current date (a rough approximation).  As I change the date you can see the Report shows me who has work to do for the given week.

Figure 6: Work Starting on July 5

Figure 7: Work Starting on July 12

And just like that I can build business intelligence within my organization, in a remarkably short amount of time (although this is a very rough example) just by using native SharePoint Lists and nothing fancier than a Table and one Parameter.

Going Forward

The integration between Reporting Services and SharePoint has really come along way and I’m happy to see how seamless it is now.  Hopefully this blog post helps get you thinking about the many ways you can leverage this new integration between two incredibly powerful systems.