CODEWITHSUNDEEP
sqlreporting-servicesexpressionreportbuilder3.0ssrs-expression
blsub6
blsub6

Reputation: 395

Using 'like' in ssrs expressions

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?

Upvotes: 24

Views: 109081

Answers (5)

RamKumar
RamKumar

Reputation: 41

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

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21766

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")

Upvotes: 17

JanBorup
JanBorup

Reputation: 5615

"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")

Upvotes: 11

devarc
devarc

Reputation: 1157

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

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

Upvotes: 47

Etch
Etch

Reputation: 3054

Why not use something like:

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

Upvotes: 2

Related Questions