I've frequently encountered the need for nested reports in various projects. Often, the solution required a third-party plugin, leading to the idea being dropped altogether.
While Oracle APEX offers a native solution with Master-Detail reports, we can agree that it's not the most user-friendly or visually appealing approach.
Recently, I discovered a straightforward method to display nested data inline for a master record. Before diving into the solution using template directives or HTML expressions in report columns, let’s revisit the old, less attractive way of doing this in APEX.
Ugly Result Simple Way
The most basic solution to your problem is to leverage SQL by using the LISTAGG aggregation function. This allows you to concatenate values separated by commas, creating what we might call a 'nested column.'
Here’s a simple example of what can be achieved quickly and easily, though it's not the most visually appealing solution:
with e as (
select
deptno,
listagg (ename || ' (' || JOB || ')', ', ') emps
from emp
group by deptno
)
select
dname department,
emps employees
from dept d
join e on e.deptno = d.deptno
If we create a classic report using this query, APEX will deliver a functional report out of the box that will, hopefully, get the job done:
Simple Result Ugly Way
It quickly becomes clear that the result isn't ideal from a user interface perspective, if nothing else. My second attempt to address the issue involved adding some custom HTML tags directly into the query. Keep in mind that, at the time, HTML expressions weren't as widely available as they are today.
Here’s a potentially improved query:
with e as (
select
deptno,
'<ul>' ||
listagg ( '<li>' || ename || ' (' || JOB || ') </li>', '') ||
'</ul>' emps
from emp
group by deptno
)
select
dname department,
emps employees
from dept d
join e on e.deptno = d.deptno
This method requires lowering the security settings on the column to avoid escaping special characters. As a result, it's far from a recommended approach, especially in APEX versions that already support template directives. The outcome:
Improved Result Simple Way
With the introduction of HTML Expressions and Template Directives, this problem can now be solved in an elegant way. There's no longer a need to hardcode HTML in queries, which makes them difficult to read and maintain, nor is there a need to disable default security features.
For the example above, the Loop Clause is a perfect fit. We can revert to the query from the first example and simply create the unordered list in the HTML Expression attribute of that column, like this:
<ul>
{loop "," TAGS/}
<li>&APEX$ITEM.</li>
{endloop/}
</ul>
Alternatively, using the exact same technique, you can create a table within that column:
<table>
{loop "," EMPLOYEES/}
<tr><td>&APEX$ITEM.</td></tr>
{endloop/}
</table>
The result should be exactly the same as before but achieved in a native and secure way within Oracle APEX.
Improved Result Improved Way
Assuming you're still following along, you'll notice that the user interface isn't ideal in the previous examples.
In each case, I wanted to aggregate two columns (Name and Title) into the details column, but it wasn’t possible to format each value separately.
Here’s the solution I found, still using native APEX functionalities. In this approach, I concatenate the Employee Name and Title, separated by a :. The result can be interpreted as a Key-Value detailed column, formatted like this:
key1:value1,key2:value2,keyN:valueN
Here’s the query:
with e as (
select
deptno,
listagg (ename || ':' || JOB, ',') emps
from emp
group by deptno
)
select
dname department,
emps employees
from dept d
join e on e.deptno = d.deptno
Here's the trick: by using a combination of template directives with loops, case and with, we can split the record twice to apply different styling to each piece of information.
<dl>
{loop "," EMPLOYEES/}
<dt class="padding-sm">
{loop ":" APEX$ITEM/}
{case APEX$I/}
{when 1/}
&APEX$ITEM.
{when 2/}
{with/}
LABEL:=TITLE
VALUE:=&APEX$ITEM.
LABEL_DISPLAY:=N
STYLE:=t-Badge--subtle
SHAPE:=t-Badge--circle
{apply THEME$BADGE/}
{endcase/}
{endloop/}
</dt>
{endloop/}
<dl>
The result:
Wow! You might be wondering what just happened. Here’s a quick explanation:
According to the documentation, there are two substitution strings available in the Loop directive:
APEX$ITEM: Represents the value of the current item in the list.
APEX$I: Indicates the 1-based index of the current item in the list.
For more information about template directives, you can refer to the documentation here.
What isn’t explicitly mentioned in the documentation is that you can loop again and use the APEX$ITEM substitution in the second loop to re-split the value using a different separator, such as :.
By combining a case directive with the APEX$I substitution, you can differentiate between the "Key" (value at index 1) and the "Value" (value at index 2), allowing you to apply different styling.
In my case, I simply print the ENAME while applying a badge template to the TITLE column using the WITH clause.
Colorful Templating
If you weren’t aware of the APEX$I substitution, here’s another quick use for it. Although the syntax of template directives is quite limited, you can still leverage the Index substitution to apply a color class to each element in a visually appealing way. Note the u-color-&APEX$I. syntax in the STYLE clause.
<dl>
{loop "," EMPLOYEES/}
<dt class="padding-sm">
{with/}
LABEL:=EMPLOYEE
VALUE:=&APEX$ITEM.
LABEL_DISPLAY:=N
STYLE:=t-Badge--subtle u-color-&APEX$I.
SHAPE:=t-Badge--circle
{apply THEME$BADGE/}
</dt>
{endloop/}
<dl>
Here's a simplified example to illustrate the concept:
Enjoy Life!