SharePoint ListItems Group By

SharePoint Grouped ListItems

In the above screenshot, I have applied a GroupBy setting on a SharePoint View. I needed the same exact values in my application through JSOM. However, all of the online examples, that I came across, were of retrieving all the ListItems of a SharePoint List. You can either retrieve all the items at once or in batches, but there seems to be no provision of applying grouping or aggregation on the ListItems through code!

Somehow, I wasn’t convinced of this and was vehemently against this idea of pulling all the data on client-side first, thereafter applying grouping and aggregations.

As it turned out, there’s a way to directly query grouped data from SharePoint, saving the overhead of doing it all on client-side. In this post, I’ll show how to retrieve items from SharePoint, grouped by a SharePoint field using CSOM & JSOM. If you’re interested in applying aggregations (read TOTALS) only, without grouping, then kindly refer to this post, SharePoint TOTALS.

CSOM

SP = Microsoft.SharePoint.Client;

using (SP.ClientContext ctx = new SP.ClientContext(siteUrl))
{
	ctx.Credentials = new SP.SharePointOnlineCredentials("piyush@something.onmicrosoft.com", SecureStringPwd);
	SP.List list = ctx.Web.Lists.GetByTitle("GroupByTest");
	string viewXml = "<View><Query><GroupBy Collapse=\"TRUE\" GroupLimit=\"30\"><FieldRef Name=\"CompanyName\"/></GroupBy></Query><ViewFields><FieldRef Name=\"LinkTitle\"/><FieldRef Name=\"CompanyName\"/><FieldRef Name=\"Profit\"/></ViewFields><RowLimit Paged=\"TRUE\">30</RowLimit><Aggregations Value=\"On\"><FieldRef Name=\"Profit\" Type=\"SUM\"/></Aggregations></View>";


	SP.ClientResult<string> groupBy = list.RenderListData(viewXml);

	ctx.ExecuteQuery();
}

App/Add-In

Following is the code snippet for a SharePoint hosted app.

viewXml = {
'query':
'<View><Query><GroupBy Collapse="TRUE" GroupLimit="30"><FieldRef Name="CompanyName"/></GroupBy></Query><ViewFields><FieldRef Name="LinkTitle"/><FieldRef Name="CompanyName"/><FieldRef Name="Profit"/></ViewFields><RowLimit Paged="TRUE">30</RowLimit><Aggregations Value="On"><FieldRef Name="Profit" Type="SUM"/></Aggregations></View>'
}

jQuery.ajax({
        url: "appWebUrl" + "/_api/Sp.AppContextSite(@target)/Web/Lists(guid'" + listID + "')/renderlistdata(@viewXml)?@viewXml='" + viewXml.query + "'&$select=" + fields + "&@target='hostWebUrl'",
        type: "POST",
        headers: { 
            "Accept": "application/json;odata=verbose",
            "content-type": "application/json;odata=verbose",
            "X-RequestDigest": $("#__REQUESTDIGEST").val()
        },
        success: doSuccess,
        error: doError
});

JSOM

var list = clientContext.get_web().get_lists().getByTitle('GroupByTest');
var viewXml = "<View><Query><GroupBy Collapse=\"TRUE\" GroupLimit=\"30\"><FieldRef Name=\"CompanyName\"/></GroupBy></Query><ViewFields><FieldRef Name=\"LinkTitle\"/><FieldRef Name=\"CompanyName\"/><FieldRef Name=\"Profit\"/></ViewFields><RowLimit Paged=\"TRUE\">30</RowLimit><Aggregations Value=\"On\"><FieldRef Name=\"Profit\" Type=\"SUM\"/></Aggregations></View>";
var groupBy = list.renderListData(viewXml);
clientContext.executeQueryAsync(function(){console.log(groupBy); },function(e){console.log(e);})

As you can clearly see that the trick lies in the CAML query (viewXml). We have to embed the grouping information in it to fetch grouped listitems.

CAML

