nested iif statement not working correctly in ssrs

• gissah

  SSCommitted

  Points: 1815

  More actions

  September 29, 2015 at 11:51 am

  #303562

  Can somebody please look the below code and tell me what I am doing wrong.

  The last portion of the code where it says "Silver " does not work. but the first 3 does work. Thanks

  =IIf(Fields!GrandTotalBillableRatio.Value >= 2.0, "Green"

  ,IIf(Fields!GrandTotalBillableRatio.Value >= 1.75 and Fields!GrandTotalBillableRatio.Value < 2.0, "Yellow",

  IIf(Fields!GrandTotalBillableRatio.Value < 1.75 and Fields!GrandTotalBillableRatio.Value >=0.00, "Red",

  "Silver")))

• pietlinden

  SSC Guru

  Points: 63506

  More actions

  September 29, 2015 at 8:23 pm

  #1830315

  Why not

  =IIf(Fields!GrandTotalBillableRatio.Value >= 2.0, "Green",

  , IIf(Fields!GrandTotalBillableRatio.Value >= 1.75, "Yellow",

  , IIf(Fields!GrandTotalBillableRatio.Value >= 0, "Red", "Silver")))

• gissah

  SSCommitted

  Points: 1815

  More actions

  September 30, 2015 at 8:12 am

  #1830458

  Thanks for your response but it did not work, it remains red when the condition is Nan or > 0.

  here is some few things i have tried.

  IIf(Fields!GrandTotalBillableRatio.Value >= 2.0, "Green",

  IIf(Fields!GrandTotalBillableRatio.Value >= 1.75, "Yellow",

  IIf(Fields!GrandTotalBillableRatio.Value >= 0, "Red", "Silver")))

  ----------------------------------------------------------------------------

  IIf(Fields!GrandTotalBillableRatio.Value >= 2, "Green",

  IIf(Fields!GrandTotalBillableRatio.Value >= 1.75, "Yellow",

  IIf(Fields!GrandTotalBillableRatio.Value > 0, "Red", "Silver")))

  some fields are blank and some turn silver if the condition is > 0

  --------------------------------------------------------------------

  IIf(ReportItems!Textbox38.Value >= 2, "Green",

  IIf(ReportItems!Textbox38.Value >= 1.75, "Yellow",

  IIf(ReportItems!Textbox38.Value >= 0, "Red",

  IIf(ReportItems!Textbox38.Value = "Nan", "Silver",

  "Silver"))))

  remain blank.

  Thanks

• Spiff

  SSCertifiable

  Points: 7826

  More actions

  October 2, 2015 at 2:57 am

  #1830984

  I image it converts the null values to 0 for the comparison and short circuits to Red. Try this, also Switch is much cleaner then nested IIFs:

  =Switch(

  IsNothing(Fields!GrandTotalBillableRatio.Value), "Silver",

  Fields!GrandTotalBillableRatio.Value >= 2.0,"Green",

  Fields!GrandTotalBillableRatio.Value >= 1.75, "Yellow",

  Fields!GrandTotalBillableRatio.Value >= 0, "Red",

  True, "Silver"

  )