This document is to display listagg of IR column break report.
Technology and Tool Used :
- Oracle Apex
- Javascript
Use Case :
If a requirement arise from user to list all value in listagg manner for a control break interactive report then we can achieve the same using javascript.
Steps to be followed:
Step 1 : Create a interactive report in the page.
Sample query — select ename Ename,sal, (select dname from dept a where a.deptno = e.deptno)dept from emp e
Step 2 : Create control break and aggregation based on your requirement. The result should look like the below screenshot.
Step 3 : Paste the below mentioned javascript code in page (Execute when page loads).
//Set to the displayed header name
var headerName = ‘Ename’;
var headerNum; //ID for TH header object
// Find the header to listagg
$(‘a.a-IRR-headerLink’).each(function(){
var $this = $(this)
if($this.text() == headerName){
headerNum = $this.closest(‘th.a-IRR-header’).attr(‘id’);
}
});
$(‘.a-IRR-aggregate-value’).each(function(){
var $aggRowTr = $(this).first().closest(‘tr’);
var $prevRow = null;
var listaggHtml = ”;
// Loop over all the rows until next next column break
while (true){
$prevRow = !$prevRow ? $aggRowTr.prev(‘tr’) : $prevRow.prev(‘tr’);
if ($prevRow.children(‘th’).length > 0){
// If TH is detected it’s the end of the IR column break
break;
}
else{
listaggHtml = $prevRow.children(`td[headers*=”${headerNum}”]`).text() + ‘,’ + listaggHtml;
}
}//for
// Regex is to remove trailing comma
$aggRowTr.children(`td[headers*=”${headerNum}”]`).html(listaggHtml.replace(/,$/gi, ”));
}); // (‘.a-IRR-aggregate-value’).each
Step 4 : Now run the page and you will get the below result.
