I need help on creating a custom aggregate in last row of Oracle APEX 21.2 IG

Question

I have a simple sql that I am trying to add a custom aggregate by taking the sum of two columns and dividing the values against each other. Here is a planned equation

round((sum(col1)/sum(col2))*100,2) = new_col_footer

Doing this in a query would be easy with a union all but will not work in this case due to if a end user starts filtering the report, that union all calculated row could not reflect or correctly or become filtered out from view. Here is a screen shot of what I am trying to do. Thank you in advance for any guidance.

What I am trying to show as ig is filtered

I tried applying some of the agg functions but it appears by default an interactive grid does not have a way to accomplish this by default.

Answer 1

1. Give IDs to your report(report_id) and columns(col_1, col_2 and col_3) first.
2. Then create a Dynamic Action for the report, using Event > After Refresh.
3. Then add in True action of Execute JavaScript Code and below codes

#If you are using Interactive Report;

    var col_1=0, col_2=0, col_3=0;
    $('#report_id .a-IRR-table tbody tr:last-child').each(function(){
        col_1 = $(this).find('td[headers="col_1"] .a-IRR-aggregate-value').text();
        col_1 = parseInt(col_1.replace(/,/g,''),10);
        
        col_2 = $(this).find('td[headers="col_2"] .a-IRR-aggregate-value').text();
        col_2 = parseInt(col_2.replace(/,/g,''),10);

        col_3 = (col_1/col_2)*100;
        $(this).find('td[headers="col_3"]').text(col_3);
    });

If you are using Classic Report, update the CSS class accordingly.