Calculating Margin Field

[kgreer]

Here is the problem I am in getting into.

I have a field that is a Expression field. I am calculating the margin. My first problem is that I was trying to divide by zero. So I thought I would use an IIF statement. When I do that I am still getting #Error as a result on the fields with a value of zero. Below is the formula I am using.

=FormatPercent((Sum(Fields!Sales.Value) - Sum(Fields!Cost.Value))/Sum(Fields!Sales.Value))

Sum(Fields!Sales.Value) -- this field is the one that has a value of zero in some cases. So on those instances the value that shows up is #Error.

If I take away the the divide by the Sum field the report doesn't show and #Error as a result.

I am looking for any help.

Thanks

 

[Sheco]

Did you try something like this:[tt]
iif(Sum(Fields!Sales.Value) = 0,
"n/a",
FormatPercent((Sum(Fields!Sales.Value) -
Sum(Fields!Cost.Value)) /
Sum(Fields!Sales.Value)
)
)
[/tt]

 

[kgreer]

Nope, still gives you an #Error.

I fixed the problem and this is what I did:


=FormatPercent( ( Sum(Fields!Sales.Value) - Sum(Fields!Cost.Value) )/ IIF(Sum(Fields!Sales.Value) = 0,1, Sum(Fields!Sales.Value)))

 

[Catadmin]

kgreer,

I think I've used ISNothing in the past, nested inside an IIF. However, I can't remember if the ISNothing calculates zeros or just works on NULLS. I know for a fact that Is NULL doesn't work and neither does ISNULL().





Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"