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