Oracle Apex: Dynamic Calculation in Classic Report

Question

I have an application where I have created the classic report based on SQL query

Select summary_id,  
APEX_ITEM.HIDDEN(1,summary_id)||  
APEX_ITEM.TEXT(2,attribute5) attribute5 
from summary;

Here summary_id and attribute 5 are number data type.

I have seven (7 rows) records, where 5th and 7th rows in the column are sub_total and total values.
I want to dynamically calculate the sub_total and total based on change of values in previous rows.
Row5th is total of 1st,2nd,3rd and 4th row and row7th is total of 1st,2nd,3rd,4th and 6th

Please let me know if need to Provide any other information to understand the problem.

Thank you.

Oracle apex 4.2 version.

are the subtotal rows always 5 & 7 or is there a value on the row that will identify these? can you show the summary table with data? — WhiteHat
I have created the workspace fro the references, Workspace: apex_82 Username: Test password: test123 — ApexDev

Vance Vance · Accepted Answer · 2015-12-08T01:37:57

Im assuming that you know how to get the name attribute of the textbox under the attribute5 column using Inspect element feature of your browser. It's something like "f01","f02" or maybe higher, just check it out. Another assumption(based on your given data) is that row 5 contains the total of rows 1 to 4 and row 7 contains the total of rows 5 and 6.

Once you got the name, paste this function on your page's "Function and Global Variable Declaration" property:

    function setTotals(itemname){
            var v_subtotal=0;
            var v_total=0;
            for(i=0;i<4;i++){
                v_subtotal = Number(v_subtotal) + Number(document.getElementsByName(itemname)[i].value);
            }
            v_total = Number(v_subtotal) + Number(document.getElementsByName(itemname)[5].value);
            document.getElementsByName(itemname)[4].value = v_subtotal;
            document.getElementsByName(itemname)[6].value = v_total;
        }

        function setOnChangeEvent(itemname){
            for(i=0;i<4;i++){
                document.getElementsByName(itemname)[i].setAttribute("onchange","setTotals(\"" + itemname + "\")");
            }
            $("[name=" + itemname + "]").eq(5).change(function(){
                $("[name=" + itemname + "]").eq(6).val(Number($("[name=" + itemname + "]").eq(4).val()) + Number($("[name=" + itemname + "]").eq(5).val()));
            });
            $("[name=" + itemname + "]").eq(4).attr("disabled","disabled");
            $("[name=" + itemname + "]").eq(6).attr("disabled","disabled");
        }

Then in the "Execute when Page Loads" property of your page, enter the following line of code:

  setTotals(itemname );
  setOnChangeEvent(itemname );

An example is

 setTotals("f02");
 setOnChangeEvent("f02");

Here's a brief explanation of the code:

The function "setTotals" does the following:

It sets the value of the subtotal box from the sum of the 1st to 4th textbox under the name you provided in the parameter which s "f02". It also sets the value of the Total box from the sum of the 6th textbox and the value computed for the subtotal textbox.

The function "setOnChangeEvent" does the following:

It sets the onchange event of boxes 1 to 4 to setTotals("f02") so if you're going to inspect one of the boxes mentioned above, it now looks like this:

<input onchange="setTotals('f02')" name="f02" size="20" maxlength="2000" value="5" type="text">

It also sets the change event of the 6th box.(What happens on its change event is self explanatory I think, just look at the code) The last part of this function disables the 4th and 7th textboxes.

Oracle Apex: Dynamic Calculation in Classic Report

1 Answers