One design pattern I’ve hit many times over the years is the need to display master-detail related information in a report.
For example with the typical EMP, DEPT tables you might want to display a report of all Departments, together with the Employees in each department.
Let’s look at a quick way to achieve this.
Firstly lets create a standard report using the following query –
select * from dept d
this would produce a report like –
so, let’s say we want to include a list of all the employees for a given department, we can use the LISTAGG function.
It looks more complicated than it actually is, so lets jump ahead to a working example, we can adapt out query to
select d.deptno, d.dname, d.loc as location, (select listagg(e.ename, ',') within group (order by ename) from emp e where e.deptno = d.deptno) as employees from dept d
this new query produces the following report
that’s great but we can use HTML to format it a bit nicer.
Let’s change the query to wrap the item inside an <li> </li> container
select d.deptno, d.dname, d.loc as location, (select listagg('<li>' || e.ename || '</li>', ',') within group (order by ename) from emp e where e.deptno = d.deptno) as employees from dept d
Now we can use the HTML Expression setting in the EMPLOYEES column in the report to wrap a <ul> around the content
We also need to change “Escape special characters” to No, this gives the following output in the report
As you can see this is quite a simple technique, but gives you a lot of control over styling the individual detail elements – you could for example use span elements and style them as bullets, for example