Build a Dynamic Hierarchical Tree Report with multiple columns in Oracle APEX

 We have to implement many requirements in our software development life cycle. This tree report is one of them requirements. The main requirements is need tree report with multiple columns to represent data properly and perfectly. By default in Oracle APEX, we can't develop multi-columns tree report. That's why, this is my efforts to overcome the limitation of default tree of APEX. So, lets start the discussion step by step. For visual assistance, you can follow the process from here.

STEP-1: Fisrt create a blank page. Then crate a region. Set it's below properties like

        Name:     Custom Tree Report

        type:        'Dynamic Content'

        PL/SQL Code: 

                                    DECLARE

    l_clob  CLOB;

BEGIN

    l_clob  := '<table class="a-IRR-table">

                    <tr>

                        <td class="a-IRR-header u-tC">Name</td>

                        <td class="a-IRR-header u-tC">Emp No.</td>

                        <td class="a-IRR-header u-tC">Manager ID</td>

                        <td class="a-IRR-header u-tC">Direct Subordinates</td>

                    </tr>

                    <tbody id="myTableBody">';

    

    FOR i IN (

        SELECT empno

            , ename

            , mgr AS manager_id

            , (SELECT COUNT(*)

                FROM emp 

                WHERE mgr = e.empno

            ) AS subordinates

            , SUBSTR(rec_level, INSTR(rec_level, 'L', -1)+1) AS rec_level

        FROM emp e 

        WHERE rec_level = 'LEVEL1'

    ) LOOP

        l_clob  := l_clob||

                    '<tr id="tr-'||i.empno||'">

                        <td class="u-tL"><span id="'||i.empno||'" style="padding-left:'||i.rec_level * 20||'px;" class="expand-collapse fa fa-plus-square-o" onclick="showDependent(this)"></span>'||i.ename||'</td>

                        <td class="u-tC">'||i.empno||'</td>

                        <td class="u-tC">'||i.manager_id||'</td>

                        <td class="u-tC">'||i.subordinates||'</td>

                    </tr>'  ;

    END LOOP;

    l_clob  := l_clob||'</tbody>

                </table>';

    RETURN l_clob;

END;

[Note: Use you own data table and you must need to add a column on your table/view named "rec_level" and data will be like 'level1', level2' etc.]

STEP-2: Create a AJAX Callback process to prepare child records of the parent. Click on the process tab icon from the left navigation pane. Right click on the 'Ajax Callbacks' and then select 'Create Process'.

Set the below properties as like-

Name:     ADD_DEPENDENT

Type:     Execute Code

PL/SQL Code:

        apex_json.open_array;

FOR i IN (

    SELECT empno

        , ename

        , mgr AS manager_id

        , (SELECT COUNT(*)

            FROM emp 

            WHERE mgr = e.empno

        ) AS subordinates

        , SUBSTR(rec_level, INSTR(rec_level, 'L', -1)+1) AS rec_level

    FROM emp e 

    WHERE mgr = apex_application.g_x01

) LOOP

    apex_json.open_object;

    apex_json.write('EMPNO', i.empno);

    apex_json.write('ENAME', i.ename);

    apex_json.write('SUBORDINATE', i.subordinates);

    apex_json.write('MGR', i.manager_id);

    apex_json.write('LEVEL', i.rec_level);

    apex_json.close_object;

END LOOP;

apex_json.close_all;

STEP-3: Go back to the navigator tab and select the page. Now go to the page properties and scroll down to "Function and Global Variable Declaration". Open the code editor and paste the below code here-

function showDependent(empId) {

    let eId = empId.id, i;

    if ($('#'+eId).hasClass('fa-plus-square-o')) {

        $('#'+eId).removeClass('fa-plus-square-o');

        $('#'+eId).addClass('fa-minus-square-o');

        

        apex.server.process(

            'ADD_DEPENDENT'

            , {

                x01: eId

            }

            , {

                dataType: 'json',

                success: function(pData) {

                    let targetRow = document.getElementById('tr-'+eId);

                    for (i = 0; i < pData.length; i++) {

                        let tr = document.createElement('tr');

                        tr.setAttribute('id', 'tr-'+pData[i].EMPNO);

                        tr.setAttribute('data-parent', eId);

                        // create reprot column one by one

                        let eName = document.createElement('td');

                        eName.classList.add('u-tL');

                        if (parseInt(pData[i].SUBORDINATE) > 0) {

                            eName.innerHTML = '<span id="'+pData[i].EMPNO+'" style="padding-left:'+(pData[i].LEVEL*20)+'px;" class="expand-collapse fa fa-plus-square-o" onclick="showDependent(this)"></span>'+pData[i].ENAME;  

                        } else {

                            eName.innerHTML = '<span id="'+pData[i].EMPNO+'" style="padding-left:'+(pData[i].LEVEL*20)+'px;" class="expand-collapse fa fa-plus-square-0" onclick=""></span>'+pData[i].ENAME;   

                        }

                        //FOR empno column

                        let empNo = document.createElement('td');

                        empNo.classList.add('u-tC');

                        empNo.innerHTML = pData[i].EMPNO;

                        //For manager Id column

                        let mgr = document.createElement('td');

                        mgr.classList.add('u-tC');

                        mgr.innerHTML = pData[i].MGR;

                        //FOR DIRECT SUBRODINATES column

                        let dirSub = document.createElement('td');

                        dirSub.classList.add('u-tC');

                        dirSub.innerHTML = (parseInt(pData[i].SUBORDINATE) > 0)?pData[i].SUBORDINATE:'';

                    

                        //APPEND these column to the TR

                        tr.append(eName, empNo, mgr, dirSub);

                        //Add the row after the appropriate parent record

                        targetRow.after(tr);

                    }

                }

            }

        )

    } else {

        hideDependent(eId); //later will be declare

        $('#'+eId).removeClass('fa-minus-square-o');

        $('#'+eId).addClass('fa-plus-square-o');

    }

}

function hideDependent(eId) {

    let childRows = document.querySelectorAll("[data-parent='"+eId+"']");

    childRows.forEach(function(row) {

        let childId = row.id.replace('tr-',""); // Get the numeric emp number.

        hideDependent(childId); // recursively remove the child row.

        row.remove(); // Finally remove the childs

    });

}

That's it. Now save and run. Hope, you have done a great job. let's enjoy the fun