Skip to Content
avatar image
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Aug 09, 2017 at 07:17 AM

    Please show your select filter as currently configured.

    Ian

    Add comment
    10|10000 characters needed characters exceeded

    • 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')

  • Aug 09, 2017 at 08:08 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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