Avoiding Divide by Zero in Report

Question

What is the experession to evaluate if the result of a computation would be a divide by zero error for a text box in report?

 

IIF(divide by zero, display nothing, else display computed result)...??

Answer 1

you should actually use it in your query,but...

 

=iif(value1 = 0 or value2=0,0, value1/value2)

 

where the values are the values your dividing by.

 

it's much easier to do it in sql though...

 

Answer 2

I'd recommend either doing it in your Stored Procedure or creating a function to handle it.  You don't want to be putting that expression in every sell in which you're doing division.

You can create a function like the one below and place it in the Report code block or put it in an assembly and reference the assembly in the report.

Public Function CalcAvg(dblNum As Double, dblDen As Double) as Object

 if (dblDen = nothing) or (dblDen = 0)
  CalcAvg = 0
 else
  CalcAvg = dblNum / dblDen
 end if

End Function

Answer 3

I have this code in my Code section

Code Snippet

Public Shared Function DivideXbyY(ByVal X As Decimal, ByVal Y As Decimal) As Decimal
    DivideXbyY = IIF(Y=0, 0, X / IIF(Y=0, 1, Y))
End Function

 

 then I call it in the Table/Matrix

Code Snippet

=Code.DivideXbyY(Fields!amt.Value,Fields!tix.Value)

 

Answer 4

Thanks guys, your responses are very helpful and much appreciated.

Answer 5

Just in case anyone is reading this now, I wanted to mention that the following statement does NOT work as intended:
=iif(value1 = 0 or value2=0,0, value1/value2)
This statement will bomb out if value2 = 0 with a DIV/0 error before even reaching the disjunction.  I assume this is because of the fact that lines of code are mathematically interpreted from right-to-left, instead of how we normally think about them.
The other suggestions for custom code (where the conditional is broken down into separate lines) are the appropriate solutions.