May 23, 2006 at 12:52 pm
I have a matrix that I need to subtotal. When I do so by just using:
=SUM(FIELDS!FIELD1.VALUE)/SUM(FIELDS!FIELD2.VALUE)
I receive the correct totals. However, if the result is 0, the report produces NaN. Then I tried
=IIF(Fields!Field1.Value >0 and Fields!Field2.Value>0, SUM(Fields!Field.Value) / SUM(Fields!Field2.Value),0)
This takes care of the NaN issue, but then my subtotal is incorrect.
Is there anyway to get the best of both worlds; 0 where needed and a correct subtotal?
May 23, 2006 at 4:52 pm
This is just a quick thought, but did you don't need "AND" in your IIF. And I think you left out the "1" in the line.
=IIF(Fields!Field2.Value = 0, 0, SUM(Fields!Field1.Value) / SUM(Fields!Field2.Value))
Another thought is to do the division in the query/sp.
DivValue = CASE WHEN ISNULL(Field2, 0) = 0 THEN 0 ELSE Field1 / Field2 END
----
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgMay 24, 2006 at 4:55 am
You do not only have to care for "0" but also for "NULL" (nothing). If you do so, the statement is a bit more complicated, but it should work:
= IIf(Sum(Fields!Field2.Value)=0 Or IsNothing(Sum(Fields!Field2.Value)), 0, (IIf(IsNothing(Sum(Fields!Field1.Value)), 0, Sum(Fields!Field1.Value))) / Sum(Fields!Field2.Value))
Hope it helps !
Markus
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply