PowerBuilder – ‘Smart’ Crosstab Datawindow
I had a question from a co-worker regarding a functionality request from a user. Basically they wanted a datawindow to display information for vendors (basic stuff like name, amt 1, amt 2, amt 3, etc.) for a specific set of amount columns. Each line represents one vendor and the various amounts (if any) with a total for the line. Each column of the amounts would be totaled at the bottom. Classic ‘cross tab’ type stuff.
However, the users only wanted an amount column to display if it had a value in it, i.e., if no vendor had this amount, don’t put it on the datawindow. The other request was to have one specific column to not be totaled with the others for each vendor, i.e., the line total was the total of all the other amounts except a specified one. We have now left the ‘cross tab’ universe.
My Solution
As a proof of concept I created a Grid Style, external datawindow with five specified columns. Column one is the vendor name; the other four are numeric (I called them box1 through box4). I then added a series of data values (vendor name and numbers). I made sure that one column of amounts was blank ( or had a value of zero).
To handle the requirement of totaling all amounts except one specified, I created a computed field at the end of the others. The expression is:
if(isnull(box1[0]),0,box1[0]) + if(isnull(box2[0]),0,box2[0]) + if(isnull(box3[0]),0,box3[0])
In this case I want to exclude box4 from the total.
I also created a series of totals for each column (using the painter to generate the expression).
The final step was to create the code which will dynamically hide any of the amount columns which have no values (or just zero amounts). The ‘workhorse’ of this code is the use of a describe to evaluate an expression on each row to determine if it has a value other than zero.
Code from Open event of window
long ll_i, ll_max, ll_x
long ll_basearraycount
string ls_basevisibles[], ls_basewidths[], ls_makevisible[]
string ls_colname, ls_coltype, ls_msg
dw_1.retrieve( )
dw_1.setredraw( False)
// get list of columns in the order they are displayed which may not be the same as their column number order
ll_max = Integer(dw_1.Object.DataWindow.Column.Count) // determine number of columns
FOR ll_i = 1 TO ll_max
// only need the visible columns
IF dw_1.describe("#" + String(ll_i) + ".Visible") = '1' THEN
ll_basearraycount ++
ls_basevisibles[ll_basearraycount] = dw_1.describe( "#" + string(ll_i) + ".Name")
ls_basewidths[ll_basearraycount] = dw_1.describe( "#" + string(ll_i) + ".Width")
ls_coltype = dw_1.describe( "#" + string(ll_i) + '.ColType')
If (UPPER(ls_coltype) = 'NUMBER') THEN
// this gives a sum which means the column has data in it, ie, we want it visible
ls_msg = dw_1.describe( "evaluate ( 'sum(" + "#" + string(ll_i) + " for all)' , 1) " )
IF Dec(ls_msg) <> 0 and not IsNull(Dec(ls_msg)) THEN
ls_makevisible[ll_basearraycount] = 'Y'
END IF
ELSE
IF ls_basevisibles[ll_basearraycount] = 'vendor' THEN // the first column is always visible
ls_makevisible[ll_basearraycount] = 'Y'
END IF
END IF
END IF
NEXT
// Now make all columns invisible...
FOR ll_i = 1 TO ll_max
dw_1.Modify("#" + String(ll_i) + ".Visible='0'") // make it invisible
NEXT
// Now make the columns with values back visible
ll_max = UpperBound(ls_basevisibles)
ll_x = 10
FOR ll_i = 1 TO ll_max
IF ls_makevisible[ll_i] = 'Y' THEN
ls_msg = dw_1.Modify(ls_basevisibles[ll_i] + ".Visible='1'") // make it visible
ls_msg = dw_1.Modify(ls_basevisibles[ll_i] + ".X=" + string(ll_x)) // set the X position (starts at 10)
ll_x +=Long(ls_basewidths[ll_i]) // keep track of running X position
ls_msg = dw_1.Modify(ls_basevisibles[ll_i] + ".Width=" + ls_basewidths[ll_i]) // set the column width
END IF
NEXT
dw_1.setRedraw(true)
Sample code files below including export of objects (need PowerBuilder 11.5/12 or above to read pbl)
SmartCrosstab