<View>
	<Query>
		<GroupBy Collapse="TRUE" GroupLimit="30">
			<FieldRef Name="CompanyName"/>
		</GroupBy>
	</Query>
	<ViewFields>
		<FieldRef Name="LinkTitle"/>
		<FieldRef Name="CompanyName"/>
		<FieldRef Name="Profit"/>
	</ViewFields>
	<RowLimit Paged="TRUE">30</RowLimit>
	<Aggregations Value="On">
		<FieldRef Name="Profit" Type="SUM"/>
	</Aggregations>
</View>

The output will be in the following format:

{
	"Row": [{
		"CompanyName": "A",
		"CompanyName.urlencoded": "%3B%23A%3B%23",
		"CompanyName.COUNT.group": "3",
		"CompanyName.newgroup": "1",
		"CompanyName.groupindex": "1_",
		"Profit.SUM": "\u20b9 221.43",
		"Profit.SUM.agg": "\u20b9 157.44"
	},
	{
		"CompanyName": "B",
		"CompanyName.urlencoded": "%3B%23B%3B%23",
		"CompanyName.COUNT.group": "2",
		"CompanyName.newgroup": "1",
		"CompanyName.groupindex": "2_",
		"Profit.SUM": "\u20b9 221.43",
		"Profit.SUM.agg": "\u20b9 63.99"
	}],
	"FirstRow": 1,
	"FolderPermissions": "0x7fffffffffffffff",
	"LastRow": 2,
	"FilterLink": "?",
	"ForceNoHierarchy": "1",
	"HierarchyHasIndention": ""
}

For aggregation, SharePoint supports the following values

Si No.

Total Function

Function Type

1 Count COUNT
2 Average AVG
3 Maximum MAX
4 Minimum MIN
5 Sum SUM
6 Std Deviation STDEV
7 Variance VAR

 

Key Takeaway

  • The output will be in the format, [Field].[Aggregation].agg. In this case, since I wanted the sum of the field, Profit, the output key is in the format, Profit.SUM.agg, where agg is always constant.

