The most powerful tools for visualizing data in Oracle APEX is the Interactive Report. It provides a rich opportunity to search, filter, sort, and calculate totals. But there are a lot of problems that often arise in the development of business applications. In a few posts I want to offer a simple solution.

First consider a trivial matter — how to show result of aggregation on each page. When using the built-in aggregation, the results are displayed on the very last page. In real applications, the result of aggregation is the goal. If user need to «rewind» first 10-20 pages to see the result — the user flies into a rage.

The optimal solution is displaying the results of aggregation on each page, but IR does not provide such an opportunity. So, we’ll do it ourselves.

То же самое, но по-русски.

1. Let’s start with the fact that for calculate the aggregation we must know a query. It can be obtained with the wonderful package APEX_IR_PKG (there is package APEX_IR for version 4.2 or later).

But it is worth to note that in the implementation of this package, there is one small error that can result in very big trouble. Namely — the filter conditions are going wrong. To fix this , you need to collect cycle conditions after line 946 :

lv_searches := lv_searches||'AND ('||lv_search || ') ';

add the code:

lv_search := '';

You can download correctly version of package here.

2. Next, create «on-Demand» process to calculate aggregation.

declare
-- "null table" (needs for get_ir_sql())
l_nullTable DBMS_SQL.VARCHAR2_TABLE;

-- query
l_sql VARCHAR2(32767);

-- result of aggregation
l_result varchar2(32767);
begin
-- retrive a query
l_sql := apex_ir_pkg.get_ir_sql (
p_app_id => :APP_ID,
p_session_id => :APP_SESSION,
p_page_id => :APP_PAGE_ID,
p_report_id => NULL,
p_app_user => :APP_USER,
p_use_session_state => TRUE,
p_binds => l_nullTable,
p_binds_val => l_nullTable,
p_incl_filters => TRUE,
p_incl_order_by => FALSE
);

-- add aggregation
l_sql := 'select sum(SAL) from ('||l_sql||')';

-- calculate
execute immediate l_sql into l_result;

-- and return
sys.htp.p(l_result);
end;

3. At last, create «After Refresh» Dynamic Action on report with JavaScript code:

// create ajax request to process
var get = new htmldb_Get(null, $v('pFlowId'), 'APPLICATION_PROCESS=GET_SUMMARY', $v('pFlowStepId'));

// execute request
var gReturn = get.get();

// make additional table row
var pData = '<tr style="text-align:right;"><td colspan="5"><b>Summary:</b></td><td><b>'
+ gReturn + '</b></td><td colspan="3"></td></tr>';

// concate our row to IR table
$(".apexir_WORKSHEET_DATA tbody").append($(pData));

Well, that’s all: Sample 1.
You can create customizable Dynamic Action Plugin: Sample 2 (with plugin).

P.S.: Please excuse my poor english…