multiple values in paramters- array subcripted error!

[arodri]

Hello,

I have been struggling with this issue for a few days now and I have a deadline so any help will be greatly appreciated.
I am using Crsytal XI and it is connected to a SQL database.
I have 5 parameters for my report right now. (2 are for the start and end date, one is just so the user can choose a report name, and the other two are Item Name and Section Name )

Just some general info: Each section contains a certain number of items (depending on the date). Right now if they leave the Item Name and Section Name parameters blank, it will return all of the item and section names in the report.
If they put UW in the ItemName parameter and leave the SectionName parameter blank, it will return only items with UW in the name, and all sections that have those items.
Here is the formula I'm using now (same for both parameters):
--> Formula:
IF {?Item Name} ="" then {SalesItem.ItemName}
else
lowercase ({SalesItem.ItemName})

--> Record Selection Formula:
{?Item Name} IN {@ItemName Parameter}

This works great. But now what I need to add is the following:

-I need the user to be able to enter multiple values for both parameters.
-I also need the user (like I'm doing now) to be able to enter only part of an item/section name, and the report return all items and/or sections that have what the user typed in. (For example, user types item name: soc report returns: Humane Society ..and any other item with soc in it)

- When I change the multiple values option in the parameter to "true" it then gives me the error: This Array must be subscripted. For example: Array
I'm not even sure what subscripted means?!
It then takes me to my record selection formula, so I added "[1]" :
{?Item Name}[1] IN {@ItemName Parameter}

-Do I also need to add something to my formula field?

It gets rid of that error, but when I enter more than one value, it only returns back values for the first item entered. Nothing else.

Any ideas?

Thanks in advance!

 

[lbass]

I don't understand the logic of this formula:

IF {?Item Name} ="" then
{SalesItem.ItemName} else
lowercase ({SalesItem.ItemName})

It seems to just return the name as it appears in the database versus in lower case. Is there a pick list that users are choosing from so that not putting it in lower case automatically excludes the records? If you don't have a picklist, how are you ensuring that users use lower case parameter entries?

I also don't see how the 'UW' works as it is not referenced in your selection formula.

In general, you can use multiple value parameters with the like operator as follows:

numbervar i;
numbervar j := ubound({?Item Name});
booleanvar flag := false;
for i := 1 to j do(
if {SalesItem.ItemName} like "*"+{?Item Name}+"*" then
flag := true
);
flag = true

Make sure that in file->report options->database server is case-insensitive is checked. Otherwise, change the formula to:

numbervar i;
numbervar j := ubound({?Item Name});
booleanvar flag := false;
for i := 1 to j do(
if lcase({SalesItem.ItemName}) like "*"+lcase({?Item Name})+"*" then
flag := true
);
flag = true

-LB

 

[arodri]

Hi Lb,
Here is my logic for the formula {@ItemNameParameter}:

IF {?Item Name} ="" then
{SalesItem.ItemName} else
lowercase ({SalesItem.ItemName})

- This is for the paramter "Item Name": if they leave it blank, then it returns EVERY record for "itemname" from the sales item table in the database. If not, then it lowercases the "ItemName" records in the SalesItem table.

*This formula is here so that I am able to reference it in my selection formula, which is:

{?Item Name} IN {@ItemName Parameter}

This way, with these two fromulas used, the user is able to input any part of an item name, and it will return all items that have what they typed in the name.
There is no picklist- users are just entering part of the item name.

*I was just using 'UW' as an example of what a user might type in. There is no need to have that in my formulas. The user could also type in 'Huma' and the results should include the items 'Humane Society', 'Humane Society $2'....and any other items with 'Huma' in the name.

I hope this clears things up...please let me know if it doesn't.
For now I will try to use the formula you provided for multiple parameters and let you know how that goes.

 

[arodri]

Lb-

Thank you sooooo much!! I used the formula you gave me and my "Item Name" parameter is working perfectly!

I guess I didn't need my other formulas :-/
I've only been using Crystal for a couple of weeks so I'm still really new to it.

Now that my "Item Name" Parameter works, I need to do the exact same thing to my "Section Name" parameter.

I just putting an "AND" and pasting the same formula you gave me (I obviously changed all the {?Item Name} to {?Section Name})

..is that the corect way to do this?

It seems to work....now my only question is how do I make it so that if users leave either/both of the field blank, it will return ALL of the data for those two columns?

That's how I had it before, but now that I'm not using my formula ({@ItemName Paramter}) I'm not sure how I would do that.
If I leave both fields blank it gives me the message "This value is not valid."

 

[lbass]

No, I can't see why that would work to return all records. If you want an option to return all records, I would tell the user to enter "All" to see all records, and then set up the record selection formula like this:

numbervar i;
numbervar j := ubound({?Item Name});
booleanvar flag := false;
for i := 1 to j do(
if (
lcase({?Item Name}) <> "all" and
lcase({SalesItem.ItemName}) like "*"+lcase({?Item Name})+"*"
) or
lcase({?Item Name}) = "all" then
flag := true
);
flag = true

-LB

 

[arodri]

Thanks so much for your help! You saved me hours of googling.

I kept with the original formula you gave me:
(numbervar i;
numbervar j := ubound({?Item Name});
booleanvar flag := false;
for i := 1 to j do(
if {SalesItem.ItemName} like "*"+{?Item Name}+"*" then
flag := true
);
flag = true)

I noticed that with just this formula, if I leave the parameter field blank and just press ">" (to place the 'blank' in the 'selected values' box) that it will return all of the values for ItemName.
This is what I wanted, but I need the users to not have to press ">" in order to move the blank over. I need the formula to somehow read that if the parameter is left completely untouched, then it will return all of the values for that field.
I have already tried the follwing:

 (numbervar i;
numbervar j := ubound({?Item Name});
booleanvar flag := false;
for i := 1 to j do(
if (
{?Item Name}[i] <> "" and
{SalesItem.ItemName} like "*"+{?Item Name}[i]+"*"
) or
{?Item Name}[i] = "" then
flag := true
);
flag = true)

I thought putting "" would allow the users to leave that field untouched, but I still need to press ">" even if it is blank.

Is this even possbile with multiple value parameters?
This was possible before I had allowed the ItemName to take in multiple parameters, but now it doesn't work.

 

[lbass]

CR XI requires that a value be selected for a parameter. I'm not sure why you are resisting asking users to enter "all" and using my earlier suggestion. I think it would make more sense to the user than just clicking on ">" with no entry.

-LB

 

[arodri]

Okay, thanks. That's what I was unsure about-whether CR XI required a value for a parameter.
I totally agree with you though- I will use your suggestion of "all" since this didn't work. It was my boss that wanted the user not to have to enter anything. If it was up to me I would have left it with them entering "all" from the beginning.

Thanks again for your help.