Running Total of a Formula Field

[Southerndiva]

Hi guys and gals!!

I need help fast.

The Scenario:
I was asked tro create an aged inventory report, aged on last purchase date, for my client. Aging bands were 0-90, 91-180 181-270, 270-360 and over 360.

The method used.

I extracted the records, and grouped on the item code. The detail section was suppressed.
In the GF, I created buckets -as Formula fields - AB0, AB1 etc. The bucket into which the item fell depended on the last purchase date(formula field - maximum(tblname.datepurchased).

@ab0 a boolean formulae was used to determine the bucket.
if the last purchase fell between currentdate-90 to currentdate ie @abo,then the Quantity on hand went into AB0(I.E Aged Bracket 0). (This is the second formulae -@QtyonHandBucket0.
@qtyonhandbucket0
if @ab0 then totext({tblname.qtyonhand}) else ""

I used similar formulae to fill the other buckets. This worked fine. These formulae were placed in the GF of the item code

However the client wants a total for each of the aging brackets in the Report Footer(I just knew he would have asked for this). To obtain this I would have to sum the formula field
@qtyonhandbucket0.

All attempts to do this using the whileprinting records formulae failed. This gave me only the value of the last record in aged bracket. As a last ditch, I tried evaluate after formula . That returned false

Any help?? I have told the client as a short term measure export to excel and total. But i find that quite inelegant. I know some out there knows how this is to be done.

SYNOPSIS: I need to create a running total of a formula field which is in the Group Footer.

Quite lenghty, but i wanted to give the whole picture. Thanks

 

[synapsevampire]

You have 2 solutions:

Don't use a totext in the formuals, in which case you can probably right click them and select Insert->Summary->Sum and get totals at whatever level you want. If the intent was to show nothing rather than 0 when there aren't any values, then right click the formula and select format field->Number->Suppress if zero.

Or use formulas to sum up the existing in the method you said it cannot be done.

Place something like the following formula in the Report header ( or group outside of the group your formula is in):

whileprintingrecords;
numbervar qtyonhandbucket0Tot := 0;

In the GF place a formula like:
Evaluateafter(@qtyonhandbucket0);
whileprintingrecords;
numbervar qtyonhandbucket0Tot := qtyonhandbucket0Tot+val((@qtyonhandbucket0));

In the report footer place something like:

whileprintingrecords;
numbervar qtyonhandbucket0Tot

Do this for each formula.

-k

 

[Southerndiva]

Thanks Synapse I will try these and let you know of the results.