Solved

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

Posted on 2014-02-18
5
1,467 Views
Last Modified: 2014-02-20
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,
0
Comment
Question by:jaisy99
  • 2
  • 2
5 Comments
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
Comment Utility
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
 
LVL 1

Author Comment

by:jaisy99
Comment Utility
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
 
LVL 1

Author Closing Comment

by:jaisy99
Comment Utility
What I needed.  Thanks!
0
 
LVL 37

Expert Comment

by:ValentinoV
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
Hi All, I am here to write a simple article to move SSRS (SQL Server Reporting Services) reports from one server to another. When I have faced the same issue to move reports those were developed by developer on development server and now need to …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now