Propogation of Null

In one of my tables I have to total a column but exclude some of the rows
from the total. I have the expression...
=SUM(Fields!IncludeInTotal.Value * Fields!AtoC.Value)
...where IncludeInTotal is integer value 1 or 0 and AtoC is numeric but
possibly null.
I was expecting a lot of blank cells on my output but instead got lots of
zeros. It seems that when AtoC is null the result of 1*AtoC isn't null but is
zero.
I've recoded to use...
=SUM(Iif( IsNothing(Fields!AtoC.Value) OrElse Fields!IncludeInTotal = 0,
Nothing, Fields!AtoC.Value )
I was very surprised by this behaviour (2005sp2) as I would've expected it
to behave like SQLServer and the null to propogate.
Is this by design? Is there a property against the report that sets this
behaviour as opposed to propogating nulls?
AndrewHello Andrew,
I got some confusion. Since you are using the SUM function, why you will
get a lot of zero?
Based on my test, I add a SUM function in the table footer and it get only
one result with the correct.
The table I use is like this:
AtoC IncludeIn ID
-- -- --
NULL 1 1
NULL 0 2
1 1 3
2 0 4
0 1 5
Could you please clarify it?
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks for the reply.
The SUM is just confusing things, so ignore it. At its simplest just set up
a table and in the detail row set a cell to be 1*Fields!AtoC.Value. You'll
see that when AtoC is null you get zero displayed.
I wondered if it was a formatting issue and null was being displayed as
zero, so I modified one of the cells to be 10+(1*Fields!AtoC.Value) and got
10 when the AtoC is null.
It looks to me as though multiplying by null gives zero, rather than null.
Andrew|||Hello Andrew,
Yes. The Expression will change the Type to numeric and that force the Null
value to 0.
You need to use the IIF to specify whether it is null.
Hope this helps.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.