Possible to use a Sharepoint List as a Datasource?

Tvercel

In Reporting Services 2005, is it possible to create a data connection to a sharepoint list If so, what connection type and connection string do i use for this

Ian Roof - MSFT

It is possible using the Xml Data processing extension. Sharepoint exposes a Lists.asmx webservice, which can be queried using the Xml data processing extension.

Connection String:
http://<YourServerName>/sites/sitename/_vti_bin/Lists.asmx

Xml Query:

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetList">
</Method>
<ElementPath IgnoreNamspaces="true">GetListResponse{}/GetListResult{}/List/Fields/Field</ElementPath>
</Query>

Dataset Parameter:
Name: listName, Value: The name of the list you want from the site.

Notes:
You may need to play with the ElementPath to get what you need. See this page for more information: http://msdn2.microsoft.com/en-us/library/ms365158.aspx

HurlyBurly

This is good as I have this exact requirement, but get

TITLE: Microsoft Report Designer
------------------------------

An error occurred while executing the query.
Failed to execute web request for the specified URL.

------------------------------
ADDITIONAL INFORMATION:

Failed to execute web request for the specified URL. (Microsoft.ReportingServices.DataExtensions)

------------------------------

< xml version="1.0" encoding="utf-8" >
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<soap:Body>
<soap:Fault>
<faultcode>soap:Server</faultcode>
<faultstring>Exception of type Microsoft.SharePoint.SoapServer.SoapServerException was thrown.</faultstring>
<detail>
<errorstring xmlns="http://schemas.microsoft.com/sharepoint/soap/">Value cannot be null.</errorstring>
</detail>
</soap:Fault>
</soap:Body>
</soap:Envelope>

------------------------------
BUTTONS:

OK
------------------------------

Any ideas

Markus Fischer

I would suggest that you use the informations provided by Teun Duynstee in this link:

http://www.teuntostring.net/blog/2005/09/reporting-over-sharepoint-lists-with.html

Cheers
Markus

Ian Roof - MSFT

The error that you are getting for the GetList method is most likely caused by not using the correct name of the list. The listName must be either the title or the GUID for the list.

For more info on this method http://msdn.microsoft.com/library/default.asp url=/library/en-us/spptsdk/html/soapmListsGetList_SV01034346.asp

Frederic Enesys

Hi,

In case you are interested we are selling a reporting services (both 2000 and 2005 version) data extension for sharepoint.

This extension makes it possible to build report using sharepoint lists (including libraries).

Several lists may be joined using SQL-like operators.

Reports parameters may be used with the query string.

An evaluation version is available on our site at http://www.enesyssoftware.com/Default.aspx tabid=56

If you prefer to do it by yourself, the article from Teun Duynstee is the way to go.

Frederic LATOUR

http://www.enesyssoftware.com

Sumit Kute

Hi,

I have the same requirement of getting the data from List in SharePoint 2007. It exposed the method GetList(). I am using the same code which you have mentioned. But is not working.

Reporting Service SP 2 provide XML DataSource

Can you please rectify where i am going wrong. I have a List by name say: Announcements

How to Specify List Name and Where I am unable to undertand your Dataset Parameter.

Dataset Parameter:
Name: listName, Value: The name of the list you want from the site.

I tried few combinations.

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap" Name="GetList">
</Method>
<ElementPath IgnoreNamespaces="True">GetListResponse{}/GetListResult{}/List/Fields/Field</ElementPath>
</Query>

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap" Name="GetList">
</Method>
<ElementPath IgnoreNamespaces="True">GetListResponse{}/GetListResult{}/Announcements</ElementPath>
</Query>

Please help. As this will save me from writing DATA Extensions for my reports integration with sharepoint 2007

Ian Roof - MSFT

There are two ways to specify parameters for an Xml Data Processing Extension query.

1. Use the Reporting Services Dataset query parameters collection.

Add a query parameter to the dataset with the name 'listName' and value 'Announcements'.

2. Add the parameters directly to the Xmlk query, using the Parameters Xml element, which is a child of the Method element.

Add the following Xml as the child element to the Method element in the query above.

<Parameters>
<Parameter Name="listName">
<DefaultValue>Announcements</DefaultValue>
</Parameter>
</Parameters>

Ian

Sumit Kute

Hi,

I tried as you said now my query is. Previoisly i was not in sharepoint integration mode. so i was giving another error. Now i am getting the error as "Error While reading XML reponse"

