Thursday 22 May 2008

Spread table in Apex



It's been a while since I posted my last blog. I'v been busy in a new job as Apex coach for a large dutch bank. The department I work for used to be an Oracle Designer and Forms shop, but last year they discovered the possibilities of Apex by a proof of concept application.
I have held several presentations for my collegues about Apex. They were impressed by the ease of application development, but they missed some features they use in Oracle Forms. One of them is spread tables. When you have a very large form, a spread table gives the the opportunity to scroll through a table both horizontally and vertically, while the heading and the row context stay in place.
Standard Apex provides you with normal HTML tables. There is no such functionality as spread tables possible, is it...?

When I was googling the internet on this subject I came across this site describing a technique to use spread tables using CSS and a bit of Javascript. It is possible to lock one or more columns to keep the context of the rows visible.
Great solution! The only drawback is that it only functions in IE and from version 5 upward, but the bank I work with has standardised on IE for browsing. As long as this feature is used within the company there is no problem.

But I want to use it in Apex. My idea is to create a new Report Template, that can be used to create spread table reports just like you create normal reports.

I have based my new Report Template on the Alternating colors template. In the Before Rows section a DIV container is started, and the table is assigned the ID spreadtable:

<table cellpadding="0" border="0" cellspacing="0" summary="">
#TOP_PAGINATION#<tr><td>
<div id="tbl-container">
<table id="spreadtable" cellpadding="0" border="0" cellspacing="0" summary="" class="t6standardalternatingrowcolors">


which is ended in the After Rows sec

</table>
</div>
</td>
</tr>


The format of the tablecells is controlled by the CSS. The class locked is assigned to table cells to be locked. The locking of the column headers to the top border of the div is done by :

#table-container th.locked { top: expression(document.getElementById("tbl-container").scrollTop);
position : relative; }


As you see the top position of the header cells is determined by a Javascript expression, which can also contain a custom Javascript function. The expression keyword in this syntax is only evaluated by Internet Explorer, it will not work in other browsers.
The column cells that serve as row context are locked to the left side of the container with the folowing CSS.

#table-container td.locked { left: expression(document.getElementById("tbl-container").scrollLeft);
position : relative; }

The full CSS can be found here
The fact that it is based on an alternating colors table makes the CSS more complicated. Apart from this to obtain a clear separation between scrollable and locked columns I needed to create three new classes xxx_last, having a grey right border.

Next we need some Javascript to assign width and height to restrain the table container and to actually assign the class locked to the table cells we want to lock. For a start we put this code in the pageheader.


function lockColumns(regionDivID, width, height, noOfColumns)
{ // set DIV properties
div = scrollDiv(regionDivID);
div.style.width = width;
div.style.height = height;
div.style.overflow = "auto";
// look for table
var tables = div.getElementsByTagName('TABLE');
var table = tables[0];
var tableRows = table.getElementsByTagName('TR'); //collection of rows
var className = 'locked';
for (i = 0; i < tableRows.length; i++) // loop along the table's rows
{ var tableRow = tableRows.item(i);
for (j=0; j < noOfColumns; j++)
{ if (j == noOfColumns - 1 ) { className = className + "_last"; }
tableRow.cells[j].className = className; // last column get grey right border
}
if (className == 'locked_last')
{ className = 'lockedalt'; }
else
{ className = 'locked'; }
}
}


We call this funtion in a HTML region that we put behind the reports region so that we are sure that the region has rendered already.


lockColumns ( "ST_REGION", "400px", "250px", 2);


With this call we restrain the report in region with ID ST_REGION to a size of 400x250 px. A row context of 2 columns remains locked in position.

The result is shown on this page - remember it only functions with IE 5 and up. In this page support is added for Ajax pagination. In the Reports template the pagination links are supplied with a call to the lockCols procedure:


<a href="#LINK# lockCols(getRegion('#REGION_ID#'));" class="t2pagination">#PAGINATION_NEXT#<img src="#IMAGE_PREFIX#themes/theme_2/paginate_next.gif" alt="Next"></a>



I am still working on adding this call to the sort-links in the column headers.

Happy Apexing!