var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Reporting SUM() Question
Hey, I've been trying to find this on the boards but couldn't find it:
I have a report where a section header has the following items:
[SumOfAmt1] [SumOfAmt2] sum([sumofamt1]-[sumofamt2])
When I preview the report, I only get the third column when the first two columns are populated... For example, if both columns were populated, it would appear as such:
1,500 1,200 300
However, if one of the two colums is blank, I get nada in the third column, as such:
1,500 (blank) (blank)
(blank) 1,200 (blank)
Any ideas on how I can fix this in the report?
Will something like the following do the trick?
[SumOfAmt1] [SumOfAmt2] sum(NZ([sumofamt1])-NZ([sumofamt2]))
or maybe something like:
[SumOfAmt1] [SumOfAmt2] sum(IIF(IsNull([sumofamt1]),0,[sumofamt1])-IIF(IsNull([sumofamt2]),0,[sumofamt2]))
Last edited by smacdonaldnc; 12-20-04 at 20:49.
"[SumOfAmt1] [SumOfAmt2] sum([sumofamt1]-[sumofamt2])"
is the word SUM nessecary there???? you can just leave it away i think:
"[SumOfAmt1], [SumOfAmt2], ([sumofamt1]-[sumofamt2]) as Difference"
But don't forget the ( )
Just to add,n case you did not know (though you may) any time you have a "Blank" or a Null value in any calculation the result will out as Null. Unless you account for the possibility.
My 2 Cents
Damn, beaten like a red-headed stepchild caught behind the couch with his cousin.
Originally Posted by Darasen
but yeah, the problem is x - null = null
That's because null is an unknown or non-existant value. If you take an unknown value and add/subtract anything from it, the result is still unknown, or null.
SMacdonaldNC, that nailed it! Oooh, sometimes I truly impress myself with my inability to look for the simple solutions...