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
[code]
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 }
],
[/code]
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:
[code]
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
}
[/code]
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 PHP demo with source code view can be seen here
Enjoy
Copyright 2014 TriRand LtdAll Rights ReservedRSS
Back to Top