October 22, 2019 APEX, Oracle 2 Comments

Oracle APEX Interactive Report (IR) has rich APIs to tweak, customize or control the user interactions. One of the API package I have recently used is APEX_IR.

Purpose of this blog is to show you how you can re-use your IR run-time query and filters to build all custom reports.
So, let’s define a requirement – You have two regions in a page – First region with summary of Department wise salary total. Second region with all list of employees. Now, when I apply any filters to detail region (which will be IR) then, the data of summary region should also change.

  • Create region 1 – summary region (classic report) with following query

select 
  d.deptno,
  d.dname,
  sum(e.sal)
  from dept d, emp e
 where d.deptno = e.deptno
   and e.empno in(select c001 from apex_collections where collection_name = ‘P16_EMP’)
group by d.deptno, d.dname
order by d.deptno

  • Create region 2 – employee list region (IR) with following query
    • Region Source Query

select EMPNO,
       ENAME,
       JOB,
       MGR,
       HIREDATE,
       SAL,
       COMM,
       DEPTNO
  from EMP
    • Region Static ID = employees_p10
  • Create new dynamic action as below:
    • Name = After Refresh
    • Region = “Employee List” the IR region
    • Add True Action
      • Action = Execute PL/SQL Code
      • Set Fire on Page Load = YES
      • Code Source
declare
  l_sql VARCHAR2(32000) := q’!
select EMPNO
  from (#IR_QUERY#)
!’;
  
  l_col_name varchar2(50) := ‘P16_EMP’;
  l_ir_query varchar2(32000);
  l_report apex_ir.t_report;
  l_region_id number;
  l_report_id number;
  l_bind_col apex_application_global.vc_arr2;
  l_bind_val apex_application_global.vc_arr2;
begin
  if APEX_COLLECTION.COLLECTION_EXISTS(p_collection_name => l_col_name) then
    APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => l_col_name);
  end if;
  
  select region_id
    into l_region_id
    from apex_application_page_regions
   where application_id = :APP_ID
     and page_id = :APP_PAGE_ID
     and static_id = ’employees_p10′;

  l_report_id := apex_ir.get_last_viewed_report_id(
                   p_page_id => :APP_PAGE_ID,
                   p_region_id => l_region_id
                 );

  l_report := APEX_IR.GET_REPORT (
                  p_page_id => :APP_PAGE_ID,
                  p_region_id => l_region_id,
                  p_report_id => l_report_id);

  l_ir_query := l_report.sql_query;
  
  FOR i IN 1 .. l_report.binds.COUNT
  LOOP
    l_bind_col(i) := l_report.binds (i).NAME;
    l_bind_val(i) := l_report.binds (i).VALUE;
  END LOOP;
  
  l_sql := replace(l_sql, ‘#IR_QUERY#’, l_ir_query);
  
  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY_B(
    p_collection_name => l_col_name, 
    p_query => l_sql,
    p_names => l_bind_col,
    p_values => l_bind_val
  );

EXCEPTION WHEN OTHERS THEN
  null;
  //You may handle any error logic.
end;
    • Add true action
      • Type = Refresh region
      • Region = Summary Region 
      • Set Fire on Page Load = YES

Working Demo.

Hope this helps!

Regards,
Jaydip Bosamiya
jbosamiya@gmail.com

, , , , , ,
Written by Jaydip Bosamiya