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

Comments

  • Anonymous
    December 13, 2007
    PingBack from http://stevepietrekweblog.wordpress.com/2007/12/13/links-12132007/

  • Anonymous
    March 25, 2009
    Has anyone figured out how to specify columns that have a space in the column name?  I've tried using underscore instead of space, wrapping the field in single quotes, wrapping the field in double quotes, wrapping the field in square brackets...all to no avail.  

  • Anonymous
    May 29, 2009
    cneiger, have you tried using x0020 in place of the space?

  • Anonymous
    August 17, 2009
    Hi I am Querying over Infopath forms Library, I am not able to get any data for the columns present in Form. Iam able to get the data when the column is in List. Please suggest how to do for SSRS Sharepoint over Infopath Forms.. Thanks in advance

  • Anonymous
    April 22, 2010
    Brilliant! you have resolved an issue i was facing. I found the easiest way obtain the column names was to use fiddler when querying the web service, i then extracted the internal names and formatted using the example above. If any one knows of a simpler way please let me know!

  • Anonymous
    June 30, 2010
    Thanks a lot. Spent  3 days figuring this out.

  • Anonymous
    November 08, 2010
    When i try to mention @columnname in element path, i couldnot able to see any data,instead i see empty row. I could able to see all columns but no rows. all row values are empty

  • Anonymous
    March 14, 2011
    @mahender , same with me, did u get solution for this ?PLease help me on this

  • Anonymous
    April 04, 2011
    @ Mahender & @Dipali Add "ows_" in fornt of the field name...to get result

  • Anonymous
    August 31, 2011
    I have added ows_ infront of the field name but no records

  • Anonymous
    May 08, 2014
    Hi, I know that this is an old post. I've tried the Maria solution, but the empty fields aren't returned from Lists.asmx. I'm using Sharepoint 2010 with SQLServer 2012

  • Anonymous
    September 04, 2014
    Worked for me Maria. Thanks! The column name has to be like @ows_FirstPartOfName + x0020 for space + SecondPartOfName and it works.