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.

Advertisements

14 thoughts on “SQL 2005 Reporting Services and Sharepoint Web Services

  1. indeed MSDN documenation oversmiplifies the usage of XML datasource. ur post here bridged the gap when it comes to querying WSS lists.
     
    well done!
    – mutaz
     

  2. Thanks, Mutaz.  If you fine tune anything above or discover any more tricks, please feel free to add another comment here!

  3. No … I have not yet tried it with WSS.  I would hate to see that web services would have changed dramatically.  Did you get any more details on the error other than: "failed" ?

  4. For those of you trying to do this under SRSS 2005 and WSS3/MOSS2007.. You can simply use the XML datasource option and use the RSS feed that’s under the "Actions -> View RSS Feed"Take that URL, and use that as your connection string.  Leave your query blank (Don’t use the report wizard.)  This will get you a basic subset of information you can use in reports.  Just make sure to bind those fields into the dataset to use in your reports.

  5. Whoops.. My mistake!Add the following as your query.<Query></Query>This will return everything the RSS feed reports on (all columns in the list, i believe.)  and bind each of those columns to a field value you can use in your report within the dataset.Enjoy!Thock

  6. Hey Jason, this was VERY helpful.
     
    It wouldn’t work for me till I removed all the extra spaces and line returns:
    <Query><Method Namespace="http://schemas.microsoft.com/sharepoint/soap/&quot; Name="GetListItems" /><SoapAction>  http://schemas.microsoft.com/sharepoint/soap/GetListItems </SoapAction></Query>
     
    I am still trying to figure out how to map the columns returned to Fields in the data source properties. If you have any tips on that it woudl be very helpful!

  7. Ryan … not sure, did you try The Kid’s suggestion from the forum?  Also, did you double check that you are using the correct URL?  Try removing the Order By and see if that changes things as well.

  8. Just as some added info–I was having trouble trying to get this to work and finally I got it so here are some lessons learned:
    1.  For the list name I used the GUI, it seems that on custom lists you have to use the GUI as the list name or it doesn’t work properly. 
    2. This only pulls data from the view set as the default, so if you want everything in the list to show you need to make sure that view is the default. 
    3.  The RSS solution works, but in the URL you have to replace feed: with http:, and it only pulls what is set in the RSS settings of the list– so if the RSS settings say to only pull the last 5 things in the last 4 days— that is all the report will pull– you have to either reset the settings, or use the other method in this article.

  9. Hi Jason, I have a question regarding your posting if you’re looking for a small challenge. When creating a dataset in VS2005 for a SSRS report, I get my SharePoint custom list data returned to me but I do not get any fields mapped in the dataset navigation pane as a result of that query/dataset. As a result I have nothing to select from to make my report. Is there some other mechanism for translating the XML information returned by the query into fields that can be used in a report? 
     
    Note: For anyone else also suffering with SSRS and reading SharePoint lists (and integration mode), I’ve listed  couple of my issues and how they were "fixed" at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2695430&SiteID=1&mode=1.
     
    Thanks
    -RaDiO

  10. 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.Regards.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s