How do I use an IN statement in SSRS Formula

Question

Is there an easy way to do an IN statement or something similar in a SSRS formula for an iff statement comparison without having to repeat a switch 16 times?

This is what I am looking to accomplish without having to write a statement or a separate OR ie(Fields!FieldName.Value = "01" OR Fields!FieldName.Value = "02"...etc) for each value:

=iif((Fields!FieldName.Value IN
("01","02","03","06","08","09","10","12","15","19"),"TypeA",
iff((Fields!ProdCode.Value IN ("04","07","11","13","14","16"),
"TypeB","TypeC")

Could you modify the SQL to join the to a table that converts the values to the string TypeA, TypeB, TypeC? — Mike, Jul 11 '16 at 22:24

score 2 · Accepted Answer · answered Jul 11 '16 at 22:33

2

Or this:

=switch( 
Instr( "," & "01,02,03,06,08,09,10,12,15,19" & ",", "," & Fields!FieldName.Value & ",") > 0  ,"TypeA",
Instr( "," & "04,07,11,13,14,16" & ",", "," & Fields!ProdCode.Value & ",") > 0  ,"TypeB",
True, "TypeC")

answered Jul 11 '16 at 22:33

saurus

84
6

alejandro zuleta · Answer 2 · 2016-07-11T22:34:36.983

0

Try this:

=Switch(
Array.IndexOf(Split("01,02,03,06,08,09,10,12,15,19",","), Fields!FieldName.Value)>-1,"TypeA",
Array.IndexOf(Split("04,07,11,13,14,16",","),Fields!FieldName.Value)>-1,"TypeB",
true,"TypeC"
)

Let me know if this helps.

edited Jul 11 '16 at 22:34

answered Jul 11 '16 at 22:26

alejandro zuleta

13,962
3
28
48

I was able to use the code from saurus, but thanks for your help! – Mattg07 Jul 12 '16 at 17:54
this code will also work, however there will be problems if 1 or more of the values in the string contain the delimiter (","). the solution by saurus will have similar but slightly different problems. – saurus Jul 13 '16 at 01:27

score 0 · Answer 3 · answered Jul 11 '16 at 22:32

I have mocked up an example of joining to a table that is generated in the sql. This is more efficient than doing it in the report

SELECT a,b,c,d, etc
, Lookup.FieldName, Lookup.TypeLabel
FROM Tables INNER JOIN 
(SELECT '01' AS FieldName, "TypeA" as TypeLabel
UNION ALL
SELECT '02' , "TypeA"
UNION ALL
SELECT '03' , "TypeA"
UNION ALL
SELECT '04' , "TypeB"
UNION ALL
...

SELECT '18' , "TypeC"
UNION ALL
SELECT '19' , "TypeC") LookUp 
ON Tables.FieldName = Lookup.FieldName

How do I use an IN statement in SSRS Formula

3 Answers3