Recently I had to connect to a SharePoint List to access some data. While there is a data extension available from Enesys (including a free Community edition) to do this I though I would first make an attempt using the out-of-the-box options.
The first step was getting connected to any of the list services. I chose to start with GetListCollection because it required no parameters (aka it was simple to use). Here is what I did to get connected:
- Right click the Shared Data Sources folder in your report project and select Add New Data Source.
- In the Shared Data Source dialog give your data source a name and from the Type drop down list select XML.
- In the Connection string text field put the URL to the Lists service (e.g. http://<sharepointserver>/<subarea_optional>/_vti_bin/lists.asmx)
- On the Credentials tab select Use Windows Authentication (Integrated Security) and click OK.
- Create a new or open an existing report definition and select the Data tab.
- From the Dataset drop down list select New Dataset.
- Provide a Name for your data set and select the Data source from the drop down list that you created earlier.
- In the Query text field put in the following query (note that there is no trailing slash on the Namespace attribute -- this is important!):
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap" Name="GetListCollection" />
</Query>
- Click OK and then click the Run in the Data tab.
You should see in the preview window at this point a dataset result from the GetListCollection call. In addition the Datasets tool window will show all the fields returned in the dataset for you to leverage in your report.
Now the GetListCollection is useful, but it's not the method call that we all really want - GetListItems. To do this we'll leverage the same shared data source:
- Create a new or open an existing report definition and select the Data tab.
- From the Dataset drop down list select New Dataset.
- Provide a Name for your data set and select the Data source from the drop down list that you created earlier.
- In the Query text field put in the following query (note that there is no trailing slash on the Namespace attribute -- this is important!):
<Query>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems"/>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
</Query>
- In the Parameters tabs add the following entries while leaving the Value blank:
- listName
- viewName
- query
- viewFields
- rowLimit
- queryOptions
- Click OK and then click the Run in the Data tab.
- In the Define Query Parameters dialog enter the name of your list in listName and click OK.
I have found various oddities while working with the XML data source like random failures for no apparent reason. I'm still playing with it but I thought I would share my learnings for others. You can, of course, refine your request with the various parameters outlined in the SDK.
As you work with Web Services and Reporting Services I highly recommend Fiddler as a troubleshooting tool. The trailing slash note above was discovered because I could see what was going on at the HTTP layer (the SoapAction header had "...soap//GetListCollection").