Using 'like' in ssrs expressions

Question

I'm trying to highlight a field when the value has the word 'deadline' in it. I'm trying to use the expression:

=IIf(Fields!Notes.Value like "%deadline%","Yellow","Transparent")

in the BackgroundColor property.

It's not highlighting the field (not changing the background color). The 'Notes' field is a text datatype and I'm using Report Builder 3.0 if that makes a difference. What am I doing wrong?

devarc · Answer 1 · 2012-01-25T20:49:21.160

47

It is like in access: not '%' but '*':

=Fields!Notes.Value Like "*deadline*"

edited Jan 25 '12 at 20:49

answered Jan 25 '12 at 20:38

devarc

1,157
1
7
11

1

It is case-sensitive. FYI. Use with lowercase - `=IIf(LCase(Fields!Notes.Value) Like "*deadline*","Yellow","Transparent")` – nirav Apr 15 '20 at 08:55
Can the expression be used to look for phrases in the string like "*text with spaces*" or does it only work with single words like "*textwithoutspaces*"? – K. Ventura Oct 23 '21 at 07:12

score 17 · Accepted Answer · edited Jan 11 '14 at 17:36

17

SSRS does NOT use SQL syntax, but instead uses Visual Basic.

Use something like this:

=IIf(Fields!Notes.Value.IndexOf("deadline") >= 0,"Yellow","Transparent")

Or .Contains instead of .IndexOf

=IIf(Fields!Notes.Value.ToLowerInvariant().Contains("deadline"),"Yellow","Transparent")

edited Jan 11 '14 at 17:36

Malachi

3,205
4
29
46

answered Jan 25 '12 at 20:34

Oleg Dok

21,109
4
45
54

1

I tried using Contains() before but I was missing the tolower() or tolowerinvariant() method to convert it from text to string. Thanks :) – blsub6 Jan 25 '12 at 21:52
What do you mean by converting text to string? Aren't they the same thing? – K. Ventura Oct 23 '21 at 07:08

score 11 · Answer 3 · answered Aug 16 '13 at 10:06

11

"InStr" works for me:

=IIF(InStr(Fields!Notes.Value,"deadline")>0, "Yellow", "Transparent")

Remember that the compare value is case-sentive, so maybe use UCASE around:

=IIF(InStr(UCASE(Fields!Notes.Value),"DEADLINE"))>0, "Yellow", "Transparent")

answered Aug 16 '13 at 10:06

JanBorup

5,337
1
29
17

score 2 · Answer 4 · answered Jan 25 '12 at 20:35

2

Why not use something like:

Fields!Notes.Value.Contains("deadline")

answered Jan 25 '12 at 20:35

Etch

3,044
25
31

score 1 · Answer 5 · edited Nov 18 '20 at 08:28

1

It is case-sensitive. FYI. Use with lowercase - =IIf(LCase(Fields!Notes.Value) Like "*deadline*","Yellow","Transparent")

edited Nov 18 '20 at 08:28

Simas Joneliunas

2,890
20
28
35

answered Nov 18 '20 at 07:47

Surya

31
5

Using 'like' in ssrs expressions

5 Answers5

Linked