I am currently using a crosstab in my group header to represent running totals. There is one row representing the sum of amounts (a database field), and columns representing codes and overall total of all sums. Graphic representation below. Is is possible to save the current field values (sums) of each code and use them in manipulating the calculation of the Total amount?
18 23 45 28 Total
sum x x x x xx
I have tried to enter formulas in the display string for the codes (example is below), and have not been able to capture the current value of each code column sum amount in one pass, it usually evaluates to 0, also I have had some issues with using IF-THEN-ELSE statements that has more than one action under THEN, such as changing the color of the cell then assiging the current value to a global variable. Would a switch or choose statement be more appropriate? I would like to use the global variable for calculations within the crosstab as well as within the group header. An example would be to subtract the current field value of 28 from the total amount and display that amout for the total by manipulating the display string or use the result outside of the crosstab.
GLOBAL NUMBERVAR pay1;
GLOBAL NUMBERVAR pay2;
if gridrowcolumnvalue("code") = 28 then
if gridrowcolumnvalue("code") = 23 then
I hope this does not sound complicated, I would rather be using running totals in the group header which would make this job a whole lot easier. But I have had issues with running total displaying in the group header.
My task is basically to show a starting amount, calculate running totals on each code, sometimes 2 codes are related for example 28 and 45, that would require me to group both codes together and get a sum for both items , then display a final total which represents the (beginning total amount + code18 - code23 + (code45 and code28) = final balance).
I am able to achieve this in a group footer with various formulas placed in the group header, detail and then displayed in the group footer. But I cannot take the footer amounts and display in the header, they usually evaluate to 0 since no data is available in the first pass at the header level. Hence I am now using CrossTabs which gives me a display but does not give me the flexibility of calculations.
Please help, this has been a long agonizing week learning about the differences in calculations for headers and footers, then finally discovering crosstabs and the limited ability.