Crystal Reports:Is it possible to create a Multi value parameter that uses "AND" logic and not "OR"?

[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 "OR" 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

[abhilash_kumar]

Hi Dwayne,

Use this code as the Record Selection Formula:

local stringvar array arr := {?Prompt_Name};
local numbervar i;
for i := 1 to ubound(arr) do
(
    if INSTR(arr[i], {String_Field}) = 0 then 
        exit for
    else
        true;
);
i = ubound(arr);

In the code above, replace {?Prompt_Name} with the multi value parameter and replace {String_Field} with the database text field where the prompt values are being searched in.

-Abhilash

[Former Member]

Hi Abhilash,

Thanks for your response. I did not notice any changes in my results it seems it still displays all records where at least one of my string values was found. I'm using a command object and have the following code in my WHERE clause:

WHERE REGEXP_LIKE(RESULTS.COMMENT, '{?Comment}', 'i')

In Crystal I'm using a Main report and a subreport. The Main report is used only to join the multiple parameter string values by "|" and the subreport is used to display all the records.

Below is the code that I added to the Record Selection on my subreport:

local stringvar array arr := {?Comment};
local numbervar i;
for i := 1 to ubound(arr) do
(
if INSTR(arr[i], {Command.COMMENT}) = 0 then
exit for
else
true;
);
i = ubound(arr);

Would greatly appreciate any suggestions. Thanks

[former_member11696]

Possibly add the Not IsNull test to it. IF CR runs into a field qhile querying that is null it stops processing the formula.

Don

[Former Member]

I added not is null but didn't seem to change anything. I not sure at this point if what I'm trying to do is even possible in Crystal Reports. Would greatly appreciate any suggestions. Thanks

[Former Member]

Please show your select filter as currently configured.

Ian

[Former Member]

Hi Ian,

By select filter do you mean record selection? If so, I'm currently doing my filtering by command object and not using record selection, the following where clause is used to pull all records that match any of the strings that a user may enter. What I'm hoping to do is pull only records that match all strings that a user enter.

WHERE REGEXP_LIKE(RESULTS.COMMENT, '{?Comment}', 'i')