Crosstab Calculations or GH Running Totals

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
pay1:=TONUMBER(CURRENTFIELDVALUE);
TOTEXT(pay1);
if gridrowcolumnvalue("code") = 23 then
pay2:=TONUMBER(CURRENTFIELDVALUE);
TOTEXT(pay2);

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.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
YOur If-Then-Else with multiple lines is easy.  Crystal uses ( ) to work as a statement block

If A = B then
(
SetColor
SetFont
)
Else
(
SetColor
SetFont
);

Running total won't work because they are evaluated during the printing pass of the report which means they are calculated after the group header is built. That is the reason you have trouble using them.

You could use a subreport to get the values then have them in the appropriate header.

mlmcc
0
Author Commented:
Thankyou, but I was wondering if there is anyway to perform calculations inside a crosstab, on a current field value that is based on the column value?
0
Commented:
You can use the special variable (i believe it is) CurrentValue to get the value of the current cell.

mlmcc
0

Experts Exchange Solution brought to you by