Nested aggregate footer using remote data

[Ron]

Hi,I am trying to populate a grid with remote data. All data, grouping, filtering, sorting is done on the server. Everything works smoothly when using aggregate data in the headers. But I want aggregate data in the footer as well. I came up with the following somewhat funky solution to display the aggregate count in the footer. This works ok with 1 level aggregation (allthough the solution is a bit ugly to me). But when I apply a 2nd level of aggregation this solution won't work. The 2nd level show the right count, but the 1st level just shows the count of the latest 2nd level aggregation. 

It may be of use to state that users will be able to define their own grouping by using the drag & drop feature of the grid.

I have attached a screenshot of the result of the current solution. The part in the red circle is what's wrong. I would want to have 2 rows with a count of 1 and 4 respectively.

$(document).ready(function () {
    var grid = $("#grid").kendoGrid({
        dataSource: {

            dataType: 'json',
            transport: {
                read: {
                    url: '/remoteurl',
                    dataType: 'json',
                    data: {
                        resultid: 'report_myprojects'
                    }
                }
            },
            schema: {
                data: 'results',
                total: 'total',
                groups: 'groups',
                aggregates: 'aggregates'
            },
            pageSize: 20,
            serverPaging: true,
            serverGrouping: true,
            serverFiltering: true,
            serverSorting: true

        },
        groupable: true,
        sortable: true,
        pageable: {
            refresh: true,
            pageSizes: [10, 20, 50, 100],
            buttonCount: 5
        },
        columns: [{
            field: "fld_1001",
            title: "Aangemaakt op",
             groupHeaderTemplate1: "Created: #=value# (Count: #=count#) #saveCurrentAggregate(data)#",
            ,groupFooterTemplate: "Total: #=getCurrentAggregate()#"
        }, {
            field: "pro1",
            title: "Status",
             groupHeaderTemplate: "Status: #=value# (Count: #=count#) #saveCurrentAggregate(data)#",
        }, {
            field: "fld_1000",
            title: "Created by",
            groupHeaderTemplate: "Created by: #=value# (Count: #=count#) #saveCurrentAggregate(data)#",
        }, {
            field: "flddef_1",
            title: "Project",
            groupHeaderTemplate: "Project: #=value# (Count: #=count#) #saveCurrentAggregate(data)#",
        }, {
            field: "flddef_2",
            title: "Project lead"
            groupHeaderTemplate: "Project lead: #=value# (Count: #=count#) #saveCurrentAggregate(data)#",
        }, {
            field: "flddef_21",
            title: "Start"
            groupHeaderTemplate: "Start: #=value# (Count: #=count#) #saveCurrentAggregate(data)#",
        }
        ]
    });
});

var cur_aggregate;

function saveCurrentAggregate(data) {
    cur_aggregate = data.count;
}

function getCurrentAggregate() {
    return cur_aggregate;
}

[Ron]

Fixed it so far by changing my aggregate functions into:

var cur_aggregate = new Array();
function saveCurrentAggregate(data) {
  cur_aggregate[data.field] = data.count;
}
function getCurrentAggregate(data) {
  return cur_aggregate[data.parent().field];
}

But still I feel like I'm missing something. Why is the "count'  aggregate function not available in my footer template? I have even added an aggregate member in my grid def like this:

,aggregates: [
              {field: "fld_1001",
               aggregates: "count"},
              {field: "pro1",
               aggregates: "count"},
              {field: "fld_1000",
               aggregates: "count"},
              {field: "flddef_1",
               aggregates: "count"},
              {field: "flddef_2",
               aggregates: "count"},
              {field: "flddef_21",
               aggregates: "count"}
            ]

[Alexander Valchev]

Hello Ron,

In case the groups are not pre-defined but can be changed by the user via drag and drop you should define the aggregates at column level. Please check the following example:

<div id="grid"></div>
<script>
    $("#grid").kendoGrid({
        columns: [
            { field: "name" },
            {
                field: "age",
                aggregates: ["count"],
                groupFooterTemplate: "Total: #= count #"
            }
        ],
        groupable: true,
        dataSource: {
            data: [
                { name: "Jane Doe", age: 30 },
                { name: "John Doe", age: 30 }
            ]
        }
    });
