Querying SharePoint List from Reporting Services returns only “not null” columns

  • Article

 

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.

This behavior is due to the Auto derivation the XMLDP: When there is an empty value for the 1st row, the column is not displayed.

 

This is fixed for the next version of Reporting Services (Katmai). For the moment you can use the following workaround, specifying all the columns explicitly (@Column1,@Column2,@Column3,@Column4,@column5,@column6,@column7):

<Query>

    <Method Namespace="https://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">

        <Parameters>

            <Parameter Name="listName">

                <DefaultValue>SomeListName</DefaultValue>

            </Parameter>

            <Parameter Name="viewName">

                <DefaultValue>{d99b0402-38b9-48a5-87e9-2c6bf198a30c}</DefaultValue>

            </Parameter>

        </Parameters>

    </Method>

    <ElementPath IgnoreNamespaces="true">GetListItemsResponse/GetListItemsResult/listitems/data/row{@Column1,@Column2,@Column3,@Column4,@column5,@column6,@column7}</ElementPath>

    <SoapAction>https://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>

</Query>

 

 

Maria Esteban

Ingeniero de Soporte de Reporting Services