Calculating total when null values may be present instead of using running total

[tim_harper]

Using Crystal Reports 2016

I am being asked to get a total for all unpaid assets. Some assets, though, have a paid date because there was a partial payment made. Some assets have ancillary costs. A set of ancillary costs for an asset may have some items with a paid date and some items without a paid date. Because the items may have a paid date, I am having trouble because the only way I was able to get a total for those was using a running total.

I was able to get all of the correct values using a running total of the UnpaidAncAmt formula below. However, I can't filter out those assets that have a $0 or null value doing it that way because formulas with running totals don't appear in the list of filterable fields.

1) Is there a way to sum unpaid ancillary costs where some ancillary costs have a paid date without using a running total field?

UnpaidAncAmt formula:

If NOT isnull ( {AssetCost.DatePaid} )

Then 0

Else ( {AssetCost.Amount} )

Thanks!

[DellSC]

Try using a Sum of your UnpaidAncAmt formula instead of a running total. If you're doing the sum for a group, then it will appear in the Group Select Expert (as opposed to the Record Select Expert.)

-Dell

[tim_harper]

Hi Dell, Thank you for the quick response. I tried what you recommended but it didn't give me what we were looking for.

After digging more into this, we discovered that the base issue was that we were looking for ancillary costs and some assets had no records in the ancillary table. Because they had no record in the ancillary table, they were returning a null value. The solution was to take the original formula we had to sum all unpaid PO and Ancillary costs:

@AllUnpaid formula:

if isnull({@BasePaidDate}) then

({@UnpaidPO_ExtCarry} + {#Total_AncUnpaidAmts})

ELSE {#Total_AncUnpaidAmts}

And create a second formula that said:

@FINAL-AllUnpaid

IF isnull ({@AllUnpaid}) THEN 0

ELSE {@AllUnpaid}

Probably not the most elegant solution, but it gave the finance people what they wanted.