SSRS “OR” Filter Logic to filter from “=” and “IN”

Question

I'm working on a report where it needs to filter data from either of the two Built-in Fields. so I'm using the "FilterExpression" to query my requirement based on the below article i.e., Reporting Services (SSRS) OR Filter Logic. The article was good enough to operate on most of the cases. But I'm trying to filter using "=" and also "IN" Operators.

From the example mentioned in the article, what if I want to filter data from a list of states or a particular Country i.e., I want to filter from states (Washington,Minnesota,Tennessee, Oregon) or Country (United States). I know someone might suggest to go with just the country field but my application is slightly different from the example and it should filter data based on either of the two column values. so I'm tried something like as below in a brand new "filterexpression" and deleted the individual filters:

=(Fields!Country.Value = "United States") Orelse (Fields!State.Value IN SPLIT("Washington,Minnesota,Tennessee, Oregon",",")). 

But it seems like, the "IN" wasn't fitted in the code, as the window shows a red line underneath that and so I need to find an alternative(Not yet figured out). Later, I tried to follow the lines in the article to set the Value being set to True (Next to the function log to expression)and the expression type have a data type of Boolean, but of getting errors (I don't know how it really works).

Answer 1

I recently came across the same need do a DATASET FILTER with OR criteria using an IN operator and EQUALS (=) operator.

The answer by [Hannover Fist] includes the expression code with INSTR() function. In my scenario I had to filter against Number Datatype values. DEVELOPERS BE FOREWARNED... the INSTR() expression is only reliable with TEXT Datatype values. See the screenshot provided below.

I don't know why Microsoft doesn't just provide the option to change the assumed [AND] criteria to a configurable [AND/OR] criteria to be selected by the report developer. A configuration filter criteria would be so much simpler than the BOOLEAN FILTER EXPRESSION work-around option.

---

SSRS-DatasetFilter_(OR Condition-IN Filter).png

Answer 2

I would use the InStr function to search for the State field in the list.

Your expression would look like:

=IIF(Fields!Country.Value = "United States" Orelse InStr("Washington,Minnesota,Tennessee, Oregon", Fields!State.Value) > 0, 1, 0)

I usually use 1 and 0 for the filter values - SSRS doesn't always like the TRUE FALSE returned from expressions like 1 = 1 when comparing them as a boolean.

Microsoft: Instr-function