Report Builder 3.0 Calculated Field Expression Syntax for item similar to "contains"

I have a field in my database that displays a list of numbers separated by a coma.  Example "0, 1, 2" or "1, 3, 5, 6" .  I want to create a calculated field that says if the original field contains a 1, then display the corresponding text description.  The 1 could be in any location along the series.  I've tried various combinations of Iif, Left, or Like, but these keep resulting in syntax errors.  I'm looking for help to identify the proper function and related syntax.  

Any suggestions are greatly appreciated.

Thank you,
LVL 1
jaisy99Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
ValentinoVConnect With a Mentor BI ConsultantCommented:
The easiest option is to prefix and suffix the string with the delimiter and combine that with the InStr function.  In your example the delimiter is a comma followed by a space.  Here's how that goes:

=IIF(InStr(", " & Fields!YourField.Value & ", ", ", 1, "), "found", "not found")

Open in new window


Note that the search term starts and ends with the delimiter.  This is crucial because otherwise you'll find 1 while it's actually 11 (for example).
0
 
jaisy99Author Commented:
Thank you for pointing me in the right direction with Instr.  I'm new to report building and did not think to go to Instr first.  I ended up going with the following syntax =IIF(InStr(Fields!CF_Str.Value,"1")>0,"Found", " ")   , but the suggested syntax was helpful as well.
0
 
jaisy99Author Commented:
What I needed.  Thanks!
0
 
ValentinoVBI ConsultantCommented:
I ended up going with the following syntax =IIF(InStr(Fields!CF_Str.Value,"1")>0,"Found", " ")   , but the suggested syntax was helpful as well.

Well, as long as your numbers don't go higher than 9 it should work fine.  Otherwise you'll run into the issue mentioned in my first post.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.