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.

Project Planning

Hello

Based on three tables (Projects, Tasks and UserCalender) I would like
to work out the total amount of available resources (UserCalender
table contains a entry for each user for each day, day being 7.5
hours) and total required effort (sum of Tasks.EstimateLikley) split
over 12 months.

For example:

Jan:
Available Resources: (4 Users, 7.5 hours per day, 5 working days per
week, 23 Working days in Jan) = (23 x 4) = (92 * 7.5) = 690 Available
Hours
Required Resources:
Project Start Date: 1/1/2007
Project End Date: 1/6/2007
Total Required effort (Sum of Tasks.Hours for above project): 500
Hours Average over 6 months = 83.33 Hours per month, so in Jan I need
to deduct 83.33 from 600 = 516.67 Hours.

etc

How could I do this, I have tried several ways but finding it hard.

ThanksPP (paul@.bobbob.net) writes:

Quote:

Originally Posted by

Based on three tables (Projects, Tasks and UserCalender) I would like
to work out the total amount of available resources (UserCalender
table contains a entry for each user for each day, day being 7.5
hours) and total required effort (sum of Tasks.EstimateLikley) split
over 12 months.
>
For example:
>
>
Jan:
Available Resources: (4 Users, 7.5 hours per day, 5 working days per
week, 23 Working days in Jan) = (23 x 4) = (92 * 7.5) = 690 Available
Hours
Required Resources:
Project Start Date: 1/1/2007
Project End Date: 1/6/2007
Total Required effort (Sum of Tasks.Hours for above project): 500
Hours Average over 6 months = 83.33 Hours per month, so in Jan I need
to deduct 83.33 from 600 = 516.67 Hours.
>
etc
>
How could I do this, I have tried several ways but finding it hard.

Is this disguise for your real business problem? I mean, project-
planning tools are plentiful on the market, so I don't see why you
would write your own. Or is it a class assignment?

In any case, for this types of questions, it helps if you post:

o CREATE TABLE statements for your tables.
o INSERT statements with sample data.
o The desired result given the sample.

This helps to clarify ambiguities in your post, and it also makes it
easy to copy and past to develop a tested solution.

... although, if it's really a class assignment, you are better to
discuss the problem with your teacher. You are likely to learn more
that way.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx