Totaling a formula field 1

[CBMac]

I have reviewed other posts and learned that the way to get a total to print based on formula fields is to add several "whileprintingrecords" formula fields - one to clear the variable in the header - one to accumulate the total in the detail - and one to assume the total value in the footer.

I have tried to establish such a setup using Crystal 8.5 in an inventory report which has a formula field which computes a LIFO value on each detail line which is often zero.

I have a header level formula which is :

whileprintingrecords;
numbervar LTX;
LTX = 0

And a detail line formula which reads

whileprintingrecords;
numbervar LTX;
LTX = LTX + LIFOVAL (the formula field)

and then the footer formula as:

whileprintingrecords;
numbervar LTX;
LTX=LTX

The problem I am running into with this is that the detail line formual insists on being a boolean - so, my detail line includes "true" and "false" only - never a value

and, the total level is always zero.

 

[lbass]

You should be using a colon before the = sign, as in:

//header:
whileprintingrecords;
numbervar LTX := 0;

//detail:
whileprintingrecords;
numbervar LTX;
LTX := LTX + LIFOVAL (the formula field)

//footer;
whileprintingrecords;
numbervar LTX;

But, note that you do not necessarily have to use variables just because you are summarizing a formula field--it depends on the contents of the formula. Please share the formula contents.

-LB

 

[CBMac]

I believe I do have the proper syntax - at least it does not generate an error.

The report shows by location - by item the year end qty and value and then shows transactions from within the year so that the LIFO (last in first out) value can be calculated. The formula field I want to total for each printed line is :

if {#YTD_QTY} < {INVENTORY_LIFO;1.EOY2006} then 0
else {#YTD Cost} - {INVENTORY_LIFO;1.Cost-chg} +
(({INVENTORY_LIFO;1.Qty-chg} - ({#YTD_QTY} - {INVENTORY_LIFO;1.EOY2006})) / {INVENTORY_LIFO;1.Qty-chg}) * {INVENTORY_LIFO;1.Cost-chg}

#YTD QTY is the running total of the transactions
{INVENTORY_LIFO;1.EOY2006} is the year end total
when the running total exceeds year end total, the value of this formula field is the prior value plus whatever portion of the current transaction is required to reach the exact year-end quantity - thus, the qty of current line minus (prior total less year end) divided by transaction qty (yielding percentage) times the cost.

This field is set to suppress when zero so it only prints when the formula is satisfied that the year end level has been reached, and then it shows the LIFO value.

I have a running total on the report for the year-end value which suppresses when duplicated (since that portion of the record repeats for each transaction) - this works OK. When adding a running total, the formula field for LIFO total is not available to be selected as a field to summarize. This is why I am trying the variable approach.

Confusing and frustrating ...

 

[lbass]

Yes, if the formula uses a running total you will need to use variables. So try it with the changes I suggested earlier.

-LB

 

[CBMac]

When I attempt this syntax :

whileprintingrecords;
numberVar LTY;
LTY := LTY + {@LIFO VAL}

I get an error saying that a number is required here - prompting at the LTY following the equal sign on the last line ... even changing it to a literal '10' the error stays.

 

[lbass]

The variable has to match the datatype of the formula, so if the formula is a:

currency: use currencyvar
string: stringvar
date: datevar
datetime: datetimevar
number : numbervar

The designated variable must be the same in all references (all three formulas) to the same variable name.

-LB