In this one, I will demonstrate how to use SQL Server Reporting Services (SSRS) to pull data from a Windows

SharePoint Services List using the web services interface. I have seen many reporting implementations whereby the developers opt to connect to the WSS content database directly and read their data using TSQL statements. this approach is not recommended for many reasons, the most obvious are that its not supported, also it poses scalability limitations in case you decided to scale out and use multiple content dbs, in such scenario you will need to modify your queries to connect to the correct content dbs. The database schema of WSS tables are not self descriptive and developers end up hard coding the mapping of WSS list columns to database table columns.

 

Using the WSS Web Service interface will encapsulate the knowledge of content dbs schema and location, the challenge with this approach is to get SSRS to learn how to connect to a WSS list.

 

To get SSRS designers to consume WSS Web Service there are a number of available options:

 

  1. Use a commercial product such as Enesys RS Data Extension (http://www.enesyssoftware.com/Products/EnesysRSDataExtension/Overview/tabid/72/Default.aspx) , this one will install a new data extension that connects and retrieves data from SharePoint list.
  2. Use the RS data extension developed by Teun Duynstee (http://www.teuntostring.net/blog/2006/03/update-reporting-over-sharepoint-lists.html), I find this extension very helpful and it is available in source code as well.
  3. Try to use the XML Web Service data source in SSRS Designer. this approach is explained on the MSDN web site (http://msdn2.microsoft.com/en-us/library/aa964129.aspx#repservxmlds_queryws) but it does not clearly show how to use it to query WSS Web Service.

 

For this example, I am going query the "Style Library" list from the SharePoint site http://sharepoint/sites/mutaz

Start by creating a new data source, select type: XML and for the connection string use something similar to: http://sharepoint/sites/mutaz/_vti_bin/Lists.asmx, here I am using the Lists web services to retrieve the list items.

 

image

 

the connection string is two parts:

1. the site URL: http://sharepoint/sites/mutaz

2. the web service asmx file path: /_vti_bin/Lists.asmx

then in the data set query, the query string should specify the following information:

  • SoapAction (Web service method): use the GetListItems method to retrieve list items
  • Parameters, the first parameter will be the list name. for full list of supported parameters by the web service method you need to refer to the Lists methods documentation on MSDN (http://msdn2.microsoft.com/en-au/library/lists.lists.getlistitems.aspx) .. here the GetListItems method can use all the following parameters: listName, viewName, query, viewFields, rowLimit, queryOptions, webID

 

image

 

the Data Query XML will be:

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
<Parameters>
    <Parameter Name="listName">
       <DefaultValue>Style Library</DefaultValue>
    </Parameter>
</Parameters>
</Method>
</Query>

executing the above data set query will retrieve the list items.

 

image

 

things become more challenging if you try to use this method to query multiple web sites or to change the website name at run time. for that I suggest to consider using a custom data extension.

 

cheers,

- mutaz