Reporting Services and WSS Lists

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 (https://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 (https://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 (https://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 https://sharepoint/sites/mutaz

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

 

 

the connection string is two parts:

1. the site URL: https://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 (https://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

 

 

the Data Query XML will be:

<Query>
<SoapAction>https://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
<Method Namespace="https://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.

 

 

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

Comments

• Anonymous
  November 19, 2008
  Thank you for posting this, Mutaz. Your example was quite helpful. My only comment is that this sample works fine for testing from a local copy of the IDE if you either allow Anonymous authentication to your SharePoint instance or you configure kerberos authentication and use that in the connection. I've found that for SharePoint web applications configured for NTLM authentication, SSRS fails authentication.

• Anonymous
  November 19, 2008
  I tested this technique with NTLM authentication, and it works. i never had the opportunity to test it with other authentication methods/anonymous authN. thanks for the feedback,

• mutaz

• Anonymous
  November 21, 2008
  Thanks for the response, Mutaz. In reference to using NTLM, you are absolutely right. I discovered that the issue was attempting to use Windows Integrated Authentication from a computer joined to an untrusted domain. Once the same test was performed from a computer joined to the same AD forest (or a explicitly trusted domain), this worked fine.

• Anonymous
  November 21, 2008
  Another question/comment: I agree with your suggestion to use a custom RS data extensions for more complex operations. I'm currently researching options for performing SharePoint list table joins through the SharePoint integration services layer. I'm wondering what you think of another alternative and that is to call the Query or QueryEx methods of SharePoint's Search.asmx web service to perform list joins. The Enterprise Search SQL Syntax in the MOSS 2007 SDK is far from complete. I'm anxious to get your feedback.

• Anonymous
  November 23, 2008
  in a recent project, we implemented a join between SQL table and sharepoint list by constructing a datatable that will join results from both. trying to do the same with sharepoint lists proven to be more complex than what we wanted, and not as useful (though one can argue many useful scenarios for sharepoitn list joins). we did not use the search service. we use the list service for performing queries against shrepoint and lots of CAML. hth

• mutaz

• Anonymous
  November 27, 2008
  Thanks very much your post is simple and Well explained

• Anonymous
  November 27, 2008
  Thanks dario.

• Anonymous
  December 26, 2008
  Dear Mutaz, Your post is very useful for me. Now I got a requirement that, I need to create report from two or more sharepoint list. I searched a lot but not find anything about it. Can u just tell me how to join two sharepoint list in SSRS and create report with respective to above example given by you. Waiting for you reply. Regards.

• Anonymous
  February 28, 2009
  Hi asem, getting SSRS to read from two lists and join the results in one data set will require custom development. if possible, we can work through your local MCS contacts to deliver a solution for this requirement. regards,

• mutaz

• Anonymous
  April 17, 2009
  The comment has been removed
• Anonymous
  May 17, 2009
  I agree, the XML data source is very limited when it comes to querying WSS lists in SSRS. and the option of developing own data extension requires a very thorough technical specs design. which could become costy. on this one i would suggest the option to by the ready made extension. cheers,

• mutaz

• Anonymous
  March 09, 2010
  is this approach for 2005, 2008 or both? Thanks

• Anonymous
  March 09, 2010
  Dean, this approach works with both SSRS 2005 and SSRS 2008. it was tested against WSS 3.0 and MOSS 2007.

• Anonymous
  April 22, 2010
  In my scenario it seems like the results are being limited to 100 records.  Is there a setting somewhere that I need to change to pull back all the rows?

• Anonymous
  May 31, 2010
  Hi, Thanks for this post, but for my site i've a 404 when i try to display the information in the query builder... Someone has any idea? I'm on SSRS2008 and WSS3.0... Many thanks, Chris

• Anonymous
  May 31, 2010
  Thanks for this post, but for me (SSRS2008 and WSS3.0) i've a error 404 when i try to display data in query builder... Someone had an idea? Cheers