5

I have a field in SSRS that i need to filter on. For 3 table I can use a IN filter. But I am in need to use a NOT IN operator. The field contains numeric values.

I need to be able to say not in (30,31,32,33,34,35,36,37,38,39)

I cant do it within the dataset either, needs to be a filter.

How should I achieve it ?

Bhanu Chandra
  • 408
  • 8
  • 26
MJCookie
  • 135
  • 1
  • 3
  • 10
  • please provide an example of the code you have tried and the community can help show you what might need changing. – pancho018 Nov 20 '15 at 11:17

3 Answers3

15

You can use an expression to determine which values are going to be filtered.

Go to Tablix properties/ Filters

In expression use:

=IIF(Array.IndexOf(split("30,31,32,33,34,35,36,37,38,39",","),
CStr(Fields!YourField.Value))>-1,"Exclude","Include")

For Operator use:

=

For Value use:

="Include"

Let me know if this can help you

alejandro zuleta
  • 13,962
  • 3
  • 28
  • 48
  • 4
    For anyone who wants to use this with a parameter, just replace the string array ("30,31,32,33,34,35,36,37,38,39") with the following code: (Join(Parameters!YourParameter.Value, ",")). This can filter the table with values that are NOT IN values from a parameter, which is what I needed the NOT IN filter for. The final modified expression will look something like this: =IIf(Array.IndexOf(Split((Join(Parameters!YourParameter.Value, ",")), ","), CStr(Fields!YourField.Value)) > -1, "Exclude", "Include") – K. Ventura May 29 '20 at 21:46
1

For a variation of the selected answer that I find easier to use, please see below. This is in a Visibility expression, but should be easily ported to a Filter expression by setting Expression type to Boolean and comparing if Expression = True:

=IIf(InStr("unwanted values here", Fields!fieldToCheck.Value), True, False)

RiSt
  • 63
  • 1
  • 8
0

Firstly I would like to say that, to my knowledge, NOT IN is not available in list of operators for filters.

To Achieve the same thing in other way, please refer the following link..it might help you...

http://blog.datainspirations.com/2011/01/20/working-with-reporting-services-filters-part-4-creating-a-not-in-filter/

Bhanu Chandra
  • 408
  • 8
  • 26