none
querying Sharepoint List with Report Builder 2.0, not all fields show up

    Question

  • We're using SQL Server 2008 and Report Builder 2.0 to query a Sharepoint List for reporting purposes.  I've followed this tutorial for querying Sharepoint Lists using an XML data source in Report Builder 2.0.  My XML query looks like so:

    <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>{8529D70B-D632-4CC8-A1E7-2C25F29BE1E0}</DefaultValue>
             </Parameter>
             <Parameter Name="viewName">
                <DefaultValue>{2FC6AA42-EA95-4C18-AB07-33E25EBBA85D}</DefaultValue>
                <ViewFields>
                   <FieldRef Name="Resolve_x0020__x0023_" />
                   <FieldRef Name="Product" />
                   <FieldRef Name="Release" />
                   <FieldRef Name="Theme" />
                   <FieldRef Name="Pre_x002d_Req_x0020_Estimate" />
                </ViewFields>
             </Parameter>
          </Parameters>
       </Method>
       <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>
    The specified view GUID refers to a view that has all those fields selected.  I also try to explicitly list the fields I want in the XML above.  However, I seem to either get Release OR Theme, but not both at the same time.  Why does this query not return all the fields in the given list and view?
    Monday, February 01, 2010 7:47 PM

Answers

  • Hi cheshire137,

    Querying to a SharePoint List from a report in Reporting Services 2005 returns only the columns that contain data. If any of the columns is NULL, they are not returned.

    Maria has offered a solution to this.  Thanks Maria!


    We can use the following workaround to solve the issue, specifying all the columns explicitly (@Column1,@Column2,@Column3,@Column4,@column5,@column6,@column7):
     

    <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>{8529D70B-D632-4CC8-A1E7-2C25F29BE1E0}</DefaultValue>
             </Parameter>
             <Parameter Name="viewName">
                <DefaultValue>{2FC6AA42-EA95-4C18-AB07-33E25EBBA85D}</DefaultValue>
             </Parameter>
            </Parameters>
        </Method>
        <ElementPath IgnoreNamespaces="true">GetListItemsResponse/GetListItemsResult/listitems/data/row{@Column1,@Column2,@Column3,@Column4,@column5,@column6,@column7}</ElementPath>
    
    </Query>


     

    By the way, we are not able to pass XML parameter directly to the query statement. We need to define a parameter as XML type, and then pass an XML parameter's value to the parameter in query, please follow these steps to do so:

    1.Change the query string to be:

    <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>{8529D70B-D632-4CC8-A1E7-2C25F29BE1E0}</DefaultValue>
             </Parameter>
             <Parameter Name="viewName">
                <DefaultValue>{2FC6AA42-EA95-4C18-AB07-33E25EBBA85D}</DefaultValue>
             </Parameter>
             <Parameter Name="viewFields" Type="xml">          
             </Parameter>
           </Parameters>
       </Method>
      </Query>


    2.Click the ellipsis button(...) beside DataSet dropdownload list. That is used to open the property dialog of the current dataset.
    3.In the DateSet properties dialog, go to "Parameters" tab.
    4.In parameter tab, create a parameter with:
    Name: viewFields
    Value:    <ViewFields><FieldRef Name="Resolve_x0020__x0023_" /><FieldRef Name="Product" /><FieldRef Name="Release" /><FieldRef Name="Theme" /><FieldRef Name="Pre_x002d_Req_x0020_Estimate" /></ViewFields>


    Here are some similar threads for  your reference:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/26c2dba4-51c5-4d59-a512-2af7883ec94a
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c131321c-8518-4c14-a941-7bccb78f591e


    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by cheshire137 Friday, February 05, 2010 4:58 PM
    Wednesday, February 03, 2010 3:16 AM

