Creating SQL RS Reports based on SharePoint List

MOSS 2007 is a great product from Microsoft, and SharePoint Lists offers a great solution for saving data, and acting as a backing store, and to be honest it is very tempting to use it and build custom content types then bind them to a list, or you can build the list on the fly defining the UI artifacts you want to show to end users, Lists offer sorting, filtering, grouping, paging, minor/major versioning, and creating out of the box custom views, for printout purposes users can creat their views and then print it right away from IE, but that's not the perfect solution for reports especialy if your uses want to show custom reports with company's logo or adding a footer showing number of the records in the list.

MOSS offers wonderful and consistent UI, but for reporting it shows limitations, imagine you want to create a custom SQL Reporting Services ( RS ) report using a SharePoint list or document library as a data source, unfortunately SharePoint is not offering this, nor SQL RS, and it seems I am not the only guy on the planet who suffers from this, I have been searching around, and I can summarize the workarounds I have come to in the following points ( using the XML data source that SQL RS is offering) :

• You can give the URL of the RSS of SharePoint list or document library.
• You can call the respective webservice for the lis or document library.

As you can see, both solutions are not efficient and might offer slow performance.

SQL Server 2005 SP2 is offering out of the box support for RS reports, in a nutshell it's a SQL RS Add-in for MOSS 2007, you can run in two modes either Native mode or SharePoint Integration mode, or you can run both at the same time, what's new about SP2 is that you can save your RS reports in a document library and get the features of the library such as checkin/out and versioning, also using a click-once smart client application you can let your users compose their own reports, but first you will have to design the data source, and then they can desing the UI part.

I hope in future service packs or releases, Microsoft offers a direct integration for reporting in SharePoint as so many solutions are based on saving data in lists and libraries, but when it comes to reporting SharePoint offers a limited support for reports.!

Please refer to the following links to get more information:

• Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007
• In a nutshell: Reporting Services Addin for SharePoint 2007
• InfoPath and Reporting
• Use Reporting Services to print SharePoint lists?
• Update: Reporting over Sharepoint lists from MS Reporting Services
• Accessing SharePoint List Items with SQL Server 2005 Reporting Services
• Deliver SQL Server Reports To SharePoint To Enhance Team Collaboration
• SQL 2005 Reporting Services and Sharepoint Web Services