One of the great things about SharePoint is the ease with which you can create a list, and immediately have an online form people can use to enter data. Of course, once you have data in your SharePoint list, you’re inevitably going to want to implement some type of reporting against it. What’s the point of collecting all of this data if we can’t gain insight from it anyway?
For some scenarios, reporting against your SharePoint list might be as simple as creating one or more custom views for the list that group or filter the data in a meaningful way. More often than not, however, I see users with needs that require functionality above and beyond what a simple list view can provide. They might need charts, cascading parameters/filters, sorting capabilities, and all the other common functionality we’ve grown accustomed with from modern reporting. Let’s take a look at a few options that deliver all the bells and whistles most users are looking for.
Reporting services: Reporting services allow us to connect directly to a SharePoint list as a data source. Once a SharePoint list data source has been created, you can then create data sets that query the list using the Query Builder tool inside the reporting services development environment.
Using reporting services in this manner allows you to query the data directly for real-time reporting against your list, but it does come with a few drawbacks. The language used to query SharePoint lists (CAML) isn’t as robust as other query languages you may be familiar with (T-SQL/MDX/DAX). For example, creating a distinct list of values from a list column (something commonly needed for report parameter dropdowns) takes some additional effort and a little scripting to achieve.
PowerPivot: PowerPivot can connect to SharePoint lists using OData feeds. Setting up this connection is as easy as locating the correct URL for the endpoint and walking through a simple connection wizard in your PowerPivot window.
Using this method, PowerPivot will download and store all of data currently in your SharePoint list, so your PowerPivot report will only be as current as the last time the workbook was refreshed. Having your SharePoint list data stored in the PowerPivot workbook does have the added advantage of being able to leverage the full capabilities of PowerPivot while reporting against your SharePoint lists, such as relating your list data to other sources, and in creating additional calculations using the DAX language.
If none of the reporting options mentioned sound like a good fit for you, you can always opt to pull the data out of your SharePoint list and into your preferred database engine using a tool like SQL Server Integration Services. Determining which reporting solution is right for you will most likely depend on current license and the amount of data that exists in your SharePoint list. Happy reporting!
Tavis Lovell is a consultant with SharePoint911, a Rackspace company.
