Using 'like' in ssrs expressions
Solution 1
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")
Solution 2
It is like in access: not '%' but '*':
=Fields!Notes.Value Like "*deadline*"
Solution 3
"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")
Solution 4
Why not use something like:
Fields!Notes.Value.Contains("deadline")
Solution 5
It is case-sensitive. FYI. Use with lowercase - =IIf(LCase(Fields!Notes.Value) Like "*deadline*","Yellow","Transparent")
Related videos on Youtube
blsub6
Updated on July 09, 2022Comments
-
blsub6 almost 2 years
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?
-
blsub6 over 12 yearsI tried using Contains() before but I was missing the tolower() or tolowerinvariant() method to convert it from text to string. Thanks :)
-
nirav about 4 yearsIt is case-sensitive. FYI. Use with lowercase -
=IIf(LCase(Fields!Notes.Value) Like "*deadline*","Yellow","Transparent")
-
K. Ventura over 2 yearsWhat do you mean by converting text to string? Aren't they the same thing?
-
K. Ventura over 2 yearsCan 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"?