Wild Card used as a Parameter

[pkohli88]

Hi,
Is it possible to create a report that runs based on multiple wild card words used as parameter.

for example if i create one parameter say country.

and with that parameter , i can look for anthing like that starts with IN or AS or US ...

means it should select countries like INDIA, INDONESIA, USA , USSR etc

Thanks In Advance

 

[TheBlondOne]

You can just create you parameter as normal and allow multiple values, and when you assign the parameter into the select expert you select startswith instead of equals.


HTH

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[pkohli88]

Thanks for yOur reply

I created a parameter with multiple values but when i use select expert with option "starts with" the parameter i created doesn't show up in the dropdown list.
but when i use select expert with option "equals to" the parameter shows up in the drop down list

 

[lbass]

Just go to report->selection formula->record and enter:

{table.country} startswith {?country}

-LB

 

[pkohli88]

Thanks
It worked.
Suppose Instead of IN*,US* ,AS* if i want to search for *IN*, *US*, *AS* how can i do this.
It means, instead of using Startswith if i want look for that parameter string in whole field value

 

[pkohli88]

my problem is similar to the problem explained in this thread thread149-836731. For the solution vidru (TechnicalUser) referenced to Check this thread: Thread766-836787.

but when i click on that thread it's showing not avaliable

 

[synapsevampire]

How would Crystal know whether you want it anywhere in the field, or just in the beginning?

You've asked for two different things, do you expect them both in one solution, or are you looking for 2 different solutions?

-k

 

[pkohli88]

Sorry for creating confusion,
These are two completely different things. I should have mentioned this before

I got answer for my first problem.

I am looking for answer to second problem. That is If i want to look for string anywhere in the field

Thanks

 

[lbass]

Try:

whilereadingrecords;
numbervar i;
numbervar j := ubound({?country};
stringvar x;

for i := 1 to j do(
if {table.address} like "*"+" "+{?country}+"*" then
x := x + {table.address} + ", "
);
{table.address} in x

I added the space before the parm to help distinguish countries from strings that could be found in other parts of the address like, e.g., BELARUS vs US.

Note that this record selection formula will not pass to the database and will affect speed accordingly.

-LB

 

[pkohli88]

Thanks LB,
The above method did work for me. As you said this record selection would not pass to the database.

Its greatly affecting the data because i have abot 200,000 records to filter so above method is taking a while to run.

I was trying something like that i am not sure its right or not, IF we use "STARTSWITH" function from crystal then crystal sends LIKE command to the database, in the WHERE clause of SQL
parameter(country) = IN
e.q

SELECT "Command"."country" FROM   "EMMISJPMC"."COST_CENTER_DEF" "Command"
 WHERE  "Command"."country" LIKE 'IN%'

so if instead of using "IN" in parameter I use %IN

so parameter = %IN

I ran no records selected

but when i looked at the sql it like this

SELECT "Command"."country" FROM   "EMMISJPMC"."COST_CENTER_DEF" "Command"
 WHERE  "Command"."country" LIKE '%IN%'

so according to above SQL crystal should return all records that contains "IN" string.

Please let me know if i am missing something.

Thanks

 

[lbass]

You can use "like" in the record selection formula, as in:

{table.address} like "*"+{?country}+"*"

You just can't do this if you set up the parameter to allow multiple values.

When you added the % to the beginning of the parameter string, it treated it like a literal %, not the wild card, which is why you got no records. When setting up parameters in a record selection formula you have to concatenate the wild cards as I just did, instead of including them in the string, and the asterisk is the wild card character, not '%' even though it translates to this in the query.

-LB

 

[pkohli88]

Hi LB,
I am using formula suggested by you as follows

whilereadingrecords;
numbervar i;
numbervar j := ubound({?country};
stringvar x;

for i := 1 to j do(
if {table.address} like "*"+" "+{?country}+"*" then
x := x + {table.address} + ", "
);
{table.address} in x

but alongwith the parameter values i am also getting null values. how can i restrict those null values.

i tried this
{table.address} in x and NOT ISNULL{table.address}

i am again getting null values

Thanks

 

[MCuthill]

pkohli88,

I am not 100% on this, but I have encountered several instances where the order of my records selection made a difference (when using certain Functions), in short [blue]IsNull[/blue] is one of them.

Try:

[blue]Not[/blue]([blue]IsNull[/blue]({Table.Address})) [blue] AND [/blue]
{Table.Address} [blue]IN[/blue] X

Hope This Helps,

Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"

 

[lbass]

You need to test for nulls first. Try:

whilereadingrecords;
numbervar i;
numbervar j := ubound({?country};
stringvar x;

for i := 1 to j do(
if not isnull({table.address}) and
{table.address} like "*"+" "+{?country}+"*" then
x := x + {table.address} + ", "
);
{table.address} in x

-LB

 

[pkohli88]

Hi LB,
I tried the above selction formula but its still not able to filter NULL values. Means i am still geting null values in the final result.

 

[pkohli88]

I am trying this formula

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


for i := 1 to j do(
if NOT ISNULL({OPERATING_SYSTEM_DEF.OS_NAME}) AND {OPERATING_SYSTEM_DEF.OS_NAME}  like "*"+" " +{?OS}[i]+"*"
then x := x +{OPERATING_SYSTEM_DEF.OS_NAME}+ ", "
);

{OPERATING_SYSTEM_DEF.OS_NAME} in x

 

[lbass]

I'm not sure what's going on. When I test this, nulls are eliminated. Is this your entire record selection formula or only part of it?

-LB

 

[pkohli88]

HI LB,
I am also not able to figure out
when i use only this in record selection formula

not isnull({OPERATING_SYSTEM_DEF.OS_NAME})

then i don't get any null values but when i include
below formula

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


for i := 1 to j do(
if (NOT ISNULL({OPERATING_SYSTEM_DEF.OS_NAME}) and {OPERATING_SYSTEM_DEF.OS_NAME}  like "*"+{?OS}[i]+"*")
 then x := x +{OPERATING_SYSTEM_DEF.OS_NAME}+ ", "
);

not isnull({OPERATING_SYSTEM_DEF.OS_NAME})

i am still looking for NON NULL values but i just added a loop even though i am not using result of loop but in the final result I am getting NULL values.

do you think i need to change settings in crystal for usinf formula in selection formula to exclude nulls

 

[lbass]

Try:

not isnull({OPERATING_SYSTEM_DEF.OS_NAME}) and

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

for i := 1 to j do(
if {OPERATING_SYSTEM_DEF.OS_NAME} like "*"+" " +{?OS}+"*"
then x := x +{OPERATING_SYSTEM_DEF.OS_NAME}+ ", "
);

{OPERATING_SYSTEM_DEF.OS_NAME} in x
)

-LB

 

[pkohli88]

Hi LB,
I try to find solution on Bussiness Objects Site. According to them first convert null to default value to do so

A) STEPS TO CONVERT NULL FIELD VALUES TO DEFAULT VALUE FOR ALL FIELDS IN CR

1. Click File > Report Options

2. Select the 'Convert NULL Field Values to Default' check box.

3. Click 'OK'.
now CR converts the null value to default value. so i tried using below formula and it worked

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

for i := 1 to j do(
if {OPERATING_SYSTEM_DEF.OS_NAME}  like "*"+{?OS}[i]+"*"
then x := x +{OPERATING_SYSTEM_DEF.OS_NAME}+ ", "
);

{OPERATING_SYSTEM_DEF.OS_NAME} in x and {OPERATING_SYSTEM_DEF.OS_NAME}<>""

but i still don't know how was the NOT NULL function working for you and was not working for me.