Parameter help one record or a range of

[muffntuf]

Hi,

Using CR10

I need to able to select a part number, which is a string field, with a partial starting number or the whole number and they want to be able to put a range of values in the same parameter:

Part Number
151127-00-000
151129-00-000

If somone just types in 15112 then I need all records to come back with that starting.

Or if they choose 151127 to 151129 then the report needs to be able to select from that.

Any suggestions?

Thanks,
muffntuf

 

[synapsevampire]

Make the parameter a range type, and set the default value to " " (a space).

In the Report->Record Selection->Record place something like:

if isnull({?part})
or
trim(maximum({?Part})) = ""
then
{Customer.part} like "*"& minimum({?Part}) & "*"
else
if {?Part} = "2" then
{Customer.part} >= minimum({?Part})
and
{Customer.part} <= maximum({?Part})

So if they only enter one value (the lower range), it will search for one, if they enter the full range, it will search for >= the first value entered and <= the second value.

This will also pass the SQL to the database so performance should be fine.

-k

 

[muffntuf]

K

If they need all of the records to come back do I still need to add an 'all' statement somewhere in the mix?


{?Part Number} = "All" or {@Part Number} ={?Part Number}

 

[muffntuf]

This is what I put in, if I choose the "" I get all records back, if I choose a range with just a couple numbers off the front end of the part number I get zilch.
Here's what I did:
(if isnull({?Part Number})
or
trim(maximum({?Part Number})) = ""
then
{@Part Number} like "*"& minimum({?Part Number}) & "*"
else
if {?Part Number} = "2" then
{@Part Number} >= minimum({?Part Number})
and
{@Part Number} <= maximum({?Part Number}))

 

[muffntuf]

Oh and the part number is formulated like this because of the paritial bit,

trim({@CommonPartNum}) [1 to 6]

 

[lbass]

The following seems to work for a string parameter that allows multiple discrete or range values, with a default value of "All":

if {?partno} <> "All" then
(
{table.partno} = {?partno} or
left({table.partno},5) = {?partno}
) else
if {?partno} = "All" then true

This assumes people are instructed to enter either discrete values with five digits or a range with all digits.

-LB

 

[muffntuf]

LB yes I agree, but if they want a to enter a partial number because we are talking about warehouse folks that don't know part numbers, but maybe the first one or two numbers, they want to be able to pull a range based of the partial selection.

Make sense, any suggestions?

Thanks,
muffntuf

 

[synapsevampire]

I still didn't understand why my solution doesn't work, it works fine here.

-k

 

[muffntuf]

I am not trying to offend you, but I put this in the select expert and if I choose "All" it works, if I choose a parital number it doesn't. So I don't understand what I am doing wrong.

That's why I am asking for suggestions.


Thanks,
Muffntuf

 

[lbass]

And did you try my suggestion? Although it uses a specific number of characters for the discrete values, if someone entered 11456 as a discrete value, then all part numbers starting with 11456 would appear on the report, along with any ranges that they provided using full numbers.

-LB

 

[muffntuf]

Is this because we are assuming this is a number field? I know synapsevampire gave an example that should go against a string field, but maybe that is what the deal is?

muffntuf

 

[lbass]

Who are you addressing? My example assumes a string parameter and that the part number is a string.

-LB

 

[synapsevampire]

Not sure waht you mean by putting it in the select expert, but you should avoid the select expert.

Use the Report->Selection Formulas->Record

My example used a canned report from Crystal that works.

-k