Master Detail Report In APEX | No page load

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

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

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 

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