locked
Having trouble with SSRS multiple conditions RRS feed

  • Question

  • I want to check to see if two fields are null or zero and if they are return zero else do a calculation.  Here is what I have and it just doesn't work.  I am not sure how to fix it.

    =IIF(ISNOTHING(Fields!LoanAmount.Value),0, IIF(ISNOTHING(Fields!OrginationFee.value),0,IIF(Fields!LoanAmount.Value = 0,0,
    IIF(Fields!OrginationFee.Value = 0,0,Fields!OrginationFee.Value / Fields!LoanAmount * 100))

    Here is the original Crystal Reports code:

    if isnull({view1.LoanAmount}) or isnull({view1.OrginationFee}) or
        {view1.LoanAmount} = 0 or {view1.OrginationFee} = 0
        then 0
    else ({view1.OrginationFee}/tonumber({view1.LoanAmount}) * 100)

    Friday, July 21, 2017 5:48 PM

Answers

  • Hi GaryAgoura_1,

    Based on my test, your issue might be related to that the IIF condition in SSRS evaluates both paths(true condition and false condition), If the data you are trying to calculate is anything other than a "Int", or is “Null”, you end up seeing #Error. 

    You can follow the next steps:

    Using custom code in Code pf Report Properties

    Public Function Divider (ByVal Dividend As Double, ByVal Divisor As Double)
    
    If IsNothing(Divisor) Or Divisor = 0
    
      Return 0
    
    Else
    
      Return Dividend/Divisor
    
    End If
    
    End Function

    Then pass expression in Tablix like below

    =IIF(
    
     ISNOTHING(Fields!LoanAmount.Value) OR ISNOTHING(Fields!OrginationFee.value) OR Fields!LoanAmount.Value = 0 OR Fields!OrginationFee.Value = 0,
    
     0,Code.Divider(Fields!OrginationFee.Value,Fields!LoanAmount.Value)*100)

    You will get the result below

    If you have any question, please feel free to ask.
    Best regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, July 24, 2017 6:55 AM

All replies

  • =IIF(
     ISNOTHING(Fields!LoanAmount.Value) OR ISNOTHING(Fields!OrginationFee.value) OR Fields!LoanAmount.Value = 0 OR Fields!OrginationFee.Value = 0,
     0,
     Fields!OrginationFee.Value / Fields!LoanAmount * 100
    )

    A Fan of SSIS, SSRS and SSAS

    Friday, July 21, 2017 7:47 PM
  • Thank's for your reply, but I put it in the Expression value and when I preview the report, it says #Error in the field, but as I look at the expression it seems that there is no error.

    Friday, July 21, 2017 9:14 PM
  • Do you know what the error is?

    A Fan of SSIS, SSRS and SSAS


    Friday, July 21, 2017 9:21 PM
  • No.  All it says is #Error
    Friday, July 21, 2017 10:50 PM
  • Hi GaryAgoura_1,

    Based on my test, your issue might be related to that the IIF condition in SSRS evaluates both paths(true condition and false condition), If the data you are trying to calculate is anything other than a "Int", or is “Null”, you end up seeing #Error. 

    You can follow the next steps:

    Using custom code in Code pf Report Properties

    Public Function Divider (ByVal Dividend As Double, ByVal Divisor As Double)
    
    If IsNothing(Divisor) Or Divisor = 0
    
      Return 0
    
    Else
    
      Return Dividend/Divisor
    
    End If
    
    End Function

    Then pass expression in Tablix like below

    =IIF(
    
     ISNOTHING(Fields!LoanAmount.Value) OR ISNOTHING(Fields!OrginationFee.value) OR Fields!LoanAmount.Value = 0 OR Fields!OrginationFee.Value = 0,
    
     0,Code.Divider(Fields!OrginationFee.Value,Fields!LoanAmount.Value)*100)

    You will get the result below

    If you have any question, please feel free to ask.
    Best regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Monday, July 24, 2017 6:55 AM
  • Hi Zoe,

    I just tried your solution and it now works.

    Thanks so much.

    Gary

    Tuesday, July 25, 2017 8:32 PM