Conditional formatting in SSRS based on presence in list


  • I'm trying to set a background color for an SSRS field based on the presence of the field's current value in a list containing ~40 items. I'd like to use the syntax =IIF(Fields!Pt_type.Value IN ('Inpatient','Outpatient','ER','OB'), "LightGrey","Transparent"), but that throws an error. The list is much longer, so I'd like to find a clean way to accomplish this.
    Monday, August 25, 2014 8:58 PM


  • One of the way that you can do, if you cannot modify the query on dataset, is creating a hidden multiple-values parameter and enter the values in there.

    For expression, you may use something like this: (using | as delimiter)

    =IIF(InStr("|"+Join(Parameters!ReportParameter1.Value,"|")+"|","|" + Fields!Pt_type.Value + "|")>0, "LightGrey","Transparent")

    This should work for ~40 items. But if you don't like the idea of entering items into parameters, another option is creating a report variable and stored the entire collection of items into a long text string.

    Hope this helps.
    ~ J.

    Tuesday, August 26, 2014 2:05 AM

All replies