All replies

  • Hi cheshire137,

    Querying to a SharePoint List from a report in Reporting Services 2005 returns only the columns that contain data. If any of the columns is NULL, they are not returned.

    Maria has offered a solution to this.  Thanks Maria!


    We can use the following workaround to solve the issue, specifying all the columns explicitly (@Column1,@Column2,@Column3,@Column4,@column5,@column6,@column7):
     

    <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>{8529D70B-D632-4CC8-A1E7-2C25F29BE1E0}</DefaultValue>
             </Parameter>
             <Parameter Name="viewName">
                <DefaultValue>{2FC6AA42-EA95-4C18-AB07-33E25EBBA85D}</DefaultValue>
             </Parameter>
            </Parameters>
        </Method>
        <ElementPath IgnoreNamespaces="true">GetListItemsResponse/GetListItemsResult/listitems/data/row{@Column1,@Column2,@Column3,@Column4,@column5,@column6,@column7}</ElementPath>
    
    </Query>


     

    By the way, we are not able to pass XML parameter directly to the query statement. We need to define a parameter as XML type, and then pass an XML parameter's value to the parameter in query, please follow these steps to do so:

    1.Change the query string to be:

    <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>{8529D70B-D632-4CC8-A1E7-2C25F29BE1E0}</DefaultValue>
             </Parameter>
             <Parameter Name="viewName">
                <DefaultValue>{2FC6AA42-EA95-4C18-AB07-33E25EBBA85D}</DefaultValue>
             </Parameter>
             <Parameter Name="viewFields" Type="xml">          
             </Parameter>
           </Parameters>
       </Method>
      </Query>


    2.Click the ellipsis button(...) beside DataSet dropdownload list. That is used to open the property dialog of the current dataset.
    3.In the DateSet properties dialog, go to "Parameters" tab.
    4.In parameter tab, create a parameter with:
    Name: viewFields
    Value:    <ViewFields><FieldRef Name="Resolve_x0020__x0023_" /><FieldRef Name="Product" /><FieldRef Name="Release" /><FieldRef Name="Theme" /><FieldRef Name="Pre_x002d_Req_x0020_Estimate" /></ViewFields>


    Here are some similar threads for  your reference:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/26c2dba4-51c5-4d59-a512-2af7883ec94a
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/c131321c-8518-4c14-a941-7bccb78f591e


    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    • Marked as answer by cheshire137 Friday, February 05, 2010 4:58 PM
    Wednesday, February 03, 2010 3:16 AM
  • Thanks for your response, Jin.  Your second suggestion, creating a separate parameter to hold the view fields XML and adding the parameter to my Query, did not work for me--I still didn't get all the fields in the view/all the fields listed in the parameter.  However, your first suggestion, explicitly listing the fields I want in the ElementPath tag, worked wonderfully:

    <ElementPath IgnoreNamespaces="true">GetListItemsResponse/GetListItemsResult/listitems/data/row{@ows_Release,@ows_Theme,@ows_ID,@ows_Pre_Req_Estimate}</ElementPath>
    I am able to run the query inside Report Builder's Query Designer and see the results I expect.  :)  I'm currently getting a "Failed to execute web request for the specified URL. Soap Fault: Exception of type 'Microsoft.SharePoint.SoapServer.SoapServerException' was thrown." error in Report Manager, but I think that might be from something else, since the query runs in Report Builder.
    Friday, February 05, 2010 4:58 PM
  • Hi cheshire137,

    I am glad to hear that you have solved the issue.

    I am sorry too for my meaningless. The second suggestion is only used to clarity how to pass XML parameter to a method in XML query.

    For your new question, I would suggest you creating a new thread. That will benefit other community members who met the same issue.
    Appreciate your understanding.

    Thanks,
    Jin Chen
    Jin Chen - MSFT
    Tuesday, February 09, 2010 11:16 AM
  • Hi Jin,

    I am a newbie to Report Builder 2.0

    I have a SharePoint list with 246 items in this particular view.
    This is my query, when I run it, I only get 100 items, but I am not using any filtering.


    <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>{3C178A9A-8DB8-46F8-A370-0F36698D830A}</DefaultValue>
             </Parameter>
             <Parameter Name="viewName">
                <DefaultValue>{E06D5512-666E-4814-9ACF-3EDB0FDFBC2D}</DefaultValue>
             </Parameter>
                </Parameters>
       </Method>
      </Query>

    Will you please let me know how I can bring all 246 itmes to my report?

    Thank you very much,
    Bitajoon
    Thursday, February 18, 2010 7:02 PM
  • Hi Bitajoon,

    Please re-read my first post. I have mentioend the cause and the solution.
    That should be caused by a "Null" column. We can use ElementPath to solve it.

    Thanks,
    Jin Chen
    Jin Chen - MSFT
    Friday, February 19, 2010 10:25 AM
  • Hi Jin,

    Thank you for your reply. The problem is not the null columns. I have columns which are filled and it is not showing up. 

    Actually I found the problem with just getting 100 items from many hundered items in a list.

    In the Modify this list view in the sharepoint itself, the "Item limit" is 100 by default. and that is the reason for showing only 100 itmes in my report builder.
    The maximum number can be 2147483647, when I changed that to the maximumn, now I do get all items.

    It did not make sense to me though, since I think this item limit is about what you see in the first page of the "view". But apparantly, only the first page pushes data to the SQL report builder!
    Friday, February 19, 2010 6:26 PM