21 thoughts on “SharePoint ListItems Group By

  1. Hi When I try this I get an exception around RenderListData something about it not being a function.
    What version of Microsoft.Sharepoint.Client are you using? and what version of sharepoint. I’m on 2010 – does that make a difference?

    Like

    • Hi Richard,

      Yes, unfortunately it makes a whole lot of difference. RenderListData seems to be introduced since, SharePoint 2013. I have used the above example on SharePoint Online. It should work on SharePoint 2013 & SharePoint 2016 as well.

      The version of my, ‘Microsoft.SharePoint.Client’ dll is, ‘16.0.0.0’.

      Like

  2. Oh well – I guess it’ll be a nice ‘upgrade’ that I can do when we move to sharepoint 2013.
    Still a nice article and against a lot of ‘can’t be done’ stuff I read.

    Ta’s for the quick response.
    Richard

    Like

  3. Hi, I use jsom, but happen a error
    the server responded with a status of 403 .There is no app context to execute this request

    Like

    • Ok. I know ,the url is error . it should be
      “appWebUrl” + “/_api/Web/Lists(guid'” + listID + “‘)/renderlistdata(@viewXml)?@viewXml='” + viewXml.query + “‘&$select=” + fields + “&@target=’hostWebUrl'”

      Thank you very much!

      Like

      • OK, the url used in the JSOM ex is for a SharePoint App. That’s why I was using AppContextSite. For plain JSOM, to be directly used inside SharePoint, your url is correct. Will update the post accordingly. Thanks 🙂

        Like

  4. Thanks for your help, but I met a new question:

    to load resource: the server responded with a status of 500 (Internal Server Error)

    The HTTP method ‘GET’ cannot be used to access the resource ‘RenderListData’. The operation type of the resource is specified as ‘Default’. Please use correct HTTP method to invoke the resource.

    When I have three where conditions, the above error will occur
    My ViewXml like this:

    L89

    Jar – Plastic

    20

    When the where condition is less than three, no error occurred, and the ViewXml like this:

    L89

    Jar – Plastic

    I think there may be too long url, do you have any suggestions?

    Thanks ☺

    Like

    • Sorry, the query is formatted and is not displayed.
      three conditions

      L89

      Jar – Plastic

      20


      two conditions

      L89

      Jar – Plastic

      Like

      • Hey, can you please encode your code inside the following tag in your comment. ‘Cause otherwise it’s bit hard to figure out,
        wp code insert

        In general, url characters up to 2000 characters works perfectly fine.So, can you also check the entire length of the url that is being sent?

        Like

  5. Hi, Piyush

    Thank you very much for your reply. After debugging, I found that the problem is not url length.
    The problem is that the where statement is malformed. When there are three conditions, my format is like:
    (And)
    where1
    where2
    where3
    (/And)
    In fact this is the wrong way to write. it should be:
    (And)
    where1
    (And)
    where2
    where3
    (/And)
    (/And)

    here
    That’s all 😊

    Like

    • In addition, if i query the data is using the following wording:
      var clientContext = new SP.ClientContext(siteUrl);
      clientContext.executeQueryAsync(fun,fun)

      How can i use the function of the RenderListData ?

      Like

      • Hi, Jeff,

        Thanks to your comment, I can see the problem in the post. The ex given above is not for JSOM but for SharePoint REST API (App). The query for JSOM would be

        var list = clientContext.get_web().get_lists().getByTitle('listTitle');
        var groupBy = list.renderListData(viewXml);
        clientContext.executeQueryAsync(function(){console.log(groupBy); },function(e){console.log(e);})
        

        Will update the post accordingly. Thanks! 🙂

        Like

  6. Hi, Piyush

    Have you ever encountered such a problem?
    My list has more than 5000 items. When I use ‘Group by’ in CSOM, there is an exception:
    Additional information: The attempted operation is prohibited because it exceeds the list view threshold enforced by the administrator.

    And my query is:

    <View>
    	<Query>
    		<GroupBy Collapse='TRUE' GroupLimit='200'><FieldRef Name='SU'/></GroupBy>
    	</Query>
    	<ViewFields>
    		<FieldRef Name='SU'/>
    	</ViewFields>
    </View>
    

    I have referred to your article: Query ListItems in Batches – SharePoint Online
    And updated my query:

    <View>
    	<Query>
    		<GroupBy Collapse='TRUE' GroupLimit='200'><FieldRef Name='SU'/></GroupBy>
    	</Query>
    	<ViewFields>
    		<FieldRef Name='SU'/>
    	</ViewFields>
    	<RowLimit Paged='TRUE'>30</RowLimit>
    </View>
    

    But will still throw the same exception. Do you have any advice?

    Like

    • Hi Jeff,

      This restriction is imposed by SharePoint. SharePoint does not perform any operation on Lists having more than 5000 items. Even if we specify group limit, it first has to perform grouping on the given items before applying the limit. Since, it’s an expensive process, in an online environment it’s not directly possible.

      So, you can try two things: –
      * Reduce the number of items by applying some filtering in the CAML. Do you really need to see all the items? If not then, remove the unnecessary items.
      * Download all the items in batches and then, apply grouping through your code.

      Like

    • Unfortunately, it’s a very strict restriction imposed by Microsoft on SharePoint Online! The sole purpose of this post was to leverage the in-built features of SharePoint. However, on List with more than 5000 items they simply block a lot of operations. 😐

      Like

  7. Hi. How can I group, for example, blogs by author? GroupBy result consists only .COUNT.group field that seems useful.Row array is a flat collection of items and totally unuseful. I expects something like this:
    {
    groups:{
    AuthorID:{
    Blogs:[]
    AuthorInfo:
    },
    AuthorID:{
    Blogs:[]
    AuthorInfo:
    },
    ……
    }
    }

    Is it possible?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s