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):
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/"
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.
- Add a new Data Source
- Set the Type to XML
- Set the Connection String to the URL of the web service:
Example: https://localhost/_vti_bin/Lists.asmx - Set the Command Type to Text
- Set the Query string using the syntax above
Example:
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/"
Name="GetListItems" />
<SoapAction>
http://schemas.microsoft.com/sharepoint/soap/GetListItems
</SoapAction>
</Query> - 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 - 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.
Jason Agostoni 
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
Thanks, Mutaz. If you fine tune anything above or discover any more tricks, please feel free to add another comment here!
Have you tried this with WSS 3.0 yet? I keep getting failed to execute web request for the URL
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" ?
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.
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
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/" 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!
Jason,
I’m having an issue with creating a report using a SharePoint list as a datasource in MSQL 2005. Below is a link to a post I made on th msdn forums but I haven’t gotten any feedback. Several google searches brought be to your site several times, do you have any suggestions?
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2294034&SiteID=17
Thanks in advance,
Ryan
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.
I tried using the RSS feed as my connection string, but the Preview only returns a single row.any idea why?
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.
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
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.
Very useful