Grouping and Aggregates with ServerPaging on Grid

[masterK]

I was wondering if there are any examples of how to implement Grouping and Aggregates in in the grid when ServerPaging is used.  At the moment the group just summarizes what's on the current page not the entire data set which gives incorrect totals.  Since the grid only has a portion of the data I can't see how it can do it any other way.

I'm have a recordset of 50,000 records, obviously I don't want to send all that to the client.  However I also need to implement group and aggregate totals.  I'm calling an Web Service written in C# from JQuery.  An example would be a great help.

If there no examples then I would like to ask the question, with the Kendo UI grid is it possible to group and summarize large datasets?

[masterK]

I can see that when I enable serverPaging and have groups I get extra query string parameters ("group[0][field]" etc) passed to me web service.  Based on these input parameters I'm pretty sure I can create a linq query to create the groupings, totals etc.  

What I have no idea of is how to plug this back into the Grid.  I guess I need to somehow return summary data and then the actual data, probably in a different call.  I just cannot find any examples of how this should be done.

[masterK]

I've gotten to a point where I think I'm performing the grouping correctly on the server side.

My Json Data is
{
   \"Count\":10,
   \"groups\":[
      {
         \"aggregates\":null,
         \"field\":\"Tier\",
         \"value\":\"Tier 1\",
         \"items\":[
            {
               \"ProductLine\":\"CART for Commuters\",
               \"Component\":\"Marketing\",
               \"Tier\":\"Tier 1\"
            },
            {
               \"ProductLine\":\"CART for Commuters\",
               \"Component\":\"Marketing\",
               \"Tier\":\"Tier 1\"               
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 1\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 1\"               
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 1\"
            }
         ],
         \"hasSubgroups\":false
      },
      {
         \"aggregates\":null,
         \"field\":\"Tier\",
         \"value\":\"Tier 2\",
         \"items\":[
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"               
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"               
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"               
            }
         ],
         \"hasSubgroups\":false
      }
   ],    
}

Then my grid config in Jquery looks like:

$("#grid").kendoGrid({
dataSource: {
type: "json",
serverPaging: true,
serverSorting: true,
serverGrouping: true,
                    pageSize: 100,
transport: {
read: "http://localhost:6332/api/View
},
schema: { data: "ViewData", total: "Count", groups: "groups" },
groups: "groups"
},
scrollable: true,
sortable: true,
pageable: true,
columns: colList
});

My grid is loading the data but without any grouping, I would appreciate if somebody could point me to where I'm going wrong.

[masterK]

I've managed to get the groupings working.  I need to do pretty much everything server side (grouping, aggregates, sorting, paging, filtering). However I am getting errors when trying to do an aggregate.

My json data is:
{
   \"Count\":10,
   \"ViewData\":[],
   \"groups\":[
      {
         \"aggregates\":[
            {
               \"Tier\":{
                  \"count\":5
               }
            }
         ],
         \"field\":\"Tier\",
         \"value\":\"Tier 1\",
         \"items\":[
            {
               \"ProductLine\":\"CART for Commuters\",
               \"Component\":\"Marketing\",
               \"Tier\":\"Tier 1\"
            },
            {
               \"ProductLine\":\"CART for Commuters\",
               \"Component\":\"Marketing\",
               \"Tier\":\"Tier 1\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
              \"Tier\":\"Tier 1\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 1\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 1\"
            }
         ],
         \"hasSubgroups\":false
      },
      {
         \"aggregates\":[
            {
               \"Tier\":{
                  \"count\":5
               }
            }
         ],
         \"field\":\"Tier\",
         \"value\":\"Tier 2\",
         \"items\":[
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",               
               \"Tier\":\"Tier 2\"
            },
            {
               \"ProductLine\":\"Platinum\",
               \"Component\":\"Help Content\",
               \"Tier\":\"Tier 2\"
            }
         ],
         \"hasSubgroups\":false
      }
   ],
   \"aggregates\":[
      {
         \"Tier\":{
            \"count\":10
         }
      }
   ],
   \"ErrorMessage\":null,
   \"Success_Flag\":true
}

My code looks like:

$("#grid").kendoGrid({
dataSource: {
type: "json",
serverPaging: true,
serverSorting: true,
serverGrouping: true,
serverAggregates: true,
                           pageSize: 100,
transport: {
read: "http://localhost:6332/api/View?ViewID=222"
},
schema: { data: "ViewData", total: "Count", groups: "groups", aggregates: "aggregates"},
group: {
field: "Tier", aggregates: [{ field: "Tier", aggregate: "count" }]
        }
},
height: 780,
scrollable: true,
sortable: false,
pageable: true,
columns: colList
});

I am trying to create an aggregate on a column named Tier, the column is defined as:
var tiercol= {field:"Tier", title:"Tier", aggregates: ["count"],  groupFooterTemplate: "Count: #=count#" };

When I do this I get an error:
Uncaught TypeError: undefined has no properties

As far as I can see my data is correct. What am I doing wrong with the aggregate function. If I remove the aggregates stuff from the column definition the page renders without error (with no aggregate obviously) so it's definitely the aggregate that causes the problem.

[masterK]

I've create a JSFiddle to demonstrate my issue, as you can see the aggregates aren't appearing:

http://jsfiddle.net/keithnolan/yrk9G/1/

[Petur Subev]

Hello Keith,

When you have enabled server aggregates that mean the aggregates should comes the server. In your jsfiddle you are settings the data through the data() method. Also the  aggregates in the data hierarchy is actually an object not an array.
Here is an updated jsfiddle:
http://jsfiddle.net/knWcJ/74/


Kind Regards,
Petur Subev
the Telerik team

[Matt]

masterK,

What did you do on the server side to get the grouping correct?

We have a WCF Odata Service with the entity framework. We have a SQL view that we are using as a model for the grid. We are getting the records from the database but not sure how to get the grouping applied.

If you get this and can reply I'd greatly appreciate it.

[Keith]

Hi,

What I ended up doing was saving the dataset to the IIS cache.  I then used Linq queries to do the grouping/sorting/paging etc.

I believe if you use the MVC extensions it's possible for the framework to do this for you.