CODEWITHSUNDEEP
jqgridjqgrid-formatter
user1042252
user1042252

Reputation: 11

jqGrid - grouping + custom summarytype function

I have a jqGrid on my page that has grouping and contains several columns (A,B,C,D). How do I make the summary (i.e. total row) for column D within each group be the following: sum(D)/Sum(A) * 100?

I know I can define a custom function for the summaryType such as: summaryType: mysum but I am not quite sure, how I can use this to do the above formula? Note, columns A,B, and C all have summaryType set as follows: summaryType: 'sum' so they get calculated using the built in summing functionality. I am also wondering if I can somehow pull this off on the loadComplete event of the jqGrid?

I managed to achieve this by doing the following:

I define global variables for the current value of column A and Column C, and the current group by

var tColA = 0.0;

var tColD = 0.0;

var tCurrentGroupBy;

Then in my function, I do the following:

function mysum(a, b, c) {
    if (tCurrentGroupBy!= c.GroupByCol) {
        tColA= 0.0;
        tColD = 0.0;
        tCurrentGroupBy= c.GroupByCol;
        tColA= tColA + parseFloat(c.ColA);
        tColD = tColD + parseFloat(c.ColD);
    }
    else {
         tColA= tColA + parseFloat(c.ColA);
        tColD = tColD + parseFloat(c.ColD);
    }

    return ((tColD / tColA) * 100);
 }

not sure if there is a better way to do this, but this seems to work. Are there any other suggestions? thanks.

Upvotes: 1

Views: 3623

Answers (3)

Patrice Gagnon
Patrice Gagnon

Reputation: 1454

Do it on the server. It'll be far simpler, you'll have complete control.

Upvotes: 0

max
max

Reputation: 11

Place this formatter function on each cell involved in the percent calculation. In this example, cell a,cell b and cell c need the formatter function. The function is in a and b to grap the value and on c to calulate the percent using the saved values.

It’s important to note 3 things with the formatter event:

1.The cell value is modifiable. Even though it’s a formatting event the value may be changed. 2.The event is fired in the order or the column model 3.Athough, rwdat is row data it is only the column model for the cell being formatter, not the whole row. That’s why the function needs to be on each cell to get all the values. 

<script>
var cnt = 0;
var a = 0.0;
var b= 0.0;

function linePercent(cellval, opts, rwdat, act)
{
//   rowed==”” means it’s a summary line(total)
if (opts.rowId =="")
{
//  save the 2 values used in the calculation
  if (rwdat.nm == "a"){totexec=cellval;}
  if (rwdat.nm == "b"){planamt=cellval;}
//  when the calculated cell is formatted, perform the calculation and set cellval
 if (rwdat.nm == "c"){cellval=(a/b)*100;}
}
//  call formatter to format cellval
return $.fn.fmatter('number', cellval,opts, rwdat, act); 
};
</script>

Upvotes: 1

Otamay
Otamay

Reputation: 111

A little bit late, but...

I have a column D that shows the individual percentage of column C with respect to column B. So I've implemented a formatter that adds the ' %' sign to D column. Column A is the identifier.

But the formatter function is applied to the summary row, too. So the formatter detects if it is the summary row, and if it is, do the calculations and return the value.

function percentage_format(cellValue, options, rowObject) {
  var a=$(rowObject).attr('A');

  if(!a || a='')
    cellValue=Math.round($(rowObject).attr('C')*10000/$(rowObject).attr('B'))/100;

  return "<span originalValue='"+cellValue+"'>"+cellValue+" %"+"</span>";
}
function unformatCampo(cellValue, options, cellObject) {
return $(cellObject.html()).attr("originalValue");
}

I hope this still helps.

Upvotes: 2

Related Questions