BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

Crystal Command, selection criteria parse a string


 
Search this topic... | Search Crystal Reports... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Crystal Reports  Previous TopicPrint TopicNext Topic
Author Message
Hackareatech
Forum Member
Forum Member



Joined: 07 Jan 2019

Posts: 2



PostPosted: Mon Jan 07, 2019 5:02 pm 
Post subject: Crystal Command, selection criteria parse a string

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.
Back to top
anil.ganga1
Principal Member
Principal Member



Joined: 04 Jul 2007
ASUG Icon
Posts: 484
Location: San Francisco


flag
PostPosted: Tue Jan 08, 2019 11:15 am 
Post subject: Re: Crystal Command, selection criteria parse a string

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.

_________________
Thanks,
Anil.

Business Objects 6.5/XIR2/3.X/4.X
Crystal Reports 9/XI/XIR2/XIR2A/2008/2011/2013/2016
Back to top
Hackareatech
Forum Member
Forum Member



Joined: 07 Jan 2019

Posts: 2



PostPosted: Tue Jan 08, 2019 12:16 pm 
Post subject: Re: Crystal Command, selection criteria parse a string

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.
Back to top
anil.ganga1
Principal Member
Principal Member



Joined: 04 Jul 2007
ASUG Icon
Posts: 484
Location: San Francisco


flag
PostPosted: Wed Jan 09, 2019 10:12 am 
Post subject: Re: Crystal Command, selection criteria parse a string

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

Business Objects 6.5/XIR2/3.X/4.X
Crystal Reports 9/XI/XIR2/XIR2A/2008/2011/2013/2016
Back to top
hilfy
Forum Enthusiast
Forum Enthusiast



Joined: 16 Apr 2007
ASUG Icon
speaker.gif*3
Posts: 1901
Location: Atlanta, GA


flag
PostPosted: Tue Jan 22, 2019 1:35 pm 
Post subject: Re: Crystal Command, selection criteria parse a string

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

_________________
Protiviti
Data & Analytics Practice
https://www.protiviti.com/US-en/data-management-advanced-analytics
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Crystal Reports  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0378 seconds using 17 queries. (SQL 0.0121 Parse 0.0009 Other 0.0248)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo