{"id":123961,"date":"2015-06-29T10:23:43","date_gmt":"2015-06-29T08:23:43","guid":{"rendered":"http:\/\/guriddo.net\/?post_type=kbe_knowledgebase&p=123961"},"modified":"2015-07-14T10:06:48","modified_gmt":"2015-07-14T08:06:48","slug":"customized-aggregations-in-grouping","status":"publish","type":"kbe_knowledgebase","link":"http:\/\/guriddo.net\/?kbe_knowledgebase=customized-aggregations-in-grouping","title":{"rendered":"Customized Aggregations in grouping"},"content":{"rendered":"

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<\/a> can be used. This is good in most cases, but in certain situations we need to perform complex calculations\u00a0 using the\u00a0 calculated data of more than one columns. Example of this can be the difference\u00a0 of two summary columns as percentage.<\/p>\n

In this article I will describe how this can be done.<\/p>\n

General we will use two colModel properties – formatter<\/a> and summaryType<\/a>. Both of these options will be defined as custom functions.<\/p>\n

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

Let suppose that we need to calculate the difference of two summary columns in percentage and put the result in another column.<\/p>\n

The colModel can look like this<\/p>\n

[code]<\/p>\n

colModel: [
\n{ label: ‘Inv No’, name: ‘id’, width: 75, key:true },
\n{ label: ‘Date’, name: ‘invdate’, width: 90 },
\n{ label: ‘Client’, name: ‘name’, width: 100 },
\n{ label: ‘Amount’, name: ‘amount’, width: 80 },
\n{ label: ‘Tax’, name: ‘tax’, width: 80 },
\n{ label: ‘Total’, name: ‘total’, width: 80 },
\n{ label: ‘Notes’, name: ‘note’, width: 150 }
\n],<\/p>\n

[\/code]<\/p>\n

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:<\/p>\n

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

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.<\/p>\n

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.<\/p>\n

The demo can be seen here<\/a><\/p>\n

The PHP demo with source code view can be seen here<\/a><\/p>\n

Enjoy<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","template":"","kbe_taxonomy":[91,92],"kbe_tags":[],"_links":{"self":[{"href":"http:\/\/guriddo.net\/index.php?rest_route=\/wp\/v2\/kbe_knowledgebase\/123961"}],"collection":[{"href":"http:\/\/guriddo.net\/index.php?rest_route=\/wp\/v2\/kbe_knowledgebase"}],"about":[{"href":"http:\/\/guriddo.net\/index.php?rest_route=\/wp\/v2\/types\/kbe_knowledgebase"}],"author":[{"embeddable":true,"href":"http:\/\/guriddo.net\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/guriddo.net\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=123961"}],"version-history":[{"count":13,"href":"http:\/\/guriddo.net\/index.php?rest_route=\/wp\/v2\/kbe_knowledgebase\/123961\/revisions"}],"predecessor-version":[{"id":124048,"href":"http:\/\/guriddo.net\/index.php?rest_route=\/wp\/v2\/kbe_knowledgebase\/123961\/revisions\/124048"}],"wp:attachment":[{"href":"http:\/\/guriddo.net\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=123961"}],"wp:term":[{"taxonomy":"kbe_taxonomy","embeddable":true,"href":"http:\/\/guriddo.net\/index.php?rest_route=%2Fwp%2Fv2%2Fkbe_taxonomy&post=123961"},{"taxonomy":"kbe_tags","embeddable":true,"href":"http:\/\/guriddo.net\/index.php?rest_route=%2Fwp%2Fv2%2Fkbe_tags&post=123961"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}