How can you get results from a SharePoint document library (with folder and child items) in a SSRS report?

Question

I have a SSRS report that uses a SharePoint document library as a data source. The issue is that the list has folder and in those folders are child items.

The dataset is only returning items from the root folder, everything in the sub-folders is effectively invisible...

Ideally I would simply add a scope="recursive" somewhere and everything would just work... Or specify a view from the list to use for the query.

Any ideas?

Answer 1

1. use data source type xml, connection string: http://SiteName/_vti_bin/Lists.asmx

2. the query is:

    <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>Library Name</DefaultValue> 
            </Parameter> 
            <Parameter Name="viewName"> 
               <DefaultValue>{E0C51281-6D2E-4F24-A600-BCC7D6D05166}</DefaultValue> 
            </Parameter> 
            <Parameter Name="rowLimit"> 
               <DefaultValue>30000</DefaultValue> 
            </Parameter> 
            <Parameter Name="queryOptions" Type="xml"> 
               <DefaultValue>
                  <QueryOptions>
                     <ViewAttributes Scope="Recursive"/>
                  </QueryOptions>
               </DefaultValue> 
            </Parameter> 
         </Parameters> 
       </Method> 
       <ElementPath IgnoreNamespaces="True">*</ElementPath>
   </Query>
   

view Name get from Library --> change View --> address --> queryString 'view'

comment: if the first row missing data in any field, the field not selected for all rows. so, replace code <ElementPath IgnoreNamespaces="True">*</ElementPath> and write all columns you whant to select:

  <ElementPath IgnoreNamespaces="True">
      GetListitemsResponse/GetListItemsResult/listItems/data/row{@ows_ID,@ows_columnnName1,@ows_columnnName2}
  </ElementPath>'