locked
Report Builder Filter Not Like Wildcard RRS feed

  • Question

  • Hello.  I am brand new to SSRS/Visual Studio and am trying to filter a dataset field that is Not Like a value with a wildcard in the Dataset Properties-Filters area.  I can easily get the Like expression but I want the comparable Not Like expression to exclude the following names from my report, but there isn't a Not Like operator:

    Expression:  PtLastName
    Operator:     Like
    Value:          TEST*

    I want to EXCLUDE the following last names in my report:  TEST, TEST9, TEST142

    I have my varchar field as "rtrim(dbo.PT.lst_nm) AS PtLastName" in the dataset.

    Tuesday, January 21, 2014 5:42 PM

Answers

  • Not like is not a supported operator in SSRS filters so you have to work around it. Expression builder supports both the Not and Like operators so you could build your filter like:

    Expression: =IIf(Not(Fields!PtLastName.Value Like "TEST*"), 0, 1)
    Type: Integer
    Operator: =
    Value: 0

    Or

    Expression: =IIf(Fields!PtLastName.Value Like "TEST*", 0, 1)
    Type: Integer
    Operator: =
    Value: 1

    This puts the Like operator into the more workable/flexible expression builder


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by J King 9 Tuesday, January 21, 2014 11:37 PM
    Tuesday, January 21, 2014 8:22 PM

All replies

  • Not like is not a supported operator in SSRS filters so you have to work around it. Expression builder supports both the Not and Like operators so you could build your filter like:

    Expression: =IIf(Not(Fields!PtLastName.Value Like "TEST*"), 0, 1)
    Type: Integer
    Operator: =
    Value: 0

    Or

    Expression: =IIf(Fields!PtLastName.Value Like "TEST*", 0, 1)
    Type: Integer
    Operator: =
    Value: 1

    This puts the Like operator into the more workable/flexible expression builder


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    • Marked as answer by J King 9 Tuesday, January 21, 2014 11:37 PM
    Tuesday, January 21, 2014 8:22 PM
  • Thank you very much...this logic should work; I'll give it a try.
    Tuesday, January 21, 2014 11:35 PM
  • Great answer, thanks so much.
    Tuesday, December 20, 2016 7:19 PM