Master Detail Report in APEX Interactive Report

Master Detail Report In APEX | No page load

Master Detail Report In APEX for standard pages and Dialog pages.

Preview

When we are working with Master Detail report pages in Oracle APEX, we need to submit the page to set the value of the row/column in one of the page Item. It works with Standard pages very well. But when you try it with Dialog pages then there is an issue of submitting the page may send you back to base page.

Better to start refreshing the Details report based on clicked row.

Create two Reports Dept and Emp with below query.

For DEPT

SELECT d.deptno,
        d.dname,
        d.loc,
        CASE
           WHEN COUNT (e.empno) > 0
           THEN
                 '<div style="background:lightcyan;cursor:pointer;" onclick="set_dept('
              || d.deptno
              || ');"><i class="fa fa-plus"></i></div>'
        END
           action
   FROM dept d LEFT JOIN emp e ON d.deptno = e.deptno
GROUP BY d.deptno, d.dname, d.loc;

  • For column “Action” Escape special characters No Preview

For EMP:

SELECT *
  FROM emp
 WHERE deptno = :p4_deptno

  • Create a Page item to hold the dept number.
  • Submit the page to Detail Report (EMP).
  • Define static ID for Report: emp Preview

Finally add JavaScript function to Function and Global Variable Declaration

 function set_dept(p_deptno) {
    $s('P4_DEPTNO', p_deptno);
    $('#emp').trigger('apexrefresh');
}


All Done save and run.

Download App