none
SSRS IIF Syntax

    Question

  • Hello,

    New to SSRS and struggling with what seems to be a very simple statement:

    =IIf(Fields!col1.Value>0,(Fields!col2.Value/Fields!col1.value),0)

    So, if col1 is greater than zero then execute the divide.  Otherwise, just print 0.

    I am getting "division by zero" errors which I would think the IIf statement would take care of and not even run that statement.

    All columns returned are numeric.

    Thanks.


    Tuesday, March 24, 2009 2:21 AM

Answers

  • Hi,

     

    You can also use custom code to prevent errors caused by dividing by zero.

     

    Click the Layout tab, and then 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 Double, Denominator as Double)

    If Denominator = 0 Then

    Return 0

    Else

    Return Numerator/Denominator

    End If

    End Function

     

    After that, set the expression to be:

    =Code.Divide(Fields!col2.Value, Fields!col1.value)

     

    Please let me know if this helps.

    Raymond

    • Marked as answer by Heidebrink Thursday, March 26, 2009 10:33 AM
    Wednesday, March 25, 2009 1:49 AM
    Moderator

All replies

  • Hi,

    The statement above u mentioned should work.There is no flaw in that.


    try this,

    IIf((Fields!YTD.Value) >0,Round(Fields!YTD.Value/Fields!YTD_LY.Value),0)


    Thanks
    Chilambu
    Tuesday, March 24, 2009 5:02 AM
  • Try this,

    IIf(VAL(Fields!col1.Value) > 0,  VAL(Fields!col2.Value)/VAL(Fields!col1.value), 0)
    Rajesh Jonnalagadda http://www.ggktech.com
    Tuesday, March 24, 2009 5:36 AM
    Answerer
  • IIF is a function call which evaluates all arguments before it executes. It means that you'll get "division by zero" in your expression in this part - (Fields!col2.Value/Fields!col1.value)

    In general you should use a pattern like this to avoid division by zero:
    =iif(B=0, 0, A / iif(B=0, 1, B))

    In your case: 
    =IIf(Fields!col1.Value>0, (Fields!col2.Value/IIF(Fields!col1.value=0, 1, Fields!col1.value)), 0)

    Tuesday, March 24, 2009 4:53 PM
    Answerer
  • Hi,

     

    You can also use custom code to prevent errors caused by dividing by zero.

     

    Click the Layout tab, and then 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 Double, Denominator as Double)

    If Denominator = 0 Then

    Return 0

    Else

    Return Numerator/Denominator

    End If

    End Function

     

    After that, set the expression to be:

    =Code.Divide(Fields!col2.Value, Fields!col1.value)

     

    Please let me know if this helps.

    Raymond

    • Marked as answer by Heidebrink Thursday, March 26, 2009 10:33 AM
    Wednesday, March 25, 2009 1:49 AM
    Moderator
  •  I like this solution, thanks.  The others were very helpful as well.
    Thursday, March 26, 2009 10:34 AM