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>


 


 


Maria Esteban


Ingeniero de Soporte de Reporting Services

Comments (12)

  1. cneiger says:

    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.  

  2. MichealHunt says:

    cneiger,

    have you tried using _x0020_ in place of the space?

  3. rakesh.micro says:

    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

  4. tomas_andrews@hotmail.com says:

    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!

  5. Bhuvan says:

    Thanks a lot. Spent  3 days figuring this out.

  6. Mahender says:

    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

  7. dipali says:

    @mahender , same with me, did u get solution for this ?PLease help me on this

  8. Chandru says:

    @ Mahender & @Dipali

    Add "ows_" in fornt of the field name…to get result

  9. Sonali says:

    I have added ows_ infront of the field name but no records

  10. Alessio Elia says:

    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

  11. RV says:

    Worked for me Maria. Thanks!

    The column name has to be like @ows_FirstPartOfName + _x0020_ for space + SecondPartOfName and it works.

Skip to main content