Search code examples
reporting-servicesnestediif

IIF statements with Multiple conditions in SSRS Expression

My expression is different than the ones I looked at already. MY code looks like this and I am having a hard time logically reading it. The code works but is part of another larger IIF statement that is used to filter out certain values on a given table column on the report.

I want to be able to "learn" how to read this type of IIF statement as it really does not make sense to me as written.

=IIF(Fields!DMDeptARCFlag.Value=1,
IIF(Fields!CloseFlag.Value=1,"",
IIF(Fields!ARCPaymentRequested.Value=0,"Invalid ARC Payment","")),"")

This expression is called "InvalidPaymentLoaded" and is used in the following expression

 =TRIM
(IIF(IsNothing(Reportitems!WTBTIClosure.Value)=0 AND Fields!SrMgmtStatus.Value = 1 AND Fields!DMDeptARCFlag.Value = 1, Reportitems!WTBTIClosure.Value, "") +
 IIF(IsNothing(Reportitems!ARCPaymentNotLoaded.Value)=0 AND Fields!DMDeptARCFlag.Value <> 1, Reportitems!ARCPaymentNotLoaded.Value, "") +
 IIF(IsNothing(Reportitems!InvalidARCClose.Value) =0, Reportitems!InvalidARCClose.Value , "") + 
 IIF(IsNothing(Reportitems!BCDPayementNotLoaded.Value)=0, Reportitems!BCDPayementNotLoaded.Value, "") + 
 IIF(IsNothing(Reportitems!InvalidPaymentLoaded.Value)=0, Reportitems!InvalidPaymentLoaded.Value, "") + 
 IIF(IsNothing(Reportitems!InvalidMISCClosure.Value)=0, Reportitems!InvalidMISCClosure.Value, "") + 
 IIF(IsNothing(Reportitems!InvalidMCFClose.Value) =0, Reportitems!InvalidMCFClose.Value , "") + 
 IIF(IsNothing(Reportitems!InvalidDeductionClose.Value)=0, Reportitems!InvalidDeductionClose.Value, ""))
Solution

  • Your IIF is not that complex if you break it down....

    Remember basic syntax for IIF is...

    IIF([This is true], [then this], [else this])

    So in you expression you have

    "If DMDeptARCFlag =1 then do some more work, else return ""

    =IIF(
    Fields!DMDeptARCFlag.Value=1,
    IIF(Fields!CloseFlag.Value=1,"", IIF(Fields!ARCPaymentRequested.Value=0,"Invalid ARC Payment",""))
   ,"")

    If DMDeptARCFlag does equal 1 then we look at the first nested IIF which reads

    If CloseFlag = 1 return "", else do some more work

    IIF(Fields!CloseFlag.Value=1,"", IIF(Fields!ARCPaymentRequested.Value=0,"Invalid ARC Payment",""))

    So if CloseFlag does not equal 1 then we look at the final nested IIF which simple reads

    If ARCPaymentRequested = 0 return "Invalid ARC Payment", if not return ""

    I think you could simplify the expression though as really you only ever return 1 of 2 values so if you work out all the conditions for returning one value you should be able to get rid of all those nested IIFs

    Something like this

    =IIF(
    Fields!DMDeptARCFlag.Value=1 AND Fields!CloseFlag.Value <> 1 AND Fields!ARCPaymentRequested.Value=0
    , "Invalid ARC Payment"
    , "")