Looking for SSRS filter which is identical to the SQL "IN" clause

Question

I am attempting to set a conditional format for one of the fields in my SSRS report. Basic formatting which would change font color. This is my original expression in report builder:

=IIf(Fields!delta.Value <
            IIf("ex1"=Fields!ID_name.Value
                ,0.95
                    ,IIf("ex2"=Fields!ID_name.Value
                        ,0.988
                        ,0
                    )
                )
,"Red"
,"Black")

However, I have to do this for over a 100 different "ID_name" values which all share common "delta" values.

I was wondering if there is something similar to the IN clause in SQL that would allow me to paste all the "ID_name" values inside ('','','') format so it would be much easier? Something like this:

=IIf(Fields!delta.Value <
    IIF(Fields!ID_name.Value IN ('ex1','ex2','ex3','...')
        ,0.95
            ,IIF(Fields!ID_name.Value IN ('ex4','ex5','ex6','...')
                ,0.988
                ,0
                )
                )
,"Red"
,"Black")

I tried the IIf(InStr() method but my "ID_name" share similar names with others that have different delta values so a contains clause would not work.

Is this possible? Appreciate any and all input! Thanks!!

The ID names i.e. ex1, ex2, etc, are these accessible in SQL? Because you could approach this problem using a parameterised DataSet or a DataSet and Lookup function. — Jonathon Ogden

Alan Schofield Alan Schofield · Accepted Answer · 2017-08-14T19:42:12

You can do this but you'll need to wrap your text values in some kind of delimiter that won;t appear in your values to check like [ex1][ex2] etc..

You can use the contains method but it needs to be opposite way you would do a typical IN statement. Something like this..

IIF ("[ex1][ex2][ex3]".Contains("[" & Fields!ID_name.Value & "]"), 0.95, FalseBitHere)

Of course you can just use a comma or whatever you like as long as it wont; appear in your actual values to test.

Looking for SSRS filter which is identical to the SQL "IN" clause

1 Answers