SQL 2005 Reporting Services and Sharepoint Web Services

Introduction
One nice addition to SQL 2005 Reporting Services is the ability to have a XML Data Source (e.g. Web Service). However, getting it to work seems to be a little trickier than need be, especially for SharePoint’s web service.

The Problem
Following the example in the SQL Books Online makes it look like you can apply it to just any web service but you’ll end up fighting with the parameters if you do as they simply don’t get properly serialized. After researching the issue and combining several different answers it comes down to the XmlDP Query syntax. The syntax which works looks like this (using GetList as an example):

<Query>
   <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/&quot;
                    Name="GetListItems" />

   <SoapAction>
      http://schemas.microsoft.com/sharepoint/soap/GetListItems
   </SoapAction>
</Query>

The difference from the books online is the <Method> node which seems trigger something in RS to handle the issue. One other note is that even with the Method node, SSRS is still picky about the parameters so I have pretty much gotten by using only the essential parameters. I haven’t really built upon this example too much further but feel free to leave a comment if you have.

The Recipe
The following instructions assume an existing report but they should still work just fine using the add report Wizard.

  1. Add a new Data Source
  2. Set the Command Type to Text
  3. Set the Query string using the syntax above
    Example:
    <Query>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/&quot;
    Name="GetListItems" />

    <SoapAction>
    http://schemas.microsoft.com/sharepoint/soap/GetListItems
    </SoapAction>
    </Query>
  4. Set the parameters in the Parameters tab
    • Use the param name from the WSDL
      Example: listName
    • Use a list name for the value
      Example: Events
  5. Close the dialogs and run the report

You should now have at least some data coming back. If you have any specific problems and/or fixes please feel free to post a comment.