Running Total on a formula not working!

[MrBillSC]

We are converting from Crystal vers 8.5 to 9.0 and I am trying to create a running total on a formula. My formula is as follows:
//@AmtTax
If (Not IsNull({ESAINHSV.LINE_TYPE}) and
{ESAINHSV.LINE_TYPE} = "T") and
(Not IsNull({ESAINHSD.LINE_TYPE}) and
{ESAINHSD.LINE_TYPE} = "I") then
{ESAINHSV.AMT_PAID} else 0

The formula works correctly, but a running total (using "sum") on this formula yields zero even though the detail section shows there are amounts in the formula field.

Any help and suggestions would be appreciated.

MrBill

 

[IanWaterman]

Running totals fall over when they encounter a Null, Change your formula so that it always returns atleast zero. The else 0 will not work for a null.

probably something like

If IsNull({ESAINHSV.LINE_TYPE}) then 0 else
If (Not IsNull({ESAINHSV.LINE_TYPE}) and
{ESAINHSV.LINE_TYPE} = "T") and
(Not IsNull({ESAINHSD.LINE_TYPE}) and
{ESAINHSD.LINE_TYPE} = "I") then
{ESAINHSV.AMT_PAID} else 0

Ian

 

[MrBillSC]

Ian, thanks for responding.

I have started with a new detail section formula:
// @AmtSpent
If IsNull({ESAINHSV.ACCT_NO}) or
IsNull({ESAINHSD.LINE_TYPE}) or
IsNull({ESAINHSV.AMT_PAID}) then
0 else
If (IsNull({ESAINHSV.LINE_TYPE}) or
{ESAINHSV.LINE_TYPE} = "T") and
{ESAINHSD.LINE_TYPE} = "I" then
{ESAINHSV.AMT_PAID} else 0

I think this takes care of null values and the detail section show zeros or numeric values.

The report is grouped on PoNum, InvDocNo and InvLineNo and then sorted ascending on the above formula @AmtSpent.

My running total sums at change of group (InvLineNo) and resets at group InvDocNo. The detail section shows that the last record of each group InvLineNo has a value greater than zero, but the running total in group InvDocNo is zero.

I've never had problems with running totals before. What am I doing wrong?

MrBill

 

[MrBillSC]

I figured out my problem.
The running total option to evaluate "on change of group" evaluates at the first record. I had assumed it did the calculation on the last record of the group, but apparently it calcs on the first record of the group. I changed my sort option for the formula @AmtSpent to sort decending and everything worked.

MrBill