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

Shuttle Item on APEX

Pada posting kali ini saya akan membahas bagaimana memakai Shuttle Item pada APEX. yang meliputi bagaimana :

• Membuat default value berdasarkan isi tabel.
• Submit kedalam tabel.
• Melakukan filter nilai lihat detil di post ini : Enhanced Shuttle Item by Christoper Beck.
• Dynamic Action (DA) terhadap Shuttle Item untuk mengambil nilai.
• Dynamic Action (DA) terhadap Shuttle Item untuk melakukan query thd report EMP-DEPT.

Skenario kita adalah table EMP sebagai source pegawai dan EMP_ASSIGN sebagai data pegawai yang telah dipilih dalam Shuttle Ittem. Table EMP_ASSGIN hanya berisi satu kolom atau lebih yang menyimpan data EMPNO dan EMPNAME.

• Membuat default value berdasarkan isi tabel.

Pada bagian ini anda harus sudah membuat shuttle item (PXX_SELECTED_EMPLOYEES) dengan query sbb:

SELECT ENAME
     , EMPNO
  FROM EMP
ORDER by 1

Lalu pada bagian render before header buatlah process : GET_SELECTED:

declare
 l_emp_array wwv_flow_global.vc_arr2;
 l_list varchar2(32767);
begin
 -- get all selected emp into array
 select empno 
   bulk collect
  into l_emp_array
  from emp_assign;
 -- convert array to colon seperated string
  l_list := apex_util.table_to_string(l_emp_array,':');
 :PXX_SELECTED_EMPLOYEES := l_list;
end;

Proses selanjutnya adalah bagaimana cara melakukan submit pegawai yang dipilih kedalam sebuah tabel.

• Submit kedalam tabel.

Pada bagian submitting page buatlah sebuah proces : SUBMIT_ASSIGNMENT :

DECLARE
v_selected APEX_APPLICATION_GLOBAL.VC_ARR2;
BEGIN
 --delete existing assignment
 delete emp_assign;

 -- Convert the colon separated string into an array
 v_selected := APEX_UTIL.STRING_TO_TABLE(:PXX_SELECTED_EMPLOYEES);

 -- Loop over array and insert into table
 FOR i IN 1..v_selected.count LOOP
   insert into emp_assign
     (empno)
   values
     (v_selected(i));
END LOOP;
END;

Lalu proses selanjutnya adalah bagaimana memberi sebuah proses DA thd shuttle, dalam contoh ini kita akan mengambil nilai EMPNO yang dipilih kedalam sebuah text item : PXX_SELECTED_EMPNO.

• Dynamic Action (DA) terhadap Shuttle Item untuk mengambil nilai.

Pada proses ini buatlah DA thd PXX_SELECTED_EMPLOYEES beri nama :shuttle_changed dengan event : CHANGE. dengan kondisi is null, ini adalah bagian tricky khusus untuk tipe item shuttle, karena untum item biasa kita tidak pelu membuat kondisi biasanya :D.

Lalu pada TRUE action lakukan proses action Set Value dengan PL/SQL Expression : null.
Pada FALSE action Set Value dengan Javascript Expression :

$v("PXX_SELECTED_EMPLOYEES")

.
Keduanya terhadap Effected Elements : item : PXX_SELECTED_EMPNO.

Beres....

Keterangan :

• Demo hasil disini.