nested iif statement not working correctly in ssrs

  • 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")))

  • Why not

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

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

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

  • 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

  • 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"

    )

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply