asked on

Crystal Reports total formula with groups?

I am having a problem with a formula that is adding three subtotals for each group. Some values are null.

formula is: if isnull({@OpenOrders}) then {@Sales} else Sum ({@OpenOrders}, {V_ORDER_HEADER_COMBO.ORDER_LOCATION}) + {@Sales}

@sales : {#InvoicedOrders} + {#ShippedOrders}
@OpenOrders: if {V_ORDER_LINES.QTY_SHIPPED} > 0 then 0 else if {V_ORDER_LINES.DATE_ORDER} = {?Date Range} then {V_ORDER_LINES.EXTENSION}

Any help would be appreciated,

Thomas

Mike McCracken

Can any of the fields in the Open Orders formula be NULL?
Try using this

if IsNull({V_ORDER_LINES.QTY_SHIPPED}) Then
    {V_ORDER_LINES.EXTENSION}
Else  if  {V_ORDER_LINES.QTY_SHIPPED} > 0 then 
    0 
else if IsNull({V_ORDER_LINES.DATE_ORDER}) then
    0
else if {V_ORDER_LINES.DATE_ORDER} = {?Date Range} then 
     {V_ORDER_LINES.EXTENSION}
Else
    0

Open in new window

mlmcc

Thomas Pitt

ASKER

Please see example of the output. Most of the lines total up on the far right, but two of them do not. Any guess why? This is the output using the original formulas.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

James0628

You're using a sum of OpenOrders for ORDER_LOCATION, and you're using running totals (in @sales), so I'm guessing that this formula is in the group footer for ORDER_LOCATION.

As mlmcc said, using IsNull ({@OpenOrders}) at that point will only check the last record in the group. By any chance, were you trying to get a sum of the non-null values? I've seen others try to use something like "if <some test> then Sum ({field})" to try to get a sum that only included certain records, but it doesn't work that way. The CR summary functions always include every record in the group (or report). Either way ...

Unless the fields used in OpenOrders will only be null in the group footer (or wherever you're using that IsNull test), then OpenOrders should almost certainly be changed, so that it will handle, or not see, nulls. If your version of CR has the option in the formula editor to control how that formula handles nulls, you could try setting that to replace them with default values. Then a null QTY_SHIPPED, for example, would be replaced by 0. Or, if you don't actually need to see the nulls in those formulas, or any other formulas in the report (with the possible exception of the record selection formula), then you could set the "Convert database null values to default" option under File > Report Options. Otherwise, you could change OpenOrders to include an IsNull test for every field that could be null. The exact form would depend on what null values mean to you. For example, does a null QTY_SHIPPED mean the same thing as 0?

James