Apex: Nested reports

Have you ever wondered how to display additional detail records together with the master records in a single report?

For example, in a recent project I had to display a list of orders. Together with the order details (who, when) I had to display the order items in the same row.

The result should look like this:


How can this be done?

The "trick" is to create a stored function which generates the HTML for the detail records so that they can be displayed with the master records.

Here is a step by step example based on the tables EMP and DEPT, what else ;).

1. Create the package and stored function to generate the html for all employees in a specific department:

CREATE OR REPLACE PACKAGE emp_pck
AS
/******************************************************************************
 NAME:       EMP_PCK
 PURPOSE:

 REVISIONS:
 Ver        Date        Author           Description
 ---------  ----------  ---------------  ------------------------------------
 1.0        16.03.2007             1. Created this package.
******************************************************************************/
 FUNCTION get_emps_inline_f (p_deptno NUMBER)
    RETURN VARCHAR2;
END emp_pck;
/


CREATE OR REPLACE PACKAGE BODY emp_pck
AS
 FUNCTION get_emps_inline_f (p_deptno NUMBER)
    RETURN VARCHAR2
 IS
    l_str   VARCHAR2 (32767);
    l_cnt   NUMBER           := 0;
 BEGIN
    l_str    := '<table class="inlineTable">';
    l_str    :=
          l_str
       || '<tr><th>No.</th><th>Name</th><th>Job</th><th>Salary</th></tr>';

    FOR cur IN (SELECT   empno, ename, job, sal
                    FROM emp
                   WHERE deptno = p_deptno
                ORDER BY ename)
    LOOP
       l_str    := l_str || '<tr>';
       l_str    :=
             l_str
          || '<td>'
          || cur.empno
          || '</td><td>'
          || cur.ename
          || '</td><td>'
          || cur.job
          || '</td><td>'
          || cur.sal
          || '</td>';
       l_str    := l_str || '</tr>';
       l_cnt    := l_cnt + 1;
    END LOOP;

    l_str    := l_str || '</table>';

    IF l_cnt = 0
    THEN
       RETURN '';
    ELSE
       RETURN l_str;
    END IF;
 END;
END emp_pck;
/

2. Create the styles in the page header, in order to format the inline table:

<style type="text/css">
<!--
.inlineTable{border:1px solid #cfe0f1;border-collapse:collapse;width:100%;}
.inlineTable th{color:#336699;border:1px solid #cfe0f1;padding:2px;}
.inlineTable td{border:1px solid #cfe0f1;border-left:none;border-top:none;padding:2px;}
-->
</style>

3. Create the query in a report region

SELECT dept.*, emp_pck.get_emps_inline_f (deptno) employees
FROM dept

Here you can see the final result:


The forum thread can be found here and the online demo is located here.

Regards,
~Dietmar.