SSRS - Adding "LIKE" filter criteria to report builder

Question

I want to add a LIKE filters with wildcards in SSRS report builder. I tried this using contains clause present in filter data section of report builder. I tried both '*' and '%', but of them failed.

I tried MyFieldName contains 2451 - this succeds MyFieldName contains 24* - this fails MyFieldName contains 24% - this fails

From below link I feel that this is an old problem with no solution till yet.

http://connect.microsoft.com/SQLServer/feedback/details/202792/ssrs-adding-like-filter-criteria-to-report-builder

What do you guys suggest?

Thanks
Ravi Gupta

score 7 · Accepted Answer · answered Apr 30 '12 at 11:43

7

You could use the InStr function

=IIF(InStr(Fields!MyFieldName.Value, "2451"),TRUE,FALSE)

answered Apr 30 '12 at 11:43

Darren

68,902
24
138
144

Thanks darren for prompt reply, what I need is "=IIF(InStr(Fields!MyFieldName.Value, "2451*"),TRUE,FALSE)" and I get error saying invalid character when I write this. – TechnicalSmile Apr 30 '12 at 11:52
Below worked for me IF(FIND(MyFieldName, "24") = 1, TRUE, FALSE) Got this idea from your answer. Thanks. I'll add this as answer to my own question, but can do so only after 8 hours. – TechnicalSmile Apr 30 '12 at 12:21
Seems to me that this doesn't really answer the question since it doesn't handle wildcards. It's nice that the OP was able to move on with his project, but it doesn't help future users who are looking for a way to use wildcards in an SSRS filter. – Tab Alleman Jan 19 '15 at 20:38
@TabAlleman this answer has been accepted and helped the OP, which was my aim. If you have a better solution then feel free to post it. – Darren Jan 19 '15 at 21:34

score 4 · Answer 2 · edited Jun 26 '13 at 08:55

4

In SSRS we can't use Like. Instead of which you can use Contains.

IIF((MyFieldName).ToString().Contains("RequiredString"),"True","False)

edited Jun 26 '13 at 08:55

Robert

5,278
43
65
115

answered Jun 26 '13 at 08:36

N.Sreenivasa Rao

41
3

score 3 · Answer 3 · answered Jul 11 '14 at 03:09

3

In the report builder in Visual studio there is a Like and it works. Use a * as wildcard in your search string

answered Jul 11 '14 at 03:09

Gertjan

519
4
5

score 1 · Answer 4 · answered May 01 '12 at 08:06

Answering my own question, Below function worked for me:

IF(FIND(MyFieldName, "24") = 1, TRUE, FALSE)

This is just a partial answer as this will work for cases like (blabla*), but its not for cases like (bla*bla, blabla*). So anyone having a better idea is most welcome.

Got idea to do this from Darren's comment above.

Thanks
Ravi Gupta

Diego · Answer 5 · 2012-04-30T19:47:18.773

0

The Report Builder does not support the LIKE operator. You must use CONTAINS;

edited Apr 30 '12 at 19:47

answered Apr 30 '12 at 13:04

Diego

34,802
21
91
134

score 0 · Answer 6 · answered Jan 29 '20 at 16:24

This is pretty late to the party, but I worked out a solution for parameter filters for my dataset. Adding a filter to my dataset with
Expression:

=IIF(InStr(Fields!AccountName.Value, Parameters!paramAccountName.Value) OR Parameters!paramAccountName.Value = "*", TRUE, FALSE)

Type

Boolean

Operator

Value

true

The parameter are defaulted to "*" so the report looks like it grabs everything by default.

score 0 · Answer 7 · answered Dec 24 '20 at 20:24

0

I just came across this old thread and I'm curious why you can't use the like keyword, since I've always used it. Did you try something like this? Where (AccountName like '%' + @paramAccountName + '%' or @paramAccountName ='')

answered Dec 24 '20 at 20:24

openball

21
4

SSRS - Adding "LIKE" filter criteria to report builder

7 Answers7