I have 2 decimal data fields (Field1 and Field2) in a dataset, and use the expression with IIf function below to calculate the quotient. If the Field1 is not zero, the divide expression within the true part will be executed. Otherwise, just 0 will be returned.
=IIf(Fields!Field1.Value<>0, Fields!Field2.Value/Fields!Field1.Value, 0)
However, why I am still getting "#Error" when Field1 is zero?
The error message is as follows:
[rsRuntimeErrorInExpression] The Value expression for the textrun 'Textbox6.Paragraphs.TextRuns.TextRuns' contains an error:
Attempted to divide by zero
The cause of this error is that the IIf function always evaluates both the true part and the false part, even though it returns only one of them. This means both the true part and the false part need to be evaluated.
In order to resolve this issue, you need to use a nested IIf function to avoid the zero-divisor in any rate.
=IIf(Fields!Field1.Value=0, 0, Fields!Field 2.Value/IIf(Fields! Field 1.Value=0, 1, Fields! Field 1.Value))
After that, you will get the correct result whether Field1 is 0 or not.
You can also use custom code to prevent errors caused by dividing by zero.
Select click Report Properties on the Report menu. Click the Code tab and enter the following code in the Custom Code box:
Function Divide(Numerator as Decimal, Denominator as Decimal)
If Denominator = 0 Then
After that, set the expression to be:
Reporting Services 2005
Reporting Services 2008
Reporting Services 2008 R2