They seem to be a perfect couple. List data that is accessible to users to add/edit delete, and Reporting Services offering custom reports with subscriptions to deliver them right where you need them. But this is not easy to accomplish OOB. There are several approaches to using Reporting Services with SharePoint data.
- Enesys RS Data Extension: This is an extension to reporting services that allows you to use list data as a data source. You can also query document libraries, permission sets, and SharePoint groups as data sources. In addition, you can query multiple lists and and perform a SQL JOIN. Very cool stuff.
- Write your own Data Extension: This is a great article by Teun Duynstee. This is the bare metal version of a Reporting Services Data Extension. If you find that your environment won’t support the Enesys Data Extension, or you just like to write your own, this shows you how you can integrate SharePoint Lists into reporting services.
- Consume the Lists.asmx web service as an XML Data Source: This approach doesn’t require any custom code on the server and it ready to go once Reporting Services is installed. Here’s another article that discusses some of the roadblocks with this approach.
- Build your own Views based on the SharePoint DB: As you should know by now, direct access to the SharePoint DB is unsupported. If you decide to go this route, I would suggest placing these views in a reporting DB outside of SharePoint. With this approach, there is no custom code on your server, (but you will have DB customization).
Of course, your choice will be limited based on
- Expertise (development, DB, reporting …)
- Technical requirements
- Organization policy
Choice number 3 is ready to go on Day 1, but it can be unwieldy to work with. Direct DB access seems to fit best with Reporting Services database focus, but adds technical risk. The first two choices require custom code which may not be supported in all environments.