none
Possible to use a Sharepoint List as a Datasource? RRS feed

  • Question

  • 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?
    Tuesday, June 13, 2006 4:29 PM

Answers

  • 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


    Wednesday, June 14, 2006 4:04 AM
    Answerer
  • 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



    Friday, July 14, 2006 6:56 PM
    Answerer
  • 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
    Friday, January 19, 2007 10:42 PM
    Answerer

  • 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
    Monday, January 22, 2007 11:06 PM
    Answerer
  • 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
    Thursday, January 25, 2007 9:34 PM
    Answerer

All replies

  • 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


    Wednesday, June 14, 2006 4:04 AM
    Answerer
  • 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

    Wednesday, July 5, 2006 4:22 AM
  • 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

    Wednesday, July 5, 2006 2:18 PM
  • 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



    Friday, July 14, 2006 6:56 PM
    Answerer
  • 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.

    Frédéric LATOUR

    http://www.enesyssoftware.com

     

    Monday, July 17, 2006 8:20 PM
  • 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

    Friday, January 19, 2007 1:32 PM
  • 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
    Friday, January 19, 2007 10:42 PM
    Answerer
  • 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.

    Monday, January 22, 2007 9:39 AM

  • 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
    Monday, January 22, 2007 11:06 PM
    Answerer
  • 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!!!

    Thursday, January 25, 2007 2:04 PM
  • 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
    Thursday, January 25, 2007 9:34 PM
    Answerer
  • 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>

    Tuesday, January 30, 2007 9:12 AM

  •  I am geting the same error message


    An error occurred while executing query
    Error while reading xml response.

    Error while reading xml response.(Microsoft.ReportingServices.DataExtensions)

    My Data query

    <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>Red Flags</DefaultValue>
        </Parameter>
    </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="True">GetListResponse{}/GetListResult{}/List/Fields/Field</ElementPath>
    </Query>

     Any help ?
    simam
    Thursday, May 21, 2009 8:14 PM
  • Any one knows how to know the viewName of the sharepoint list.

    Thank you.
    simam
    Wednesday, May 27, 2009 2:31 PM
  • Hi,

    I can access the list in SharePoint but then a message pops up displaying the following and it does not disappear even after clicking the OK button.

    An error occurred while reading data from the query result set.
    Unable to read data from the transport connection: An existing connection was forcibly closed by the remote host. (Microsoft Report Designer)

    Any ideas what this problem is?
    Wednesday, December 23, 2009 3:49 PM
  • It is working now and this is the query I used.

    <Query>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
    <Parameters>
        <Parameter Name="listName">
    <DefaultValue>{4C98DDEC-D0B2-4528-AA54-2C7074E38A40}</DefaultValue>
        </Parameter>
        <Parameter Name="viewName">
    <DefaultValue>{B4CEA9AB-A81B-4333-B397-76C62890E568}</DefaultValue>
        </Parameter>
    </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="true">GetListItemsResponse/GetListItemsResult/listitems/data/row
             {@ows_Title,@ows_Modified,@ows_PercentComplete,@ows_Status_x0020_No}</ElementPath>
    </Query>
    Tuesday, December 29, 2009 11:36 AM