Want to Set Up Parameter to Filter for A String

[kc27]

I am using Crystal Reports 9 on a sql database. One of the fields in my report is bt_images.img_desc. The records in bt_images are photography image files. The field bt_images.img_desc is a description of the image. I would like to allow users of the report to enter an image description, and have the report bring back any image that includes the word(s) the user has entered. I went into Field Explorer, right clicked on Parameter Fields, and created a parameter with a value type of "string" and under options selected discrete values. I clicked the "Set Default Values" button and selected the table and field, then clicked "OK"

When I refresh the report, it prompts for a parameter. I enter one, but the report data is never filtered. What am I missing in trying to create a parameter field to filter by a string?

Thanks in advance for any ideas on this.

 

[BeeBear2]

Have you put the formula in the selection criteria or made the data show/hide depending on if it meets the parameter value?

 

[bdreed35]

It sounds like you have referenced the parameter in your report, like dropping it on the report for display purposes possibly, but not used it in the actual Record Selection in order to filter the report records.

I don't have a copy of version 9 on my laptop so these instructions may be different than in version 2008 that I am using.
There should be a tool called the Select Expert that you can access from the Report menu or a button in one of the tool bars.
Open it up, choose the img_desc field, then "is equal to" for the operator, and then your parameter field in the last drop down.
Click OK to close and refresh your report. You should get prompted again so select a description and click OK. Data should come back filtered at this point.

~Brian

 

[kc27]

I worked on this after posting my initial question. You are right, it did not work until I added

{bt_images.img_desc} like "*"+{?Image Desc}+"*"

to the record selection forumula.

Thanks for the the advice on this.

 

[kc27]

Thanks for the help. I did try using the Select Expert equals the Parameter Field. It does not return any results. The only way I get any results is by including {bt_images.img_desc} like "*"+{?Image Desc}+"*" in the selection forumula

 

[bdreed35]

I wasn't sure from your original post if you were looking to get exact matches of values from your img desc field or if you want to be able to match patterns. Since you want to match a pattern, the solution you found is the correct approach. Nice job figuring that part out!

~Brian

 

[kc27]

Thanks, I do need some additional advice. because I am finding I am getting too many results.

If I am searching for the image descriptions that contain the word "lane", I also get records that have descriptions with the words such as "delaney", "plane", and any other word that contains the letters l-a-n-e. If I enter the letters lane with single or double quotes, I don't get any results.


I'm not sure what I need to change in my formula {bt_images.img_desc} like "*"+{?Image Desc}+"*" to get it to match only whole words that are part of the description. Does anyone have any ideas on this?

 

[CoSpringsGuy]

{bt_images.img_desc} like "* "+{?Image Desc}+" *"

Note the additional spaces

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[bdreed35]

You can either add the additional spaces like CoSpringsGuy posted or you can add any spaces to search on when the user puts in the prompt value. There are pros and cons for each.

If you add the spaces in the record selection criteria, it saves the user from having to remember to add them. The con is that you can't search on partial words.

If you require that the user add the spaces to the prompt value, you get more flexibility for the user but the downside is that they have to remember to add them.

~Brian

 

[kc27]

Thanks CoSpringsGuy and bdreed35, for the solution, and for outlining the implications for the user depending on how the solution is implemented.

 

[kc27]

How does this work if I am searching for a phrase? I tested and understand putting the space before and after a single word brings back results that only contain that whole word, but what if I want the query to return results that consist of more than one word, such as "Cleveland Browns"? How do you enter the search terms to bring back any descriptions that contain a multi-word phrase?

 

[bdreed35]

You should be able to use this same solution for multi-word phrases. Have you tried it out?

~Brian

 

[kc27]

I tried space[Word One]space[Word Two]space and did not get any results. I just went back and tried it again to double check myself before responding, it still did not return any results. I then tried something different and entered [Word One]space[Word Two] and that worked.

 

[bdreed35]

The text has to match exactly. Leading and trailing spaces are characters just like letters and numbers. If they do not exist as pat of the values in the database column, it won't find it. When you did not use the leading and trailing spaces, it did find it.

~Brian

 

[kc27]

That makes sense. I was so focused on finding single words, I did think to consider searches for phrases. Thanks for the explanation.