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

Comments

  1. Replies
    1. Hello Devil Eyes, are you facing any specific issues when you follow the steps I have provided in blog? Just to understand so that I can improve the blog details.

      Delete

Post a Comment

My photo
Jaydip Bosamiya
I am Oracle APEX Consultant, Blogger and Integrator. All-rounder in building small, medium and enterprise applications. Extensive knowledge in various area of web-driven applications in Back-end (PL/SQL, SQL, Java), Front-end (Oracle APEX, HTML, JavaScript, CSS, jQuery, OracleJET, ReactJS), RESTful APIs, Third-party library integrations (Apex Office Print (AOP), Payment Gateways, Syncfusion, HighCharts) and APEX Plugins (HighChart, StarRating)

Popular posts from this blog

Convert Classic Report Card Template and Card List Template into Flip Card Animated Templates

jQuery Circular Progress Bar - Pie Chart

How to create your own customized nested report regions using jQuery