BusinessObjects Board

Crystal Command, selection criteria parse a string

building reports crystal reports
#1

I have a simple command to select patients from about 2000 records and the where clause is to filter it down to the program they are in. Here is the working code for 1 program:

select patid,episode_number,
patient_name,
program_code,
program_value,
c_unit_code,
c_unit_value

from system.view_episode_summary_current as ves

where ves.program_code IN {?Program}

I would like to be able to select multiple programs. The application of this report is that it is passed a single string to that ?Program parameter. So I have a multi-select checkmark list ProgA, ProgB, ProgC, etc. and when selected, it passes the string ProgA&ProgB&ProgC to the parameter.

So, is it possible to modify the where clause to accept this & delimited string and select the appropriate programs? I have to do this in the main report because I already have sub-reports based on this information. My report fully works for 1 program selection, but I’m asked to see if I can modify it. Also, I tried this in the selection criteria and the report ran unacceptably long.

Thank you for any help in this.

Hackareatech (BOB member since 2019-01-07)

#2

Command parameters do not support multiple values, but in your case if the program_code is a number type (strings will not work because you will have to pass values in quotes),

You can try changing your code to

where ves.program_code IN Replace({?Program},’&’,’,’)

Let me know how this works.

anil.ganga1 :us: (BOB member since 2007-07-04)

#3

I almost didn’t say anything about it being multiple values, because my app side is a multi-select, but ultimately it passes a single string of alphanumeric values in a string to the parameter.

Ex: 1-BA04&1-BA05&1-FN01

Ampersand (&) is the delimiter, so I need to match the program_code for any of those 3 values.

I did try what you suggested, but it did not work of course because the values are not numeric.

Any further ideas are appreciated.

Hackareatech (BOB member since 2019-01-07)

#4

Only workaround I knew is, having that parameter at report level and write logics to separate the delimited values and pass as inputs.

anil.ganga1 :us: (BOB member since 2007-07-04)

#5

Or alter your program to build a multi-select parameter instead of a single string. Then you could pass the list and have Crystal parse it into the correct format in the command.

Or, if your database has InStr() or Pos() functions that would determine whether the program code is in the string, you might try something like this (SQL Server syntax):

where CharIndex(’;’+ves.program_code + ‘;’, ‘;’+replace(’{?Program}’ + ‘;’, ‘&’, ‘;’)) > 0

This does a couple of things:

  1. Replace ‘&’ with ‘;’
  2. Append ‘;’ to the start and end of the parameter string.
  3. Add ‘;’ to the start and end of the program code in the database.
  4. Finally looks to see whether ‘;program_code;’ is in the parameter.

I put the semi-colons on both ends of the program_code and the parameter so that you don’t get a data where the full program_code value is just a part of a program_code that’s in the parameter. For example:

1-BA1 is a part of 1-BA10, but isn’t the same thing.

If ALL of your program_code values are always the same length - 6 characters in your examples - then you don’t need to include the extra semi-colons and may be able to just leave the ampersands as the delimiters.

-Dell

hilfy :us: (BOB member since 2007-04-16)