My Data Source is:
http://localhost/Docs/vti_bin/Lists.asmx

My Query is :
<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap" Name="GetList">
<Parameters>
<Parameter Name="listName" Type="String">
<DefaultValue>Announcements</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath>GetListResponse{}/GetListResult{}/List/Fields/Field</ElementPath>
</Query>

My Web Service is :

< xml version="1.0" encoding="utf-8" >
<soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope">
<soap12:Body>
<GetListResponse xmlns="http://schemas.microsoft.com/sharepoint/soap/">
<GetListResult>
<xsd:schema>schema</xsd:schema>xml</GetListResult>
</GetListResponse>
</soap12:Body>
</soap12:Envelope>

I have tried all combinations. 1) Isn't there any tool where i can construct this query 2) I am not able to view the dataset result in XML, so that i can map it with <ElementPath>. I am not able to test webservice with this "http://localhost/Docs/_vti_bin/Lists.asmx op=GetList" URL, to view the dataset result.

Ian Roof - MSFT

The exception you see being thrown is usually a wrapped exception that occurs in the call in the request/response phase, most likely a permissions issue. Check the log files for more information about this exception.

Also, regarding the ElementPath:

Try setting the IgnoreNamespaces attribute to true on the ElementPath element:

<ElementPath IgnoreNamespaces="true">

Answers to your questions:

1. Unfortunately, no, there is no tool at this time.
2. After you set the attribute mentioned above, try making the ElementPath less restrictive and to return the Xml as is. For example, this will return one field containing the raw Xml of the GetListResult element. You can use this to information see what the elements are in the GetListResult.

<ElementPath IgnoreNamespaces="true">GetListResponse{GetListResult(XML)}</ElementPath>

Ian

Sumit Kute

Hi,

This time i tried all below combionations bit its still not working. I have wasted lot of effots on this and this is very important for me to get it solved.

ERROR its Gives in Log is
< xml version="1.0" encoding="utf-8" ><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><soap:Fault><faultcode>soap:Server</faultcode><faultstring>Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown.</faultstring><detail><errorstring xmlns="http://schemas.microsoft.com/sharepoint/soap/">Value cannot be null.</errorstring></detail></soap:Fault></soap:Body></soap:Envelope>

http://localhost/_vti_bin/Lists.asmx

GUID
<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Name="GetList" Namespace= "http://schemas.microsoft.com/sharepoint/soap">
<Parameters>
<Parameter Name="listName">
<DefaultValue>21CF03AF-3A7E-479C-98E0-CBE0F16A594A</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetListResponse{GetListResult(XML)}</ElementPath>
</Query>

WithOut GUID
<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Name="GetList" Namespace= "http://schemas.microsoft.com/sharepoint/soap">
<Parameters>
<Parameter Name="listName">
<DefaultValue>Contacts</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="true">GetListResponse{GetListResult(XML)}</ElementPath>
</Query>

GUID
<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap" Name="GetList">
<Parameters>
<Parameter Name="listName">
<DefaultValue>21CF03AF-3A7E-479C-98E0-CBE0F16A594A</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="True">GetListResponse{}/GetListResult{}/List/Fields/Field</ElementPath>
</Query>

WithOut GUID
<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap" Name="GetList">
<Parameters>
<Parameter Name="listName">
<DefaultValue>Contacts</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="True">GetListResponse{}/GetListResult{}/List/Fields/Field</ElementPath>
</Query>

Please Help!!!

Ian Roof - MSFT

Ok, I tracked down the culprit. The Method element in your tests and the example I provided above differ very slightly. The namespace for the web service ends with a '/', which was mssing from in your query. This caused the method portion of the soap request sent to the server to exist in a different namespace than was expected, which caused it to be interpreted as null.

To reslove this issue, append a '/' to end of the Namespace attribute value in the Method element.

Ian

Sumit Kute

Thanks a lot it worked.

Working Query is

<Query>
<SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetList</SoapAction>
<Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetList">
<Parameters>
<Parameter Name="listName">
<DefaultValue>Announcements</DefaultValue>
</Parameter>
</Parameters>
</Method>
<ElementPath IgnoreNamespaces="True">GetListResponse{}/GetListResult{}/List/Fields/Field</ElementPath>
</Query>

Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?

Re: Possible to use a Sharepoint List as a Datasource?