Crystal Reports:Possible to create a Multi value parameter that uses"AND" logic instead of "IN"?

[Former Member]

I'm fairly new to Crystal Reports. I have a report that uses a multi value parameter string to search through a block of text. A user pointed out that they would like the ability to have "AND" logic on this parameter, where the results returned are only those that meet all the values entered for this string.

For example: If the user enters "Biopsy", "Prostate", "Lateral" they would like each row to have all of these string values. Currently the report will pull records based on "IN" logic where it pulls records as long as one of these string values are found.

Is it possible to have "AND" logic on a multivalue parameter? If possible could you please provide examples to this solution. Thanks

[DellSC]

This is not possible directly in a multi-value parameter. There might be a way to do this with just a single string parameter, but the value you're searching for would have to exactly match what's in the database.

If you're looking for all of these words in a single field in a single row in the database (e.g., not looking for a situation where the words are spread across multiple records), and you have Crystal 2008 or newer, you might be able to do something like this:

1. Determine what the maximum number of words is for the search. For this example, I use four words.

2. Create a separate string parameter for each word. I'll call these {?Search1}, {?Search2}, {?Search3}, and {?Search4}. Your user would enter a single word or phrase in each. Set these parameters as optional, with a default value of "" (empty string).

3. In the Select Expert, use something like the following formula (you'll have to go to the Formula Editor in the Select Expert...)

(not HasValue({?Search1}) OR {?Search1} in {MyTable.MyField}) AND
(not HasValue({?Search2}) OR {?Search2} in {MyTable.MyField}) AND
(not HasValue({?Search3}) OR {?Search3} in {MyTable.MyField}) AND
(not HasValue({?Search4}) OR {?Search4} in {MyTable.MyField})

This will search for all of the words that are entered and ignore any parameters that have not been entered.

-Dell