Maria's two cents

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

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="http://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>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>

</Query>

Ingeniero de Soporte de Reporting Services

Published Thursday, December 13, 2007 5:47 PM by mariae