Help for "Device_OIG" parameter that allows wildcard multiple value.

[kwtx04rm]

I have a report where users are allowed to enter multiple values for a "Device_OIG" parameter.

The "Device_OIG" parameter is setup as follows:
Value type - String
Options:
Allow multiple values
Descrete value(s)
Set Default Values:
Browse table - devicem1
Browse field - BA_ALT_ARRAY
these are a few of the default values from devicem1.BA_ALT_ARRAY...
WIM Oracle DBA
WIM SQL/Server DBA
WIMT Online Channel

And here is part of the record selection:

(If {?Device_OIG} = "" Then
True
Else
{devicem1.BA_ALT_ARRAY} Like {?Device_OIG})

If a user selects the 3 default values listed above, the report only returns records for "WIM Oracle DBA" but not for the other 2 (WIM SQL/Server DBA, WIMT Online Channel). I know that records for the other 2 exist because individual queries for each returns data.

The "Show SQL Query" for the Crystal Report shows:
WHERE ("devicem1"."BA_ALT_ARRAY" LIKE 'WIM Oracle DBA' OR "devicem1"."BA_ALT_ARRAY" LIKE 'WIM SQL/Server DBA' OR "devicem1"."BA_ALT_ARRAY" LIKE 'WIMT Online Channel')

I am not sure how to handle this since data for this field is an array.

I tried using this line of code after the Else:
{devicem1.BA_ALT_ARRAY} like "*" & {?Device_OIG} & "*"

But then a message indicating "This array must be subscripted" is displayed.

Then I tried using:
{devicem1.BA_ALT_ARRAY} like "*" + {?Device_OIG} + "*"

...and selected the 3 default values listed above, the report now returns way too many records...seems to not filter for the 3 selections.

This produced the "Show SQL Query" for the Crystal Report as follows:
WHERE ("devicem1"."BA_ALT_ARRAY" LIKE '%' OR "devicem1"."BA_ALT_ARRAY" LIKE '%' OR "devicem1"."BA_ALT_ARRAY" LIKE 'WIM Oracle DBA' OR "devicem1"."BA_ALT_ARRAY" LIKE 'WIM SQL/Server DBA' OR "devicem1"."BA_ALT_ARRAY" LIKE 'WIMT Online Channel')

Is there some other code I can use in the record selection that will use a "Like" command for this?

Thanks in advance!!

 

[lbass]

I know this field is named as an array, but your examples for the field show single values (I think). If you place the field in the detail section, can you show what the results would look like per record? What separator is used between values in the array?

Also, what CR version are you using?

-LB

 

[kwtx04rm]

Hello Ibass:

Currently using CR 10 Professional...will be upgrading to CR XI Developer.

Not sure how the array is delimited, but when I query the database (ServiceCenter DB2 Federated DB) a symbol that looks like a rectangle appears between elements in the array.

Below is sample data, but since rectangle symbol can't be entered in this message I replaced with a comma (,):

Dev OIGs
=== ====
Dev1 WIM Oracle DBA
Dev1 WIM SQL/Server DBA
Dev1 MTI West Test Group , WIM Oracle DBA
Dev1 <empty>
Dev2 WIM SQL/Server DBA
Dev2 <empty>
Dev2 MTI West Test Group , WIM Oracle DBA
Dev2 WIM Oracle DBA MTI West Test Group
Dev2 WIM Oracle DBA
Dev3 MTI Northeast Test Group , WIMT Online Channel
Dev3 WIM SQL/Server DBA
Dev3 WIM Oracle DBA
Dev3 WIMT Online Channel , MTI Northeast Test Group
Dev3 WIMT Online Channel
Dev3 <empty>
Dev4 WIM SQL/Server DBA , MTI West Test Group
Dev5 MTI West Test Group , WIM SQL/Server DBA

The record selection in question is:

(If {?Device_OIG} = "" Then
True
Else
{devicem1.BA_ALT_ARRAY} Like {?Device_OIG})


The report does not return all records when user selections for {?Device_OIG} do not have an asterisk "*" before and after the selected parameter value. For example if I select WIM SQL/Server DBA only 3 records are returned. However, if I enter *WIM SQL/Server DBA* 5 records are returned. I would like for the report to return the 5 records without the user having to manually enter the asterisk "*".

 

[IdoMillet]

See my answer for a similar situation at: thread767-1343190

Notes:
1. I didn't test that code
2. If you know you don't need more than 3 parameter values, creating 3 parameters would provide faster record retrieval (the simpler logic would allow the condition to be included in the WHERE clause).

- Ido

view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

This is similar to Ido's suggestion:

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

For i := 1 to j do(
If ucase({devicem1.BA_ALT_ARRAY}) like "*" + ucase({?Device_OIG}) + "*" then
x := x + {devicem1.BA_ALT_ARRAY} + ", "
);
if {?Device_OIG} <> "All" then
{devicem1.BA_ALT_ARRAY} in x else
if {?Device_OIG} = "All" then
true
)

-LB