Now that you have collected all your information in your SharePoint lists, your business users will want to see reports off them.

You may want to leverage SQL Server Reporting Services for this (I would! I’d love to use SSRS for all the reports I need to create!), but we know it’s not an easy task.
Your options are:

• Query the SharePoint database. Although I say 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
   
• 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 (yes, yes, I’m a big PowerShell fanatic). The advantage here is you gain awesome speed in your reports (which could very well be readonly, indexed to the max, and “written” only during the bulk updates). 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
   

I’ve tried all of the above, and so far, my best bet is using a third party tool called Enesys RS Data Extension.

Enesys has made it easy to create data sources from SharePoint lists. And since I’m a database person, I am happy to report that they support a “limited” version of SQL syntax called SQLLite. Doing basic operations like aggregates, JOINs, UNIONs is fairly easy.

The best part is it plugs into Reporting Services with no issues! You just need to install the Enesys Server component, and away you go.

Interested to learn more? Have a look at these links:

• Reporting on List Data in SharePoint provides a good comparison, and matrix of pros and cons of the different ways to report off your SharePoint Lists.
• Reporting on List Data in SharePoint using Enesys
• Enesys Web Site

Related posts:

1. SQL Server PowerShell : How to View your SSRS Reports (rdl) Using PowerShell and ReportViewer ...
2. Creating a MOSS VPC Image: The whole 9 yards ...
3. SQL Server PowerShell : How to List SQL Server Services using PowerShell ...
4. DEVTEACH/VANTUG 2007 – Sharepoint Integration Presentation Materials ...
5. DevTeach/VANTUG Sharepoint 2007 Integration Presentation (Nov 2007): Resources ...
6. Step by Step Guide to Creating a SQL Server VM Using VMWare ...