I want to build a report:
with Excel 2013 + PowerPivot Add-In
using data from SharePoint Online
that should work in Excel Services (so I'm not able to use VB, Power Query, etc.)
Report should be build against list items stored on multiple sub sites of a site collection.
For example, here is a root site:
that have the following sub sites:
http://<root>/siteA http://<root>/siteB ... http://<root>/siteZ
each of these sites has a list CustomItems. I need to retrieve all the items from CustomItems lists in Excel.
It looks that items can be retrieved with the OData query like:
but if I add this query as Data Source, Excel extracts only Webs items, but doesn't extract Lists or Items expanded in the request.
Is there a way to retrieve these items in Excel?