</script>

In this way you should be able to use directly the count aggregate in the template.

Regards,
Alexander Valchev
Telerik

 

 

[Ron]

Hi Alexander,

I have added aggregates at the column level as you suggested and altered the group footer to match yours. But it still won't work. Right now a javascript error occurs when grouping even a single level via drag and drop:  Uncaught TypeError: undefined has no properties. To make sure, should I only provide a groupFooterTemplate to the first column or to all the columns? I have tried all thinkable combination without success. I there something wrong perhaps with my datasource? My data looks like this when grouping on 2 columns:

{

        "total": 843,

        "groups": [{

            "hasSubgroups": true,

            "field": "fld_1001",

            "aggregates": {

                "fld_1001": {

                    "count": 1

                }

            },

            "value": "16 apr 2014 10:11",

            "items": [{

                "hasSubgroups": false,

                "field": "pro1",

                "aggregates": {

                    "pro1": {

                        "count": 1

                    }

                },

                "value": "Conceptueel",

                "items": [{

                    "pro1": "Conceptueel",

                    "flddef_21": "16 apr 2014",

                    "flddef_2": "Test Tester",

                    "flddef_700": "Test action 2",

                    "flddef_1": "Test projectnaam 1",

                    "flddef_701": "",

                    "flddef_703": "",

                    "fld_1001": "16 apr 2014 10:11",

                    "fld_1000": "Ron van Pol"

                }]

            }]

        }, {

            "hasSubgroups": true,

            "field": "fld_1001",

            "aggregates": {

                "fld_1001": {

                    "count": 1

                }

            },

            "value": "16 apr 2014 10:12",

            "items": [{

                "hasSubgroups": false,

                "field": "pro1",

                "aggregates": {

                    "pro1": {

                        "count": 1

                    }

                },

                "value": "Conceptueel",

                "items": [{

                    "pro1": "Conceptueel",

                    "flddef_21": "15 apr 2014",

                    "flddef_2": "Fred van Pol",

                    "flddef_700": "",

                    "flddef_1": "Test projectnaam 2",

                    "flddef_701": "",

                    "flddef_703": "",

                    "fld_1001": "16 apr 2014 10:12",

                    "fld_1000": "Ron van Pol"

                }]

            }]

        }, {
                  ...

        }

}

[Alexander Valchev]

Hello Ron,

I compared the data format with the one from the relevant documentation and it looks correct. I am not sure what exactly causes the issue.
Is it possible for you to send me a small but runnable sample with mock data that isolates the issue? It this way I will be able to reproduce the error locally and test different solutions.

Please provide such project and I will check it right away.

Regards,
Alexander Valchev
Telerik

 

 

[Ron]

I have created a small sample: http://jsfiddle.net/knWcJ/274. The remote datasource is a bit static, but the data returned should match either:

- no grouping
- grouping only on "Projectleider"
- grouping on "Projectleider" and "Startdatum"

This sample will break on the footertemplate "Count: #=count#"

[Ron]

The following sample show the (somewhat funky) solution I came up with to fix my problems: http://jsfiddle.net/knWcJ/276/

[Rosen]

Hello Ron,

The error you have described is caused by incorrect grouped data result. As you may know all of the defined aggregates should exists on all of the grouping levels. For example if a count function is defined for the foo field. The aggregate result for the field should be calculated for all of the grouping levels not just for the one which is grouped by the foo field.

[{
  aggregates: {
    FIEL1DNAME: {
      FUNCTON1NAME: FUNCTION1VALUE,
      FUNCTON2NAME: FUNCTION2VALUE
    },
    FIELD2NAME: {
      FUNCTON1NAME: FUNCTION1VALUE
    }
  },
  field: FIELDNAME,
  hasSubgroups: true,
  items: [
    {
      aggregates: {
       FIEL1DNAME: {
        FUNCTON1NAME: FUNCTION1VALUE, // calculated for the nested items
        FUNCTON2NAME: FUNCTION2VALUE // calculated for the nested items
       },
       FIELD2NAME: {
        FUNCTON1NAME: FUNCTION1VALUE // calculated for the nested items
       }
      },
      field: NESTEDGROUPFIELDNAME,
      hasSubgroups: false,
      items: [
      // data records
      ],
      value: NESTEDGROUPVALUE
    },
    //nestedgroup2, nestedgroup3, etc.
  ],
  value: VALUE // the group key
} /* other groups */
]

The data you have pasted is working for group headers as each groupheader access aggregates data only for the field the actual group item is group by. However, in the group footers all of the fields for which the aggregates are defined and accessed in the template are evaluated.

Regards,
Rosen
Telerik

 

 

[Ron]

I'm sorry, but I still can't get this stuff working properly. I'm still not sure how my data should look like in my specific case. Your response is too abstract for my liking. Could you be more specific to my example? Should I perhaps take this discussion to a private support ticket?

[Rosen]

Hello Ron,

As I have mentioned in my previous answer, all of the aggregates should be calculated for every group level and should exist in the result. Here you can find a modified version of your sample page which demonstrates this.

Regards,
Rosen
Telerik

 

 

[Ron]

Hi, I have gotten things working since last time. But unfortunately I'm facing another riddle concerning remote grouping and aggregates. Consider the example on http://jsfiddle.net/DA75z/2/. I have defined a footerTemplate for a column which is using the aggregate function "sum". I have defined this function in my configuration for the grid but instead of getting the sum in the bottom row on the grid, I get a "NaN". Am I missing something in my output?

On a side note: if I changed the aggregate function to count instead of sum, then I get results (see http://jsfiddle.net/DA75z/3/).

[Alexander Valchev]

Hello Ron,

I assume that you would like to calculate the aggregates on the server because your example is using server side operations.
In that case you should set the serverAggregates option of the DataSource to true and include the aggregates in the server response:

function getData(groups) {
    var dataVal = {
        "total": {
            "rowcount0": {
                "sum": 208
            },
            "totalcount": {
                "sum": 1000
            }
        },
        "aggregates": {
            "rowcount0": {
                "sum": 20000
            }
        },
        "groups": [{

On a side note, I am not sure why the "total" field contains aggregates. According to the schema that you have specified the aggregates are located in the "aggregates" field:

schema: {
    data: 'results',
    total: 'total',
    groups: 'groups',
    aggregates: 'aggregates'
},

Regards,
Alexander Valchev
Telerik

 

 

[Ron]

Brilliant, works like a charm. Thanks for the help. But I do have to point out that the documentation on this subject could be a lot better. It is either too hard to find or incomplete. Anyway, I got this working properly now and I'm very thankful for the awesome forum support!

[Alexander Valchev]

Hi Ron,

Thank you for the feedback! We are currently working on improving the documentation by including more same scenarios and 'walk-through' articles.

Regards,
Alexander Valchev
Telerik

 

 

[Patrick]

Hello,

Is there any chance any of that new documentation is ready? I'm struggling with this same issue even after closely following and inspecting all of the given examples. I'm trying to start simple by modifying the groupHeaderTemplate for a single column in my grid:

{
     "encoded":false,
     "title":"Status",
     "field":"job.currentStateEnum",
     "aggregates":["count"],
     "groupHeaderTemplate":"Count: #=count#",
     "hidden":false,
     "width":100
}

And my json from the server includes:

aggregates: [
  {
    "job.currenStateEnum": {
      "count": 1
    }
  }
]

for each group but when I try to group on the column, I get this error:

Uncaught ReferenceError: count is not defined

When I try to group on any of the other columns it continues to work fine with the default header. Any advice here? Am I missing some sort of configuration?

[Alexander Valchev]

Hello Pat,

Is there any chance any of that new documentation is ready?

We recently added a lot of new "how to" help articles which you can find near the widget overview topic:

• http://docs.telerik.com/kendo-ui/web/grid/introduction (see the navigation tree on the right)

I am not sure what is the cause of the exact issue you have. Is it possible for you to provide a small but runnable Kendo Dojo test page which isolates the issue?  In this way I will be able to examine your exact implementation and advice you further.

Regards,
Alexander Valchev
Telerik

 

 

[Patrick]

Hello Alexander,
Thank you for your reply. I ended up figuring out the issue shortly after posting. I was trying to pass back the aggregates in the server response as a JSON array instead of an object.