Cross-tab report: from list

[Kay C Lan]

For the solution where you want to cover blank data, this might help.
Basically if the statement that the data is in descending order is correct,
then the last line should contain the last year of the report, which means
you can calculate how many columns need to be created.

This solution basically creates a blank intermediate variable that is of
appropriate size (rows x columns) but is mostly blank. This uses the simple
fact that if a variable contains t number of items and you put something
into item z, then all the items in between are created as empty :-) It then
uses the original script to fill in the blanks. Lastly, anything that is
still blank is replaced with "NULL" (or whatever you want) for better
presentation.

This assumes you have a field "input" and a field "output". Watch for line
wraps, this is a 19 line script.

1)    ON mouseUp pMouseBtnNo
2)        constant january = 2, february = 3, march = 4, april = 5, may = 6,
june = 7, july = 8, august = 9, september = 10, october = 11, november = 12,
december = 13
3)        put field "input" into tListA
4)        set the itemdelimiter to "-"
5)        put  ((word -1 of the long date) - item 1 of line -1 of tListA +
2) into tColumnCount
6)        put "Month" & cr & "January" & cr & "February" & cr & "March" & cr
& "April" & cr & "May" & cr & "June" & cr & "July" & cr & "August" & cr &
"September" & cr & "October" & cr & "November" & cr & "December" into
tListB
7)        --creates every line with the right amount of items in it
8)        REPEAT FOR each line tLine in tListB
9)            put "NULL" into item tColumnCount of tLine
10)            put tLine & cr after tListC
11)        END repeat
12)        REPEAT FOR each line tLine IN tListA
13)            put item 3 of tLine into item ((word -1 of the long date) -
item 1 of tLine + 2) of line value(item 2 of tLine) of tListC
14)            put item 1 of tLine into item ((word -1 of the long date) -
item 1 of tLine + 2) of line 1 of tListC
15)        END repeat
16)        replace "-" WITH tab in tListC
17)        replace tab & tab WITH tab & "NULL" & tab in tListC
18)        put tListC into field "output"
19)    END mouseUp

HTH