none
Attempted to divide by zero error RRS feed

  • Question

  • Hi,

    Does anyone has ever encounter setting formula in 'Expression', tho result is zero, but it reflect "#Error"

    Any suggestion if i can use ISNULL in 'Expression' to resolve this "#Error" message in SSBID?

    Tuesday, April 13, 2010 10:09 AM

Answers

  • =IIf(Fields!Test1.Value = 0, "N/A", Fields!Test2.Value / IIf(Fields!Test1.Value = 0, 1, Fields!Test1.Value))

    U can make use of this also


    Praxy
    • Marked as answer by ngs5 Friday, April 16, 2010 8:07 AM
    Tuesday, April 13, 2010 10:36 AM

All replies

  • You need to compare both for NULL and Zero.

    Try the below on. If Field1 is null or zero, it will show blank, otherwise it will divide

    =IIF(IsNothing(Fields!Field1.Value) = True,"",IIf(Fields!Field1.Value > 0 , Fields!Field2.Value/Fields!Field1.Value,""))

    Tuesday, April 13, 2010 10:16 AM
  • =IIf(Fields!Test1.Value = 0, "N/A", Fields!Test2.Value / IIf(Fields!Test1.Value = 0, 1, Fields!Test1.Value))

    U can make use of this also


    Praxy
    • Marked as answer by ngs5 Friday, April 16, 2010 8:07 AM
    Tuesday, April 13, 2010 10:36 AM
  • =iif(IsNothing(Fields!field2.value) or Fields!Field2.value=0, 0, fields!field1.value/fields2.value)

    in this case, field1 is divided by field2 and if field2 is null or zero , it will return 0 or division result.


    mark it as answer if it answered your question :)
    Tuesday, April 13, 2010 12:46 PM
  • Do not forget that the IIF function will execute everything it holds. So if you have a

    iif(IsNothing(Fields!field2.value) or Fields!Field2.value=0, 0, fields!field1.value/fields2.value)

    It will execute the fields!field1.value/Fields!field2.value regardless of the outcome of the function. The result of that is that it will execute the divide by zero and will always result in an error.

     

    What you want to do is return a 1 if the field is either NULL or contains zero.

    =Fields!Field1.Value/ IIF(Fields!Field2.Value=0,1,IIF(ISnothing(Fields!Field2.Value),1,Fields!field2.value) )

    Almost the same as what Prasanna made, but with the ISNothing check.

     

    Remarks

    Note    The expressions in the argument list can include function calls. 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 .
    http://msdn.microsoft.com/en-us/library/27ydhh0d%28VS.71%29.aspx
    Tuesday, April 13, 2010 1:21 PM
  • I think it does not error out.

    Tried this.

    Created a Data Set as follows

    SELECT 0 AS Val1 UNION 1 AS Val1

    Added a tablix with one column and assiged the data set

    Then added the expression like

    =IIF(Fields!Val1.Value <= 0,0,10/Fields!Val1.Value)

    As per you, it should have errored for 1st record

    But it display 2 rows with 0 & 10 as values

     Also tried hard coding 1/0 as

    =IIF(0>1,1/0,0)

    It does not error out. Prints 0

     

    Tuesday, April 13, 2010 1:27 PM
  • You Can use Custom code Something like below,

    Public Function Percentage(ByVal a as Double ,ByVal b as Double ) as Double
       Dim p as Double
       If b=0 Then
          p = 0
       Else p= a/b*100

       End If
       Return p
    End Function

     

    And Call it As =Code.Percentage(Fields!Field1.Value,Fields!Field2.Value )

     

    Tuesday, April 13, 2010 1:50 PM
  • hi Ferry,

     

    thanks for input but i always use the method i mentioned here and never had any problem. i will still double check it but at the same time, check at your end and clarify.

    thanks,

    p


    mark it as answer if it answered your question :)
    Tuesday, April 13, 2010 2:38 PM