SSRS - Using iff(Instr(Fields! to find certain text in a string

Question

I'm struggling (spending LOTS of time trying to figure this out) to make an iff statement work in SSRS/Report Builder.

I'm pulling a VARCHAR into a report. In the VARCHAR, consists of lots of words/text.

I'm trying to colour this textbox if this VARCHAR string contains the word "red" or "amber" or "green".

If it does find either of these words in the VARCHAR string, I would like it to colour the textbox the same colour as the text it's looking for.

If it finds "red" in the VARCHAR, the textbox becomes red in colour, etc.

I've looked on google and youtube lots and have found two potential solutions:

• iff(Fields!note.value.contains("red"), "red", "white")

-Above I believe should look for "red" in the VARCHAR string and then fill the textbox red if it matches or white if no match?

• iff(InStr()<0, [true], [false])

The second option I have no idea how this would work. I apologise that my syntax for vba is awful. Any help would be GREATLY appreciated. I'm trying to learn SSRS and/or Report Builder on the job.

Please note I'm using Report Builder ver 15 OR Visual Studio 2019.

Kind regards.

Answer 1

You should be able to use Tostring.Contains() like this... (I've used SWITCH rather than nested IIFs as it's cleaner in my opinion.

=SWITCH(
       Fields!AddressLine1.Value.ToString.Contains("Street"), "Red",
       Fields!AddressLine1.Value.ToString.Contains("Road"), "Green",
       Fields!AddressLine1.Value.ToString.Contains("Ave"), "Orange",
       True, Nothing)

The last True acts like an else. I've also used Nothing which is the default 'transparent'

I used this on a test dataset and applied the expression to the last column, here are the results....