Add a Dynamic Total to a Tabular Report
I have a Tabular Report with an editable Amount item. When the page loads, the total amount should be shown below the report; and if the user updates any amount on any row, the total amount should be updated automatically.
Note: this method does not work if you have a tabular report that might have a very large number of records (as it relies on all records being rendered in the page at one time).
1. Make sure the report always shows all the records. To do this, set the Number of Rows and the Maximum Row Count to a large number (e.g. 1000).
2. Add an item to show the total, e.g. P1_TOTAL_AMOUNT. I use a Number field, and add “disabled=true” to the HTML Form Element Attributes so that the user won’t change it.
3. Examine the generated HTML to see what ID is given to the amount fields in the tabular report. In my case, the amount field is rendered with input items with name “f04” and id “f04_0001”, “f04_0002”, etc.
4. Add the following code to the page’s Function and Global Variable Declaration:
function UpdateTotal () {
var t = 0;
$("input[name='f04']").each(function() {
t += parseFloat($(this).val().replace(/,/g,''))||0;
});
$s("P1_TOTAL_AMOUNT",t.formatMoney());
}
This strips out any commas from the amounts before parsing them as Floats and adding them to a running total; it finally formats the total using my formatMoney function and updates the total amount item.
5. Add the following to the page’s Execute when Page Loads:
$("input[name='f04']").change(function(){UpdateTotal();});
To prime the total amount field when the page is loaded, I have a Before Header process that calculates the total based on a simple query on the table.
Now, in my case I want to have two running totals: one for “Cash” lines and another for “Salary” lines. My tabular report renders a radio button on each record which the user can select “Cash” or “Salary”. So instead of just the one total amount field, I have two: P1_TOTAL_CASH and P1_TOTAL_SALARY. The radio buttons have hidden input items with the value, rendered with id “f05_nnnn” (where nnnn is the row number).
My UpdateTotal function therefore looks like this:
function UpdateTotals () {
var sal = 0, cash = 0, amt, rownum, linetype;
$("input[name='f04']").each(function() {
amt = parseFloat($(this).val().replace(/,/g,''))||0;
// determine if this is a Cash or Salary line
rownum = $(this).prop("id").split("_")[1];
linetype = $("input[id='f05_"+rownum+"']").val();
if (linetype == "SALARY") {
sal += amt;
} else if (linetype == "CASH") {
cash += amt;
}
});
$s("P52_TOTAL_SALARY",sal.formatMoney());
$s("P52_TOTAL_CASH",cash.formatMoney());
}
And my Execute when Page Loads has an additional call:
$("input[name='f05']").change(function(){UpdateTotals();});
Now, when the user changes the amounts or changes the line type, the totals are updated dynamically.
EDIT: simplified jquery selectors based on Tom’s feedback (see comments) and use the hidden field for the radio buttons instead of querying for “checked”
UPDATE: If the tabular form has an “Add Row” button, the above code won’t work on the newly added rows. In this case, the Execute when Page Load should be this instead:
$(document).on("change", "input[name='f05']", function(){UpdateTotals();});
About Jeffrey Kemp
Application Designer & Developer at Oracle specialising in Oracle APEX (Application Express), Oracle SQL and PL/SQL. Oracle ACE Alumni ♠️. Piano player, father, husband, Christian. The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.