Customized Aggregations in grouping

We know that when using grouping we can define a lot of custom options to perform calculations on certain column. For this purpose a custom summaryType can be used. This is good in most cases, but in certain situations we need to perform complex calculations  using the  calculated data of more than one columns. Example of this can be the difference  of two summary columns as percentage.

In this article I will describe how this can be done.

General we will use two colModel properties – formatter and summaryType. Both of these options will be defined as custom functions.

It is unknown maybe that the summaryType  when defined as function can return  object to store the calculations. We will use this property again with custom formatter which should place the calculated value on the right place.

Let suppose that we need to calculate the difference of two summary columns in percentage and put the result in another column.

The colModel can look like this


colModel: [
{ label: 'Inv No', name: 'id', width: 75, key:true },
{ label: 'Date', name: 'invdate', width: 90 },
{ label: 'Client', name: 'name', width: 100 },
{ label: 'Amount', name: 'amount', width: 80 },
{ label: 'Tax', name: 'tax', width: 80 },
{ label: 'Total', name: 'total', width: 80 },
{ label: 'Notes', name: 'note', width: 150 }
],

In this scenario we will make a summary of columns Amount and Tax and put the difference in percentage in the Total column. To do this we need to define the grouping and the summary types:

colModel: [
{ label: 'Inv No', name: 'id', width: 75, key:true },
{ label: 'Date', name: 'invdate', width: 90 },
{ label: 'Client', name: 'name', width: 100 },
{ label: 'Amount', name: 'amount', width: 80, summaryType: 'sum', formatter:'number' },
{ label: 'Tax', name: 'tax', width: 80, summeryType: 'sum', formatter: 'number' },
{ label: 'Total', name: 'total', width: 80,
   summaryType : function(value, name, record) {
      // initialize the value object
      if(typeof value === 'string'){
         value = {totalAmount: 0, totalTax: 0 };
      }
      // perform summary
      if(record['amount']) {
         value.totalAmount += parseFloat(record['amount']);
      }
      if(record['tax']) {
         value.totalTax += parseFloat(record['tax']);
      }
      return value;
   },
   formatter: function (cellval, opts, rwdat, act) {
     // get the regional options and pass it to the custom formatter
     opts = $.extend({}, $.jgrid.getRegional(this, 'formatter') , opts);
     // determine if we are in summary row to put the value
     if (opts.rowId === '') {
        if(cellval.totalAmount > 0) {
           var val = (cellval.totalAmount - cellval.totalTax)/cellval.totalAmount*100;
           return $.fn.fmatter('number', val, opts, rwdat, act)+' %';
        } else {
           return '0';
        }
     } else {
         return $.fn.fmatter('number', cellval, opts, rwdat, act);
     }
  }
},
{ label: 'Notes', name: 'note', width: 150 }
],
grouping: true,
groupingView: {
   groupField: ["name"],
   groupColumnShow: [true],
   groupText: ["<b>{0}</b>"],
   groupOrder: ["asc"],
   groupSummary: [true],
   groupCollapse: false
}

The trick here to put the new calculated value from formatter is to check if the rowId parameter is empty. This will grand that this is the summary value that we want to place in the summary row.

As can be seen using custom formatter and custom summaryType we can make any calculations, which make Guriddo jqGrid a simple powerful java script reporting engine.

The demo can be seen here

The PHP demo with source code view can be seen here

Enjoy

Leave a comment

Stay connected with us in your favorite flavor!