Monthly Archives: February 2017

Using LISTAGG in APEX Reports

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