0

My boss has been looking for a way to do this for a while, I figured I might as well ask around instead of telling him it can't be done without multiple queries to the server.

During a Project Server deployment it is common to add fields to the native Issues and Risks lists that stay inside each Project site. Project Server natively exports Risk and Issue information to its Reporting database, however, it does not take the fields that were added during the deployment.

We want to build a report using Excel Services containing this information without having to query each project site that exists on the server, using the REST API so that it can be used in SPO.

I am aware that I can use the REST query of

/PWA/ProjectSite/_api/web/lists/getbytitle('Risks')/Items 

But what I want would be the equivalent of

/PWA/ProjectSiteA/_api/web/lists/getbytitle('Risks')/Items 
UNION
/PWA/ProjectSiteB/_api/web/lists/getbytitle('Risks')/Items 
UNION
/PWA/ProjectSiteC/_api/web/lists/getbytitle('Risks')/Items 

For those not familiar with Project Server, all Project sites are subsites of the Project Server instance's site collection.

Is there a way (SPSiteDataQuery?) to query all these lists without iterating all the sites through the REST(or CSOM) api?

  • 3
    Have you considered creating a search result that gathers data from all the lists, and simply using the search REST api? – wjervis Jan 16 '15 at 13:33
  • That is a promising idea and, I confess, I have not considered it. I do worry about the 500 item limit that the REST API has, but it might not be an issue. I guess I can page it with a few more requests but it still beats the alternative. I can see that as an answer... – Choggo Jan 16 '15 at 13:37
2

Unfortunately there is not a way to query multiple lists at the same time except for using search - but this is exactly what the search REST API is intended for.

The problem with the search REST API is that is daunting to look at the results that you get. But essentially all you would have to do is hit a URL like the following end point:

_api/search/query?querytext='contenttype:risk'

You might also want to include some select criteria to reduce the size of your returned payload.

&selectproperties='Author,Path,Title,Url'

The primary search results are in a "Data Table" object format in the following node:

Object.d.query.PrimaryQueryResult.RelevantResults.Table.Rows.results

For the field title, you can just look at result.Cells.results[3].Value

And for the contained value you'd look at result.Cells.results[6].Value

You get a lot of flexibility with the returned results but those results are also amazing complex compared to what gets returned from the REST API for list items.

Here is an example of how you might do it using jQuery and Q.js. I've not tested this specific code (and I'm sure there are errors) but I have used this exact approach with the ListData.svc REST API from 2010. The problem with this is that pagination of the results becomes difficult unless you want to return every single item to the client for sorting and paging.

$.ajax({
        type: "GET",
        url: "https://domain/PWA/_api/webs?$expand=Webs",
        headers: headers
    }).done(function (data, textStatus, jqXHR) {
        getListData(data.Webs).then(function(response){
            //Do something with the response.
        });
    }).fail(function (jqXHR, textStatus, errorThrown) {
        //Handle errors.
    });


function getListData(webs){
    var promises = [];
    var riskItems = [];
    var defer = Q.defer();
    var i;
    for(i = 0; i < webs.length; i++){
        promises.push(
            $.ajax({
                type: "GET",
                url: webs[i].__metadata.uri + "/lists/GetByTitle('Risks')/items",
                headers: headers
            }).done(function (data, textStatus, jqXHR) {
                riskItems = riskItems.concat(data.d.results);
            }).fail(function (jqXHR, textStatus, errorThrown) {
                //Handle errors.
            })
        );
    }
    Q.all(promises).then(function(){ 
      defer.resolve(parItems); 
    });
    //I'm returning an object like what a list would return
    return defer.promise.then(function(data){
                var retValue = {
                    d: {
                        results: data
                    }
                };
                options.success(retValue);
            });
}
| improve this answer | |
  • Sadly with the ListData.svc we still end up iterating all Webs to query the Risks list. But using the search REST api does allow me to query all the lists at once (to a 500 item limit) I can't use it in a reporting services/excel report, but it does answer the question (very detailed, thanks =) – Choggo Mar 5 '15 at 20:46
  • You can certainly get past that with paging. – Robert Kaucher Mar 5 '15 at 21:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.