When your business user wants a report based on the data stored in your list, you may want to leverage SQL Server Reporting Services for this. In this case to to retrieve the data in your reports, the available options are:
- Query the SharePoint database. Although it’s an option, this is NOT AT ALL recommended. Don’t touch your SharePoint content databases directly! Not only that it’s complicated to query it, it also adds extra load to the server that is outside of SharePoint’s control. MS also places no guarantee that the structure of the db will not change when patches or service packs are applied. Your changes may get overwritten, and you will have to re-do them, or re-architect your solution Throwing more light on this
- Duplicate your data. You can either create an event that inserts into a parallel database when a record is inserted/updated/deleted from your SharePoint list, or dump the data to a database using a batch job that runs on a regular basis. If you choose the latter, you can go with either a console application, or PowerShell . The advantage here is you gain awesome speed in your reports. It also makes your report creation a lot easier! The big disadvantage is the latency, which may or may not be tolerable depending on your business.
- Use Web Services But it is not great in terms of performance, and you cannot query and correlate multiple lists (for example if you need to “join” lists together in one report)
- Use a reliable, third party tool