Dynamic Interactive Report Using Collection

Salah satu kelemahan Interactive Report (IR) adalah hanya menyediakan type source SQL tidak ada pilihan untuk PL/SQL. Sehingga kita tidak bisa membuat dynamic query seperti pada classic report.

Namun hal tersebut masih kita bisa cari solusinya dengan cara :

• Membuat function return table pipelined atau.
• Memakai bantuan query terhadap APEX collection.

Pada posting saya ini akan menjelaskan pada teknik kedua yaitu Memakai bantuan query terhadap APEX collection.

Skenario :

• Kita akan membuat IR thd semua tabel yang ada di  skema HR untuk contoh demo (EMP & OEHR_EMPLOYEES).
• Kita hanya membatasi max  kolom sejumlah 10.
• Skema akses apex memiliki system privilege : SELECT ANY DICTIONARY.

Logic & Process :

• Membuat sebuah fungsi  yg secara dinamis bisa me-return 10 nama kolom tabel input.

create or replace 
function get_report_headers 
(
  p_table     in varchar2 default 'EMP'
, p_separator in varchar2 default ','
, p_type      in varchar2 default 'HEADER'
, p_num       in number default 1
) return varchar2 as 
  v_temp_arr apex_application_global.vc_arr2;
  v_list varchar2(32767);
begin
 if p_type = 'HEADER' then
  SELECT column_name bulk collect
    into v_temp_arr
    FROM USER_TAB_COLUMNS
   WHERE owner = 'HR'
     and table_name = p_table
     and owner = 'HR'
     and data_type <> 'BLOB'
   order by column_id;
  v_list := apex_util.table_to_string(v_temp_arr,p_separator); 
  return v_list;
 else
  if p_num > 10 then
   v_list := null;
  else
   begin 
    SELECT initcap(REPLACE(column_name,'_',' ')) 
      into v_list
      FROM USER_TAB_COLUMNS
     WHERE owner = 'HR'
       and table_name = p_table
       and owner = 'HR'
       and column_id = p_num; 
    exception when others then v_list := null;   
   end;    
   end if;
  return v_list;   
  
 end if;
end get_report_headers;

• Buatlah sebuah page dengan 10 item dan sebuah IR.
• Pada before header buatlah sebuah process untuk membuat sebuah collection unik.

declare
 v_query VARCHAR2(32767):=null;
 v_collection varchar2(100) := nvl(:P20_COLL_NAME,'DYNAMIC_'||:SESSION_ID);
begin
 v_query := 'select '||
             get_report_headers(p_table => :P20_TABLE_NAME) ||
            ' from HR.' || nvl(:P20_TABLE_NAME,'EMP');

IF APEX_COLLECTION.COLLECTION_EXISTS (p_collection_name => v_collection) THEN                 
  APEX_COLLECTION.DELETE_COLLECTION(p_collection_name => v_collection);
  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name => v_collection
                                              ,p_query           => v_query );
else
  APEX_COLLECTION.CREATE_COLLECTION_FROM_QUERY(p_collection_name => v_collection
                                              ,p_query           => v_query );
end if;
end;

• Pada before header buatlah sebuah process mengisi 10 item yang telah dipersiapkan untuk nama header kolom.

:P20_C01 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 1);
:P20_C02 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 2);
:P20_C03 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 3);
:P20_C04 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 4);
:P20_C05 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 5);
:P20_C06 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 6);
:P20_C07 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 7);
:P20_C08 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 8);
:P20_C09 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 9);
:P20_C10 := get_report_headers(p_table => :P20_TABLE_NAME,p_type => 'COLUMN', p_num => 10);

• Buatlah IR :

select *
  from APEX_collections
 where collection_name = :P20_COLL_NAME;

• Buatlah heading column secara dinamis me-refer ke item serta kondisi thd nilai item reference (C001...C010).

• Buatlah semua kolom condition : never (selain C001...C010).

Demo :  Dynamic IR with collection