Crystal Reports v10 - Summing fields, values may be null

[huddy33]

I have a report whose purpose is to show a summary of several fees by project. Because each fee may have more than one record attached to it, I have had to move all the fields into the G3 footer and run maximums on a running total to avoid the project showing up as many times as there are fees attached to it. Well, I had the report developed and working properly and then they threw another wrench in it. One field was supposed to be a maximum of a field named "ManagementFee." The formula for calculating this is:
If ({Meetings_ManagementFee.ManagementFeeCatID}=1)
then {Meetings_ManagementFee.Actual}
else $0.00

Well, now they want to add in several more ManagementFeeCatID's to the original ManagmentFee. So, I wrote a formula for each of the categories, exactly as above, only changing the catID #.

When I go to add them all together in a separate formula, the formula stops evaluating at the first null one it hits, thereby making the result incorrect in the running total, since it may be missing one or more fee category results.

I tried setting the report to convert the null values to default, but it doesn't work. I have no first hand experience with variables, but google is telling me I need to use them to set the formulas to $0 to get the maximum formula to evaluate all 13 categories. Is this correct and if so, how do I write and use the variable?

I'm sorry this is so long; any help is appreciated!

[BillBoeBaggins]

Can you eliminate the records with Null prior to being pulled in the report?
What about using a Nested IF statement to setting Actual to 0 if Null?

[huddy33]

Can you eliminate the records with Null prior to being pulled in the report?
What about using a Nested IF statement to setting Actual to 0 if Null?


I've tried converting the nulls to default in report options but no dice. What do you mean by nested if statements?

[BillBoeBaggins]

I've tried converting the nulls to default in report options but no dice. What do you mean by nested if statements?

then {Meetings_ManagementFee.Actual}
How about...
then if(isnull({Meetings_ManagementFee.Actual}),0,{Meetings_ManagementFee.Actual})

[huddy33]

then {Meetings_ManagementFee.Actual}
How about...
then if(isnull({Meetings_ManagementFee.Actual}),0,{Meetings_ManagementFee.Actual})

Thanks for the further ideas. I tried this:

If ({Meetings_ManagementFee.ManagementFeeCatID}=14)
then if(isnull({Meetings_ManagementFee.Actual},0,{Meetings_ManagementFee.Actual}))
else $0.00

and I get the "the keyword 'then' is missing" error.

[BillBoeBaggins]

I gave you code (whoops) in Inline IF format. You need to convert it to your syntax/flow.

[punkcrib]

I would suggest using COALESCE(value1, value2....valueN) to rid of NULL values.

For instance, if you are summing several parameters, some (or all) of which may be null, you could use this to replace null values with '0':

COALESCE(param1, 0) + COALESCE(param2, 0) + COALESCE(param3, 0)

If all param's are NULL then you will get 0 + 0 + 0 = 0. COALESCE() takes the 1st non-null value.