How to Calculate total sum of interactive grid column into page item in oracle APEX.
Most of the time, this question was asked by my friends and colleagues that “How to Calculate total sum of interactive grid column into page item in oracle APEX” then i got solution in two ways.
Solution 1.
- Need to create an editable interactive grid report using emp table and Page item Px_ITEM.
- Copy and Paste below code in “Function and Global Variable Declaration”.
(function($) {
function update(model) {
var salKey = model.getFieldKey("SAL"),
total = 0;
console.log(">> starting sum SAL column")
model.forEach(function(record, index, id) {
var sal = parseFloat(record[salKey]),
meta = model.getRecordMetadata(id);
if (!isNaN(sal) && !meta.deleted && !meta.agg) {
total += sal;
}
});
console.log(">> setting sum SAL column to " + total)
$s("P23_TOTAL", total);
}
$(function() {
$("#emp").on("interactivegridviewmodelcreate", function(event, ui) {
var sid,
model = ui.model;
if ( ui.viewId === "grid" ) {
sid = model.subscribe( {
onChange: function(type, change) {
console.log(">> model changed ", type, change);
if ( type === "set" ) {
if (change.field === "SAL" ) {
update( model );
}
} else if (type !== "move" && type !== "metaChange") {
update( model );
}
},
progressView: $("#P23_TOTAL")
} );
update( model );
model.fetchAll(function() {});
}
});
});
})(apex.jQuery);
Note: Defile region static id- “emp”.
Solution 2:
- Now create a dynamic action on the SAL column of IG for change event.
- Create a true action as Set Value. Set the type PL/SQL expression as :SAL and submit the SAL.
- Create another true action to execute JavaScript code and add the following code:
var model = apex.region("ONTOOR").widget().interactiveGrid("getViews", "grid").model;
var n_amt, n_totamt = 0;
col_amt = model.getFieldKey("SAL");
model.forEach(function(igrow) {
n_amt = parseInt(igrow[col_amt], 10);
if (!isNaN(n_amt)) {
n_totamt += n_amt;
}
});
apex.item("P23_TOTAL").setValue(n_totamt);
