Sum function needs to handle nulls

[rjoubert]

The query for my report passes back two numeric fields that are used in a grand total in my report footer. In most cases, I am getting back 3 records, and I get the sum of each in my report footer, then add those two sums together to get my grand total (SUM({FieldA}) + SUM({FieldB})). The problem occurs when one of those fields has a NULL for all 3 records. Nothing shows up in my grand total field. I tried using an IIF and checking for IsNull, but that didn't seem to work (IIF(IsNULL(SUM({FieldB})), 0, SUM({FieldB}))). Any suggestions? Thanks in advance!

 

[IanWaterman]

Couple of options but quickest is

replace FieldA and FieldB with a formula

@FieldA

If isnull(fieldA) then 0 else FieldA

Repeat for FieldB

Use these in your GT Sum formula

Ian

 

[rjoubert]

Thanks for the quick reply. I tried your suggestion, but this report has multiple detail sections, some of which are suppressed depending on other values. I wound up modifying the grand total formula, as shown below, and that seems to work.

if isnull(Sum ({FieldB}))
then Sum ({FieldA})
else
Sum ({FieldA}) + Sum ({FieldB})

 

[IanWaterman]

I thought it was possible for FieldA to be null too. if that is case then you should modify to

if isnull(Sum ({FieldB}))
then Sum ({FieldA})
else
if isnull(Sum ({FieldA}))
then Sum ({FieldB})
else
Sum ({FieldA}) + Sum ({FieldB})

Ian

 

[pmax9999]

And just in case both values could be null:

If	isnull(Sum({FieldB})) and
	isnull(Sum({FieldB}))
Then 	0
Else
If	isnull(Sum({FieldB}))
Then 	Sum({FieldA})
Else
If 	isnull(Sum({FieldA})) 
Then 	Sum({FieldB})
Else 	Sum({FieldA}) + Sum({FieldB})

Cheers
Pete

 

[CoSpringsGuy]

or .. in your (SUM({FieldA}) + SUM({FieldB})) formula.. at the dropdown box select default value for NULL

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[Madawc]

Simpler to have the same logic at detail-line level:

If	isnull({FieldA}) 
Then 	0
Else {FieldA}

If isnull({FieldB}) 
Then 	0
Else {FieldB}

@TrueA + @TrueB

Then use a summary total on @TrueTwo. If you're not already familiar with Crystal's automated totals, see FAQ767-6524.






Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP