Get Aggregation Totals Using RenderListDataAsStream in SharePoint

If your SharePoint list already makes use of aggregate functions and you want to leverage that lists’ aggregation data instead of calculating it yourself, you can make use of the SharePoint REST’s RenderListDataAsStream API endpoint.

In this blog, we’ll be running the query on a sample list of 10 students and their grades for the English subject.

The list below shows two views with each view corresponding to the aggregate function being used on the Grade column (not required for the query to work, but it’s good to have them be visible on the list).

Note that we’ve returned to the classic SharePoint experience, the aggregation values don’t show up in the modern experience, as of this posting.

We will be using jQuery’s ajax in our solution. Here is the relevant code (we’re going to get the standard deviation):

  1. var body =  {
  2.     “parameters”: {
  3.         “RenderOptions”: 2, // ListData
  4.         “ViewXml”: “<View><ViewFields></ViewFields><RowLimit>1</RowLimit><Aggregations><FieldRef Name=\”Grade\” Type=\”STDEV\” /></Aggregations></View>”
  5.     }
  6. }
  7. jQuery.ajax({
  8.         url: _spPageContextInfo.webAbsoluteUrl + “/_api/Web/Lists/getbytitle(‘EnglishGrades’)/RenderListDataAsStream”,
  9.         type: “POST”,
  10.         headers: {
  11.             “Accept”: “application/json;odata=verbose”,
  12.             “content-type”: “application/json;odata=verbose”,
  13.             “X-RequestDigest”: $(“#__REQUESTDIGEST”).val()
  14.         },
  15.         data: JSON.stringify(body),
  16.         success: function(success) {
  17.             console.log(“success”, success);
  18.         },
  19.         error: function(error) {
  20.             console.log(“failure”, error);
  21.         }
  22. });

The values in body variable determine what gets picked up by the query. We pass in 2 under RenderOptions to tell the query that it’s getting data from a list, and we supply whatever columns we need through the ViewXml key.

RenderListData as stream returns quite a lot of data, so we need to explicitly tell it what we want in order to cut down on what it returns, which makes up most of the ViewXml logic.

Everything is wrapped inside the <View> tag. We’ll go through them in order.

<ViewFields></ViewFields> : If we wanted to get another column from the list, we could add a <FieldRef Name=\”[Internal Name of Column] \”/> inside the ViewFields element, but we only want the aggregate totals field, which will be returned regardless of ViewFields’ contents.

<RowLimit>1</RowLimit> : Since the aggregation total value can be found on all row entries returned by the query, we only need to get the first one.

<Aggregations><FieldRef Name=\”Grade\” Type=\”STDEV\” /></Aggregations> : This tells the query that we want to apply an aggregation total Std Deviation to the Grade column. This makes the field Grade.STDEV (Internal Name of Field + “.” + Aggregation Type) available in the query results.

Pictured below is the value returned by the query:

It’s important to note that we are only able to reference a field once in the Aggregations element.

We have to run another query to get the Average in this case. As mentioned earlier, the query isn’t tied down to any of the views that you’ve made.

Have you tried this before? What do you think? We hope we helped you get aggregation totals using the RenderListDataAsStream in Microsoft SharePoint. Don’t forget to share this blog on social media.

WATCH: Strategies to Avoid Data Sprawl

Add a Comment

Your email address will not be published. Required fields are marked *