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;
// 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));
P.S.: Please excuse my poor english…