Having trouble writing mulitple parameter formula

[ShanPaul]

I have a report that uses 3 parameters to search by Order Number, Serial, and Item Number. The set up requires that the query fields all have a full value in them or a "*" in order for any data to be pulled. I need to set up a formula that will allow the user to run a partial query in one field and leave the other fields blank i.e. OrderNumber ?123? with generate all data that starts or contains "123" but they can leave the Serial and ItemNumber fields blank. Currently unless all fields have "*" in them the will not generate any data.

 

[lbass]

You need to identify the version of CR you are using.

-LB

 

[ShanPaul]

Sorry Crystal 10

 

[lbass]

I think there must be a selection for each parameter, even if irrelevant. The standard way to write a formula like the following. I wouldn't use "*" for a parameter as it is a wild card in CR; instead I would add an option "All" (or you could name it "Ignore" or "None"). Then set up the formula like this:

(
(
{?OrderNo} <> "All" and
{table.orderNo} = {?OrderNo}
) or
{?OrderNo} = "All"
) and
(
(
{?Serial} <> "All" and
{table.Serial} = {?Serial}
) or
{?Serial} = "All"
) and
(
(
{?ItemNo} <> "All" and
{table.ItemNo} = {?ItemNo}
) or
{?ItemNo} = "All"
)

If the parameters are number parameters, you would use 0 instead of "All". In that case, you might want to add a description field of "All" to the parameter picklist and set it up to display only the description field, while using the 0 in the value field.

You can explain to users in the prompt text that they should select "All" if they don't want to select specific value(s).

-LB

 

[ShanPaul]

Thank you I will give it a shot and see if I can get it to work. Thank you again for you help . I will let you know how it turns out.

 

[ShanPaul]

I am having trouble still. I added the OrderNo parameter and in the Select Expert I added the formula you gave me. Forgive me but I don't understand about where I am supposed to add the "All" option?

 

[ShanPaul]

Also when i prompt now for new parameters and I just enter a Serial number to search by it only populates the serial number data but the other data fields are blank.

 

[lbass]

If you are setting up picklists for your parameters, you would add "All" either as a value or description, depending upon the datatype. Please identify the datatype of each parameter before we proceed further.

If you not using picklists and users are expected to enter values manually, then direct them in the prompt text to enter "All" if they are not making a selection.

Please also paste the record selection formula you are using into the thread by going to report->selection formula->record and copying what you see.

-LB

 

[ShanPaul]

Users with be searching manually. Here is the record selection formula I have in place:
{?ItemNumber} <> "All" and
{Item_Detail.PRODUCT} = {?ItemNumber}
or
{?ItemNumber} = "All" and {?Serial} <> "All" and
{Item_Detail.SER} = {?Serial}
or
{?Serial} = "All"
and{?OrderNo} <> "All" and
{Item_Detail.OrderNumber} = {?OrderNo}
or
{?OrderNo} = "All"
Now while this formula is allowing the user to search by a specific data field, it is only populating information for that specific search field and leaves the other fields blank.

 

[lbass]

This is not set up correctly. The parens are NOT optional. Please set up your formula exactly as I showed, just substituting the actual field names.

-LB

 

[ShanPaul]

Ok it is running correctly now . Thank you so much for your help and being patient with my ignorance. Much appreciated.