CODEWITHSUNDEEP
sql-server-2005reporting-services
SeanFlynn
SeanFlynn

Reputation: 453

Using Like Operator in SSRS 2005 IIF Expression

I have a pretty complicated IIF expression in SSRS, in which I want to change the background color of the cell will change based on certain conditions. It works fine in its current state, but I am trying to add a Like condition to it...

so, here's what it looks like now (working): 

 =IIf(inscope("matrix1_errorType"), 

IIf(

(
    FormatPercent(Sum(Fields!numberOfIssues.Value)/First(Fields!totalItems.Value),3) >= .0001 & "%" 
    And ( First(Fields!errorType.Value) = "TypeA")
)

Or 
(
    FormatPercent(Sum(Fields!numberOfIssues.Value)/First(Fields!totalItems.Value),3) >= .04 & "%" 
    And First(Fields!errorType.Value) = "TypeB"
)

Or 
(
    FormatPercent(Sum(Fields!numberOfIssues.Value)/First(Fields!totalItems.Value),3) >= .02 & "%" 
    And First(Fields!errorType.Value) = "TypeC"
    )
,
"Maroon", "Transparent")
, "Transparent")

What I would like to do is add a Like type statement somewhere in this line, which would say ...and location LIKE "Scan" - in other words, a location with "Scan" somewhere in the title. locationName is a field i display in the report as well.

(
    FormatPercent(Sum(Fields!numberOfIssues.Value)/First(Fields!totalItems.Value),3) >= .0001 & "%" 
    And ( First(Fields!errorType.Value) = "TypeA")
)

The like statement would be something similar to: 

... And ( First(Fields!errorType.Value) = "TypeA") And First(Fields!locationName.Value) Like "*Scan*"

but that doesn't work. I've also tried:

And ( First(Fields!errorType.Value) = "TypeA" And First(Fields!locationName.Value.ToString().Contains("Scan")

Which doesn't work either.

Any way I can do this in an SSRS 2005 expression?
Thanks!

Upvotes: 2

Views: 6843

Answers (1)

RThomas
RThomas

Reputation: 10882

I think your problem is syntax. Double check placements of () and , in your statement as Contains should work for what you are trying.

I also notice your statement:

First(Fields!locationName.Value.ToString().Contains("Scan"))

Should probably be:

First(Fields!locationName.Value).ToString().Contains("Scan")

You can also try the IndexOf function. IndexOf returns the character position where the string you pass occurs in the source. So if it's greater than (or equal to) zero your string contains the characters 'Scan' - if less than zero, the source doesn't contain the string.

Fields!YourField.Value.ToString().IndexOf("Scan") >= 0

Although it works roughly the same as Contains the way your using it.

Upvotes: 3

Related Questions