=Fields!Price.Value / Fields!Cost.ValueThis works fine unless a value in the Cost field is zero (and is not an integer or double), in which case you will get '#error' displayed in your report. This is because a divide by zero with a decimal field will throw an exception. This exception is caught by the report processing engine and #error is displayed. If you would rather have a string like "N/A" instead of #error, you might think about creating a Reporting Services expression using the IIf function:
=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / Fields!Cost.Value)But when you preview the report, you still see #error in your report. What gives?
If you take a look at the IIf function description in the Visual Basic documentation, you will see the following:
As part of preparing the argument list for the call to IIf, the Visual Basic compiler calls every function in every expression. This means that you cannot rely on a particular function not being called if the other argument is selected by Expression.This means that even through the value for cost is zero, the error will still be generated. So how do you work around this? You have to force the expression evaluation to avoid the division with a nested IIf:
=IIf(Fields!Cost.Value = 0, "N/A", Fields!Price.Value / IIf(Fields!Cost.Value = 0, 1, Fields!Cost.Value))The nested IIf is evaluated first so that the divide by zero can be avoided but is not used by the outer expression if it is zero.
As an interesting side note, the Arithemtic Operators topic in the Visual Basic documentation seems to say that a DivideByZeroException is thrown for dividing by anything but the Double data type. I have not found this to be the case. Try this simple application in VB:
Dim a, b As IntegerYou will get "Infinity", despite what the documentation says. Anyway, the use of the expression above in your report will ensure that you will get the desired results, no matter what data type you are using.
a = 1
b = 0
MsgBox (a / b)
