Converting string parameter input to upper case

[mocgp]

Crystal 10; Oracle 9

Is there a way to convert parameter string values to upper case? I need to retain the * option in the parameter, so this precludes the use of the edit mask character >. I can't use the UpperCase function on the parameter in the report filter because it is set to Allow Multiple Values.

I guess another related question might be this: If there is a way to do this in Crystal Pro, will it still work once the report is published in Enterprise?

 

[IanWaterman]

How are you using the parameter? Please paste where being used and examples of data likely to be entered. I do not understand why you can not use UPPERCASE function.

Ian

 

[mocgp]

Ian,

I have many reports that I need to revise for this option if a solution can be found. For purposes of testing, I did a simple query of a table by selecting distinct records on a field that is Str,8. This returned 448 records, so I set up a parameter with a single default option of * and checked the Allow Multiple Values box. (The end user has to be able to select one, many or * for All values.) I can run this query for one or many but I have to use upper case to match the database.

The filter reads:

{myfield} like {?parameter}

If I change this to

{myfield} like UpperCase({?parameter})

I get the error "Array must be subscripted"

The UpperCase function works if the parameter allows only one value.

 

[IanWaterman]

It will slow down processing but have you considered making select date case insensitive,

File->Report options
Check - Daatbase Server is Case Insensitive

That may cure your problem.

Ian

 

[mocgp]

Interesting point, especially since that box is already checked.

 

[IanWaterman]

That has stumped me.

Try

{myfield} in [uppercase(join({?parameter},","))]

Ian

 

[mocgp]

I tried that earlier. The formual doesn't fail but it only returns the first item in the parameter. Won't show multiples.

 

[IanWaterman]

If that works you will need to extend select to somthing like.

Instead of * use All

If Parameter <> "All" and count(parameter) = 1 then
myfield like parameter&"*" else
If Parameter <> "All" and count(parameter) > 1 then
{myfield} in [uppercase(join({?parameter},","))]
else true

Ian

 

[mocgp]

That one also retuns the "Array must be subscripted" error.

 

[lbass]

I think you can just do a slight variation on Ian's last suggestion:

if {?parm} <> "All" then
{table.field} in ucase(join({?parm},",")) else
if {?parm} = "All" then
true

-LB

 

[mocgp]

OK, we're getting closer. LB's formula works for the ucase function but it pulls unwanted records. Example -

One of my records is the word ASSIST; I entered that as a parameter (either uppercase or lowercase) and expected to get only that word in return. Instead, I get ASSIST plus 6 other records - IS, SI, SS, ST, T, and " ", all of which are records in this table.

 

[lbass]

Well, I think you can't have used my formula exactly as written. Please post the formula you used.

-LB

 

[mocgp]

Actually, I copied your formula into the filter and just replaced the corresponding pointers.


if {?Resource Code} <> "All" then
{ODS_TIDSCHRC.RESOURCE_CODE} in ucase(join({?Resource Code},",")) else
if {?Resource Code} = "All" then
true

 

[lbass]

Okay, I see. Try this:

(
if {?Resource Code} <> "All" then
{ODS_TIDSCHRC.RESOURCE_CODE}+[red]","[/red] in ucase(join({?Resource Code},","))+[red]","[/red]else
if {?Resource Code} = "All" then
true
)

-LB

 

[mocgp]

That returns ASSIST, ST, T, and the "

 

[lbass]

Sorry, kind of hard to test this. Try adding spaces also then:

(
if {?Resource Code} <> "All" then
" "+{ODS_TIDSCHRC.RESOURCE_CODE}+"," in
" "+ucase(join({?Resource Code},", "))+", " else
//add a space after the comma
if {?Resource Code} = "All" then
true
)

-LB

 

[mocgp]

OK, that seems to work. The downside is that it runs on the client so it will be a performance issue in many of our reports. I also have yet to test it in CE to see if the uppercase comes over to the scheduling parameters. I will do that in the morning and let you know what happens.

Thanks (both of you) so much for your efforts on this question.

 

[mocgp]

The formula works in Crystal Enterprise as well. One other thing I noticed is that it does not allow for the use of wild cards as does the LIKE operator. Most of our parameters do not need the wild card option but there are a couple that will need to retain that. I don't suppose there is a way to invoke wild cards without the use of the LIKE operator ??

 

[IanWaterman]

Looks difficult to add in a wild card clause.

Could you consider using a second parameter. So you leave first one as All.

Have the second param default as Null
add this to your select statement.
(If not(isnull(param2) then {ODS_TIDSCHRC.RESOURCE_CODE} like param2 else true)

However, LB may have a better suggestion.

Ian

 

[lbass]

You could try the following instead:

numbervar i;
numbervar j := ubound({?Resource Code});
stringvar x;

for i := 1 to j do(
if {ODS_TIDSCHRC.RESOURCE_CODE} like "*"+ucase({?Resource Code})+"*" then
x := x + {ODS_TIDSCHRC.RESOURCE_CODE} + ", "
);
if {?Resource Code} <> "All" then
{ODS_TIDSCHRC.RESOURCE_CODE} in x else
if {?Resource Code} = "All" then
true

-LB