Irregular tables

A couple of years ago I had an interesting problem.  There are a series of tables that my client uses in pension benefit calculations. These are two dimensional tables, with a member age, a continuant beneficiary age, and a factor.

Each year a new set of tables arrives from the actuary.  And each year those tables are loaded into the database so the calculation routines can use them.  The tables are effective dated, because there are frequent requirements to you earlier version.  As the process of disability retirement can literally take years, a back-dated retirement is a frequent occurrence.

Then a new set of tables arrived, and they were different.  They had wider age ranges.  That meant not only more rows, but more columns.  For the programs that needed to refer to the tables for calculations, that is no big deal.  For the inquiry pages that displayed them, it was another matter.

On a Peoplesoft grid the number of rows is dynamic, not so for columns.  Those are defined when you define the grid.  So the existing pages were not going to work.

Because the pages are all read-only, I didn’t have to worry about building a page with update capability.  So instead of a grid, I used an HTML Area.  And in the post-build peoplecode of each component, I coded something like this:

Declare Function tl_2dim_table PeopleCode SFC_DERIVED_HTM.HTMLAREA FieldFormula;

Local Record &tblrec;
Local string &title;
Local string &html;

&tblrec = CreateRecord(Record.PA_TL_SFLKUP01);
&title = “Option 2, Non-Spouse, Disability, All Safety Plans”;

tl_2dim_table(SFC_SFLKUP01_VW.EFFDT, &tblrec, &title, &html);

SFC_DERIVED_HTM.HTMLAREA = &html;

And this is the function itself.  Note that I had to make two passes though the table, once to collect the ages that would appear as heading across the top, and a second time to fill in the age down the side and the factor that goes in the intersection of the two ages.

Function tl_2dim_table(&effdt As date, &tblrec As Record, &title, &html)

Local SQL &headings;
Local SQL &detail;
Local number &age;
Local number &lastAge;
Local number &spouseAge;
Local number &factor;
Local number &cols;
Local string &strFact;
Local string &firstPart;
Local boolean &firstRow = True;
Local boolean &odd = True;

&headings = CreateSQL(“select distinct sfc_age from %table(:1) where effdt=%datein(:2) order by sfc_age”, &tblrec, &effdt);

&cols = 1;
While &headings.Fetch(&age)
&html = &html | “<td class=’PSLEVEL1GRIDCOLUMNHDR’ align=’center’>” | &age | “</td>”;
&cols = &cols + 1;
End-While;

&html = &html | “</tr>”;

&firstPart = “<table class=’PSLEVEL1GRID’>”;
&firstPart = &firstPart | “<tr><td class=’PSLEVEL1GRIDLABEL’ ‘2’ colspan='” | &cols | “‘ >” | &title | “</td></tr>”;

&cols = &cols – 1;
&firstPart = &firstPart | “<tr><td class=’PSLEVEL1GRIDLABEL’ colspan=’1′>Beneficiary</td><td class=’PSLEVEL1GRIDLABEL’ colspan='” | &cols | “‘ align=’left’>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Retiree Age</td></tr><tr><td class=’PSLEVEL1GRIDLABEL’ align=’center’>Age</td>”;

&html = &firstPart | &html;

&detail = CreateSQL(“select SFC_AGE, SFC_SPOUSE_AGE, SFC_OPTION_FACTOR from %table(:1) where effdt=%datein(:2) order by SFC_SPOUSE_AGE, SFC_AGE”, &tblrec, &effdt);

&lastAge = 0;
While &detail.Fetch(&age, &spouseAge, &factor)
If (&lastAge <> &spouseAge) Then
&lastAge = &spouseAge;
If (&firstRow = False) Then /* End the previous row */
&html = &html | “</tr>”;
If &odd = True Then
&odd = False;
Else
&odd = True;
End-If;

End-If;
&html = &html | “<tr><td class=’PSLEVEL1GRIDCOLUMNHDR’ align=’center’>” | &spouseAge | “</td>”;
End-If;
&strFact = NumberToString(“%#05.4t”, &factor);
If &odd = True Then
&html = &html | “<td class=’PSLEVEL1GRIDODDROW’>” | &strFact | “</td>”;
Else
&html = &html | “<td class=’PSLEVEL1GRIDEVENROW’>” | &strFact | “</td>”;
End-If;

&firstRow = False;
End-While;

&html = &html | “</tr>”;

&html = &html | “</table>”;

End-Function;

 

Not a masterpiece by any stretch of the imagination, but it was useful.  It also gave me the opportunity to introduce a little bit of HTML to my co-workers.  That came in handy later when we turned the internally developed user manual into online help pages.  They understood the ‘boxes within boxes’ concept of HTML by then.