How to merge cells in classic report using JavaScript.

Overview

This document is about how to merge cells in classic report using javascript in oracle apex.

Technologies and Tools Used

The following technologies have been used to to merge cells in classic report using javascript.

Oracle Apex
Javascript

Use Case

Imagine you are developing a comprehensive HR dashboard in Oracle APEX that displays information from the classic “EMP” and “DEPT” tables. The stakeholders require a consolidated, hierarchical view of employees and departments, where specific cells need to be merged to emphasize organizational structure and key performance indicators.

Steps with Screenshot

This demo focuses on merging cells in a Classic Report that combines data from the “EMP” and “DEPT” tables, providing stakeholders with an intuitive and hierarchical view of employee and department information.

Steps to be followed,

Step 1: Create a new page with the two classic report region that combines data from the “EMP” and “DEPT” tables.

Region 1: Classic report without merge

Region 2: Classic report with merge

Code:

select

b.DNAME,a.ENAME,a.JOB,sum(Sal)salary

from emp a, dept b

where a.deptno=b.deptno

group by b.DNAME,a.ENAME,a.JOB

order by 1 asc ;

Step 2: Assign the static id “merge_col” for the region – Classic report with merge.

Step 3: Insert the provided JavaScript code into the ‘Execute when Page Loads’ section to enable cell merging functionality.

Code:

$(document).ready(function(){

$(‘#merge_col .t-Report-report’).each(function () {

//#merge_col is static id

// alert(1);

var dimension_cells = new Array();

var dimension_col = null;

var grand_tot = null;

var i = 1;

// First, scan first row of headers for the “Dimensions” column.

$(this).find(‘th’).each(function () {

// alert($(this).text());

if ($(this).text() == ‘Dname’) {

dimension_col = i;

// alert(dimension_col);

}

i++;

});

// first_instance holds the first instance of identical td

var first_instance = null;

var hid_col = null;

var hid_col2 = null;

var hid_col3 = null;

var hid_col8 = null;

// iterate through rows

$(this).find(‘tr’).each(function () {

//alert(‘find tr’);

// find the td of the correct column (determined by the dimension_col set above)

var dimension_td = $(this).find(‘td:nth-child(‘ + dimension_col + ‘)’);

var col1 = $(this).find(‘td:nth-child(‘ + 0 + ‘)’);

var col2 = $(this).find(‘td:nth-child(‘ + 1 + ‘)’);

//var col3 = $(this).find(‘td:nth-child(‘ + 3 + ‘)’);

//var col8 = $(this).find(‘td:nth-child(‘ + 8 + ‘)’);

// alert(col8.text());

//alert(tot);

if (first_instance == null) {

// must be the first row

first_instance = dimension_td;

hid_col = col1;

hid_col2 = col2;

// hid_col3 = col3;

// hid_col8 = col8.text();

//alert(hid_col8);

} else if (dimension_td.text() == first_instance.text()) {

//alert(dimension_td.text());

// the current td is identical to the previous

// remove the current td

dimension_td.remove();

col1.remove();

col2.remove();

//col3.remove();

dimension_td.text(“”);

v_first_instance=0;

if(isNaN(first_instance.attr(‘rowspan’)))

{

v_first_instance=1;

}

else

{

v_first_instance=first_instance.attr(‘rowspan’);

}

// increment the rowspan attribute of the first instance

first_instance.attr(‘rowspan’, parseInt(v_first_instance)+ 1);

//hid_col.attr(‘name’, ‘f01’);

//hid_col.attr(‘id’, ‘f01_1’);

hid_col.attr(‘rowspan’, parseInt(v_first_instance)+ 1);

hid_col2.attr(‘rowspan’, parseInt(v_first_instance)+ 1);

//hid_col3.attr(‘rowspan’, parseInt(v_first_instance)+ 1);

first_instance.attr(“style”,”border-bottom: 1px solid #e6e6e6;”)

hid_col.attr(“style”,”border-bottom: 1px solid #e6e6e6;”)

hid_col2.attr(“style”,”border-bottom: 1px solid #e6e6e6;”)

// hid_col3.attr(“style”,”border-bottom: 1px solid #e6e6e6;”)

} else {

// this cell is different from the last

first_instance = dimension_td;

hid_col = col1;

hid_col2 = col2;

//hid_col3 = col3;

// hid_col8 = col8.text();

//alert(hid_col8)

}

//alert(“dimension_td.text()=”+dimension_td.text()+”–“+first_instance.text());

});

Step 4 : Following that, create a ‘Dynamic Action’ set to trigger ‘After Refresh,’ and configure a ‘True Action’ to execute JavaScript code. This ensures the seamless preservation of cell merging when navigating to the next page after refreshing the Classic Report.

Note: Apply the same JavaScript code as used in step 3 in this context.

Step 5: To Make your Classic Report look better by adding the provided CSS script in the inline section.

Code:

.t-Report-cell, .t-Report-colHead {

border: 2px solid #e6e6e6;

}

.t-Report-colHead{background-color:cornflowerblue;color:white; font-weight: 900;}

Output:

Conclusion:

In summary, the ability to merge cells in Oracle APEX Classic Reports using JavaScript enhances visual presentation and user experience. Through dynamic actions and thoughtful design, developers can create more organized and visually appealing reports, making it easier for users to understand and analyze complex data. This approach not only improves aesthetics but also facilitates effective communication of key information, contributing to the overall success of Oracle APEX applications.

Aravind Vignesh