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”

tabularreportdynamictotal

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();});

5 thoughts on “Add a Dynamic Total to a Tabular Report

  1. Hi Jeff,

    Just a fair warning when working with fractions. See http://stackoverflow.com/questions/588004/is-floating-point-math-broken
    Especially given money, there will likely be cases where you’ll get “wrong” numbers.

    > parseFloat(‘10.1’) + parseFloat(‘9.2’)
    19.299999999999997

    No real workaround either. As is mentioned on that SO thread and in the book Javascript: the good parts (http://books.google.com/books?id=PXa2bby0oQ0C&pg=PA105&dq=douglas+crockford+javascript+good+parts+dollar+values+can+be+converted&rview=1&cd=1#v=onepage&q=&f=false) you may consider working not in dollars but in the unit that has no fractional parts (cents) to eliminate the comma.
    Or provide a fair warning, or make sure the value is correctly calculated serverside aswell.

    Just a heads up ofcourse. It works fine until you stumble on that, or the user thinks that the calculation is wrong or somesort – then the trouble starts of course.

    Additionally, why not work with the name attribute selector instead of a partial id match: $(“input[name=f04]”) ?
    Isn’t your “linetype” selector in the updateTotals function not wrong? In there you are selecting f05 through the name but with the id naming: input[name^=’f05_”+rownum+”‘]. Likely you are aware of retrieving the parent row and then select the appropriate child, but perhaps you decided against that because you don’t want traversal?

    Like

    1. Thanks Tom, I’m so used to Oracle’s handling of numbers that I’d forgotten to even consider how Javascript handles them.

      I’m looking at accounting.js as a possible solution. http://openexchangerates.github.io/accounting.js/

      accounting.formatNumber(10.1+9.2,2) results in 19.30

      The idea of using the name attribute selector is good – I think I’ll use it. When I get back to the office I’ll have to check the selector for the radio buttons, from memory I think their names were like f05_0001_0001 but I’m not sure – maybe that’s their ids. I’m not a javascript guru at all so I don’t know which way is better.

      Like

      1. It looks like it could take care of some of the problem, indeed. Though it does multiplication too, and in basic js it can go “wrong” there too, eg:
        > parseFloat(‘16.08’) *100
        1607.9999999999998

        However, since the plugin does seem to call toFixed after, it could just work out:
        > (parseFloat(‘16.08’) * 100).toFixed(2)
        “1608.00”

        So that seems okay so far. It may still be a good thing to keep this at the back of your head though :)

        As far as selectors go: if you can go with just the name selector: better. A partial match would inherently mean some form of checking – though the actual “performance loss” would be very trivial here.
        I understand why you’d substring the id and concatenate it to retrieve another element on the same row, since that would mean you wouldn’t have to traverse the dom to find the parent element and query it’s children – but once again, the performance difference would end up being trivial too. I have no testing to back any of this up though, but usually the js interaction in apex forms aren’t that “heavy”. Nevertheless, either you’re a perfectionist or you’re not ;)

        Like

  2. Thanks Tom! Certainly I prefer the simpler selector syntax, especially as this code will only iterate over 2-3 rows in the usual case, up to about 100 rows in exceptional cases.

    Like

Comments are closed.