Suggestions for creating SSRS reports using data from SharePoint list

Posted: March 8, 2010 in SSRS

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
Advertisements
Comments
  1. Another option is SharePoint List Association Manager or SLAM, an open source project run by my company which many people use to report against SharePoint.

    http://slam.codeplex.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s