Crystal Command Format a String
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22718
Printed Date: 29 Apr 2024 at 2:03am
Topic: Crystal Command Format a String
Posted By: Hackareatech
Subject: Crystal Command Format a String
Date Posted: 11 Jan 2019 at 4:47am
I am writing a Crystal Command for use with a Cache DB.
My parameter sent in to the command is a STRING variable in the format of Prog&Prog&Prog (ex. 1-BA04&1-BA05&7-FN01) with no preceding or trailing characters such as ' " space, etc.
The where clause in my query looks similar to this:
WHERE program_code IN {?program_list}
Because this is alphanumeric list, I need the format to ultimately be:
WHERE program_code IN '1-BA04','1-BA05','7-FN01'
My question is how to add the beginning and ending single quote ' AND the ',' (single quote comma single quote) between the program names.
I have modified my WHERE to look like this:
WHERE program_code IN REPLACE(?program_list),'&',''',''')
I'm not sure if that will truly replace my & with ',' and I'm still missing the beginning and ending '
Any help in this would be most appreciated.
|
Replies:
Posted By: DBlank
Date Posted: 11 Jan 2019 at 10:54am
|
are you asking how to multi-select in a parameter (from a program list) and use the multiple selections to filter the data set?
|
Posted By: hilfy
Date Posted: 22 Jan 2019 at 7:00am
If you just pass it in as a multi-select parameter instead of the "&" delimited string, Crystal knows how to parse it to get it in the correct format.
You would then just have this in the where clause:
WHERE program_code in {?program_list}
-Dell
-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics
|
Posted By: Hackareatech
Date Posted: 25 Jan 2019 at 11:37am
Thank you Hilfy, but I can't change the format of the string before it hits the crystal report. The string is an ampersand (&) delimited string.
We are going a different route and running a stored procedure to run the crystal against.
|
|