SQL Server 2008 R2 Reporting Services, SharePoint List Data Source

Increasing numbers of companies use SharePoint lists to store information that needs to be shared with a broader audience or in a standard report format. Although there are some creative ways you could employ to get that data into Reporting Services, custom code was always part of the solution.

SQL Server 2008 R2 Reporting Services has a new data extension provider that allows you to access SharePoint 2007 or SharePoint 2010 lists. After you create the data source using the Microsoft SharePoint List connection type and provide credentials for authentication, you must supply a connection string to the site or sub-site in the form of a URL that references the site or sub-site. That is, use a connection string such as http://MySharePointWeb/MySharePointSite or http://MySharePointWeb/MySharePointSite/Subsite. A query designer is available with this connection provider, allowing you to select fields from the list to include in your report.