problems with multiple parameters in record selection statement

[rorymo]

SQL Server 2000
Crystal Reports 2008

Hello,
I am trying to get a multiple parameter record selection formula to work.
Specifically, I cannot get this particular parameter to return records. I have checked the links already.
(There are records that meet the criteria in the database.)

Any suggestions as to what I am doing wrong?

BTW, {chgcat.sym} is a string, and the ?Category parameter is a string parameter.
{chgcat.sym} is a multiple value (category) field.
The parameter settings are:
Allow custom values - True
Allow multiple values - True
Allow discrete values - True
Allow range values - False
Default is "ALL"

RECORD SELECTION (partial):

(
{chgcat.sym} in split({?Category}[1], ", ")
OR
{?Category} = "All"
)

This is the full record selection formula:
(
{chgcat.sym} in split({?Category}[1], ", ")
OR
{?Category} = "All"
)

and

(
{?Environment} = {zenvironment.sym}
OR
{?Environment} = "ALL"
)

and

(
{?Impact} = {@Impact}
OR
{?Impact} = "ALL"
)

and

(
{?Assignee} = {@Assignee}
OR
{?Assignee} = "All"
)

Thanks in advance,
Rory

 

[IanWaterman]

YOu need to change each section to this format

(If {?Category} <> "All") then {chgcat.sym} in split({?Category}[1], ", ") else true)

Ian

 

[lbass]

What is the content of {?Category}? Why is it necessary to use a subscript? Please provide a sample of the parameter entry.

-LB

 

[rorymo]

Thanks Ian, I'll try that.

Lbass,
The category parameter is for change order categories for a Service Desk application.
Some examples are:
ADR.HW.UNIX
ACS.SW.SYS.Platform.Linux

There are hundreds of these.

I used the subscript because I kept getting the "This array must be subscripted" message.
That was when I tried to use this:
(
{?Category}&"*" = {@Category}
OR
{?Category} = "All"
)
I have to use the wildcard because the names are so long and no one will want to type in the entire name(s).

Then I tried this:
{?Category} like {@Category}
OR
{?Category} = "All"
)
And it gave me the "a string is required" message.

I am probably overlooking something obvious but can't see it at this point.

Thanks,
Rory

 

[lbass]

Are the following examples of the field or of what is entered at the parameter prompt?

ADR.HW.UNIX
ACS.SW.SYS.Platform.Linux

If these are instances of the field, what might be entered at the prompt?

-LB

 

[rorymo]

Hi Lbass,

Those are examples of the data that is in the field. (possible values for the category field)
What might be entered at the prompt would be ADR.HW* or ACS* or All if they wanted all of the records for some reason.

BTW, I tried Ian's suggestion but still did not get any results.
(If {?Category} <> "All") then {chgcat.sym} in split({?Category}[1], ", ") else true)

Everything that I have seen has said to put the "all" part of the statement at the end rather than the beginning, which I also tried to no avail.

Thanks,
Rory

 

[rorymo]

Hello all,
I got it to work by removing the ) after the "All" and using
"like" instead of "in".

CODE:
(If {?Category} <> "All" then {chgcat.sym} like split({?Category}[1], ", ") else true)

Now I just need to get it to work with the wildcard.

Thanks,
Rory

 

[lbass]

Use a selection formula like this:

if {?Category} <> "All" then (
whilereadingrecords;
numbervar i;
numbervar j := ubound({?Category});
stringvar array k;
if {?Category} <> "All" then (
for i := 1 to j do (
if {chgcat.sym} like "*"+{?Category}+"*" and
not ({chgcat.sym} in k) then (
redim preserve k[j];
k := {chgcat.sym}
));
{chgcat.sym} in k
) else
if {?Category} = "All" then
true

-LB

 

[rorymo]

Thank you lbass!!!
Worked like a charm.