Cascading Select list in IR Oracle APEX

By default the Interactive Reporting Region allows end users to customize reports. This interactive reporting region allows users to customize the layout of the data by choosing the columns they are interested in, applying filters, highlighting, and sorting. They can also define breaks, aggregations, different charts, and their own computations.

Upon the default functionality, the Apex developer can also customize the Interactive Report.
Cascading Select list in IR

Let’s imagine you have an IR report where you want to make it as editable Report with cascading select list. Let’s see how to do this in detail.

Follow the steps to create an Editable Interactive Report with cascading select list,

Step 1: In a Page, Create a Region and select report type as Interactive report

Step 2: Change the source type from Table/View to SQL Query

Step 3: Place the below code in the SQL query section,

SELECT apex_item.checkbox (1,
empno,
NULL,
NULL,
NULL,
‘f01_’ || ROWNUM
)
|| apex_item.hidden (2, ‘N’, p_item_id => ‘f02_’ || ROWNUM) chkbx,
empno,
apex_item.select_list_from_query
(10,
b.deptno,
‘select DNAME , DEPTNO from DEPT’,
‘onchange=”f_set_casc_sel_list_item(this,”f11_’
|| LPAD (ROWNUM, 4, ‘0’)
|| ”’)”‘,
‘YES’,
”,
‘- Select DNAME -‘,
‘f10_’ || LPAD (ROWNUM, 4, ‘0’),
NULL,
‘NO’
) dname,
apex_item.select_list_from_query
(11,
a.ENAME,
‘SELECT DISTINCT ENAME d, ‘
|| ‘ENAME r FROM EMP where DEPTNO = ‘
|| a.deptno,
”,
‘YES’,
”,
‘- Select User -‘,
‘f11_’ || LPAD (ROWNUM, 4, ‘0’),
NULL,
‘NO’
) ename,
apex_item.text (12, a.sal, p_item_id => ‘f12_’ || ROWNUM) sal
FROM emp a, dept b
WHERE a.deptno = b.deptno

Step 4 : Set Escape special characters as No for the following Columns CHKBX, DNAME, ENAME, SAL

Step 5 : Create a button named as SAVE,

Step 6 : Place the below javascript code in the
Page header ==> Function and Global Variable Declaration

function f_set_casc_sel_list_item(a, b) {
var p_deptno = $(a).val();
apex.server.process(‘GET_EMP_FR_DEPT’, {
x01: p_deptno
}, {
success: function(pData) { //alert(pData);
var ret = pData;
$(‘#’ + b).children(‘option:not(:first)’).remove();
if (ret) {
$(“#” + b).append(ret);
}
},
dataType: “text”
});
}

Step 7 : Create a Ajax call back application level process and enter the process name as GET_EMP_FR_DEPT and place the code given below,
Shared Components ==> Application Process

BEGIN
OWA_UTIL.mime_header (‘text/xml’, FALSE);
HTP.p (‘Cache-Control: no-cache’);
HTP.p (‘Pragma: no-cache’);
OWA_UTIL.http_header_close;

FOR rec IN (SELECT ename d, empno r
FROM emp
WHERE deptno = apex_application.g_x01)
LOOP
HTP.prn (” || rec.d || ”);
END LOOP;

END;

Step 8 : Create a Dynamic action on the target page
Event :- Change
Selection Type :- jQuery Selector
jQuery Selector :- [name=f01]
Action :- Execute Javascript Code
Code :-
var column_id = this.triggeringElement.id;
var rw_id = column_id.substr(4);
if ($(“#”+column_id).is(“:checked”)) {
$(‘#f02_’+rw_id).val(‘Y’);
} else {
$(‘#f02_’+rw_id).val(‘N’);
}




Step 9 : Create a page process on the target page,
Code:
DECLARE
lv_seq_id VARCHAR2 (4000);
cnt NUMBER := 0;
lv_query VARCHAR2 (32000);
BEGIN
FOR i IN 1 .. apex_application.g_f02.COUNT
LOOP
IF (apex_application.g_f02 (i) = ‘Y’)
THEN
BEGIN
UPDATE emp
SET ename = apex_application.g_f11 (i),
deptno = apex_application.g_f10 (i),
sal = apex_application.g_f12 (i)
WHERE empno = apex_application.g_f01 (i);
END;
END IF;
END LOOP;
END;