ParamQuery grid support forum

General Category => Help for ParamQuery Pro => Topic started by: jplevene on May 09, 2024, 04:46:30 pm

Title: Calculated cell in footer
Post by: jplevene on May 09, 2024, 04:46:30 pm

I have a grid (see attached) and the only way I could think of to calculate the circled cell is using render:

Code: [Select]

render:function(ui){
if(typeof(ui.rowData["idx"])==="undefined")
{
return {
text: empty(ui.rowData["profit"]) || empty(ui.rowData["revenue"]) ? 0 : floatval(ui.rowData["profit"] / ui.rowData["revenue"], 4)*100 + " %"
}
}
}
This works fine except obviously the value won't export to Excel.  Other options are:

Code: [Select]

groupModel: {
on: true,
header: false,
grandSummary: true,
agg: {
revenue: "sum",
approved_cost: "sum",
unapproved_cost: "sum",
estimated_cost: "sum",
profit: "sum",
//margin: "avg"
}
},
formulas: [
["profit", function(rowData){ return rowData["revenue"] - rowData["approved_cost"] - rowData["unapproved_cost"] - rowData["estimated_cost"]; }],
["margin", function(rowData) {
return !rowData["profit"] || !rowData["revenue"] ? 0 : rowData["profit"] / rowData["revenue"];
}
]
],
I can't use "avg" because of the "0" cells (thus commented) that cause the cell calculation to be incorrect.

Is there a way calculate the footer cell so that it exports to Excel (without using "exportRender" due to an Excel issue), basically the calculation for that row is profit/revenue (being based on the cells on the footer row) or even make it use the formula for that column?

Title: Re: Calculated cell in footer
Post by: paramvir on May 09, 2024, 10:21:10 pm

Custom aggregate functions can be written and used if inbuilt avg function doesn't suit your requirements.

https://paramquery.com/pro/api#method-aggregate

Title: Re: Calculated cell in footer
Post by: jplevene on May 10, 2024, 01:56:17 am

Thank you for this, however your documentation omits to mention the third parameter "data", which is all the data in the grid.

The solution for others:

Code: [Select]

pq.aggregate["margin"] = function(arr, col, data){
var profit=0,
revenue=0;
$.each(data, function(i,v){
profit += floatval(v["profit"]);
revenue += floatval(v["revenue"]);
});
return profit / revenue;
};
Then in the grid options:

Code: [Select]

groupModel: {
on: true,
header: false,
grandSummary: true,
agg: {
revenue: "sum",
approved_cost: "sum",
unapproved_cost: "sum",
estimated_cost: "sum",
profit: "sum",
margin: "margin"
}
},
summaryTitle:{ sum: "{0}", margin:"{0}" },

Title: Re: Calculated cell in footer
Post by: paramvir on May 10, 2024, 12:24:31 pm

Thanks, grid data is same as array of rows which is already mentioned in the docs.