Reporting on SharePoint Lists Data

When create reports based on the data in SharePoint lists, you need to consider how to access data and how it is presented.

How to access data

There are 3 approaches to access SharePoint lists data for reporting:

1. Access SharePoint database directly. This option provides best performance and join capabilities. However if Microsoft change the database schema, the queries will be broken. Here is a example: Reporting on SharePoint lists from Microsoft SQL Reporting Services.
2. Access via SharePoint lists Web service. This option reduces the compatibility risks, but performance may become an issue, especially large volume of data is accessed. Also it will be challenging if need data joined from several lists.
3. Export list data to an external database and then reporting on the data from the database. This option cannot provide “live” data for reports.

There are some third party components based on these three approaches. Here are more details: Reporting on List Data in SharePoint.

How to present reports

There are also 3 types of UI to present the reports:

1. Web Parts. This option integrates very well with SharePoint, but may not has enough UI element to present complex reports. Following Web Parts can be used:
• Lists Web Part
• Data View / Data Form Web Part
• Content Query Web Part (CQWP)
• Business Data Catalog (BDC) Web Parts
• 3rd Party Web Parts
2. Reporting system. Reporting system like SQL Server Reporting Services (SSRS) provides powerful reporting capabilities. SSRS can access SharePoint lists via SharePoint list Web service using XML custom data source. Here is a example: SQL Reporting Services data from SharePoint lists. In SQL Server 2008 R2 SharePoint List Data Extension can be used.
3. Custom UI. This option is very flexible, but also costly. UI can be built using ASP.NET, Silverlight etc.