Link to home
Start Free TrialLog in
Avatar of bahamaER
bahamaER

asked on

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.

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.
Avatar of Mike McCracken
Mike McCracken

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
Avatar of bahamaER

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Mike McCracken
Mike McCracken

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Forced accept.

Computer101
EE Admin