Re-use your Interactive Report query using APEX_IR.GET_REPORT

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