Parameter Searches 2

[Garyjr123]

I have yet another report I would like help with.

Basically, I want to be able to search on three different parameters if I DON'T have one or the other. The selection expert formula below says that I need to have all 3 parameters (mfrcatnum, lotno, and serialno) to bring back data:

toNumber({caseproitemlist.is_smda}) = toNumber({@smda}) and
{caseproitemlist.consumed_qty} > 0.00 and
((Minimum({?mfrid}) = 0) or {mfr.mfr_id} in {?mfrid}) and
((Minimum({?devtypeid}) = 0) or {devicetype.devicetype_id} in {?devtypeid}) and
{casemain.resunit_id} in {?resunitid} and
{casemain.actcase_start_datetime} in {?frmdt} to {?todt} and
{caseproitemlist.surgsuptype_id} = 3.00 and
((Minimum({?serviceid}) = 0) or {casepro.pro_service_id} in {?serviceid}) and
(isnull({caseproitemlist.mfr_cat_num}) or ({caseproitemlist.mfr_cat_num} like {?mfrcatnum})) AND
(isnull({caseproimplant.lotnumber}) or ({caseproimplant.lotnumber} like {?lotno})) AND
(isnull({caseproimplant.serialnumber}) or ({caseproimplant.serialnumber} like {?serialno})) AND
((Minimum({?surgeonid}) = 0) or {casepro.primpract_res_id} in {?surgeonid})

If I change the 'AND' to 'OR' then will I be able to use one or all to narrow my search?

toNumber({caseproitemlist.is_smda}) = toNumber({@smda}) and
{caseproitemlist.consumed_qty} > 0.00 and
((Minimum({?mfrid}) = 0) or {mfr.mfr_id} in {?mfrid}) and
((Minimum({?devtypeid}) = 0) or {devicetype.devicetype_id} in {?devtypeid}) and
{casemain.resunit_id} in {?resunitid} and
{casemain.actcase_start_datetime} in {?frmdt} to {?todt} and
{caseproitemlist.surgsuptype_id} = 3.00 and
((Minimum({?serviceid}) = 0) or {casepro.pro_service_id} in {?serviceid}) and
(isnull({caseproitemlist.mfr_cat_num}) or ({caseproitemlist.mfr_cat_num} like {?mfrcatnum})) OR
(isnull({caseproimplant.lotnumber}) or ({caseproimplant.lotnumber} like {?lotno})) OR
(isnull({caseproimplant.serialnumber}) or ({caseproimplant.serialnumber} like {?serialno})) and((Minimum({?surgeonid}) = 0) or {casepro.primpract_res_id} in {?surgeonid})

Is there a better way to do this? Would this work:
If I created a single parameter of {?mfrseriallotno} and then applied this way:

toNumber({caseproitemlist.is_smda}) = toNumber({@smda}) and
{caseproitemlist.consumed_qty} > 0.00 and
((Minimum({?mfrid}) = 0) or {mfr.mfr_id} in {?mfrid}) and
((Minimum({?devtypeid}) = 0) or {devicetype.devicetype_id} in {?devtypeid}) and
{casemain.resunit_id} in {?resunitid} and
{casemain.actcase_start_datetime} in {?frmdt} to {?todt} and
{caseproitemlist.surgsuptype_id} = 3.00 and
((Minimum({?serviceid}) = 0) or {casepro.pro_service_id} in {?serviceid}) and
(isnull({caseproitemlist.mfr_cat_num}) or ({caseproitemlist.mfr_cat_num} like {?mfrseriallotno})) or
(isnull({caseproimplant.lotnumber}) or ({caseproimplant.lotnumber} like {?mfrseriallotno})) or
(isnull({caseproimplant.serialnumber}) or ({caseproimplant.serialnumber} like {?mfrseriallotno})) and
((Minimum({?surgeonid}) = 0) or {casepro.primpract_res_id} in {?surgeonid})

Thanks,

Gary

 

[lbass]

You need to clarify the logic better. Let's say your records could look like this:

cat_num lot_no serial_no
row1 null null null
row2 10 null null
row3 null 11 null
row4 null null 12
row5 13 14 null
row6 15 null 16
row7 null 17 18
row8 19 20 21

For row8, does each value have to match a parameter or only one? If there are two values as in rows 5 to 7, do both values have to match or only one?

-LB

 

[Garyjr123]

LB,

I just want to match one of the parameters so if I only have a lot number then I search on the lot number but nothing else or if I have the lot number and the serial number then I can search on them both. Basically, I want to be able to search on one of the parameters but have the option to search on more if I have the info but not make any of the parameters required.

Row8: I can search 19 or 20 or 21 or I can search 2 of 3 or all 3.
Row5 & 7: I can search on either parameter provided like 13 or 14 or 15 or 2 of 3 or all.

Does this make sense?

Gary

 

[lbass]

So you are always only looking for at least one match per row? If you are using XI, parameters are not optional. Your logic would that of your second formula except you should build in criteria that your user can use to basically make the parameter have no impact, as in:

(
(
{?mfrcatnum} <> 0 and
(
isnull({caseproitemlist.mfr_cat_num}) or
{caseproitemlist.mfr_cat_num} like {?mfrcatnum}
)
) or
{?mfrcatnum} = 0
) or
(
(
{?lotno} <> 0 and
(
isnull({caseproimplant.lotnumber}) or
{caseproimplant.lotnumber} like {?lotno}
)
) or
{?lotno} = 0
) or
(
(
{?serialno} <> 0 and
(
isnull({caseproimplant.serialnumber}) or
{caseproimplant.serialnumber} like {?serialno}
)
) or
{?serialno} = 0
)

If the parameters are strings, change the 0's to "All". Instruct the user to select 0 (or all) if they don't want the parameter to apply. If you are using picklists, add the 0 or all to the lists.

-LB

 

[Turkbear]

Hi,
LB, every time I see your handling of mixed AND/OR/NULL parameter checking I marvel anew..Great response and a star for you.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Garyjr123]

LB,

This doesn't seem to limit my report to just the one parameter if entered. For example, I entered All for {?mfrcatnum} and {?serialno} and 331165 which the report returned 4835 records instead of the 5 records I see when I search in SQL manager. In other words, it still is returning all the records from the table.

Here is the new record selection I'm using:

{caseproitemlist.consumed_qty} > 0.00 and
{casemain.actcase_start_datetime} in {?frmdt} to {?todt} and
{caseproitemlist.surgsuptype_id} = 3.00 and
(({?mfrcatnum} <> 'All' and (isnull({caseproitemlist.mfr_cat_num}) or
{caseproitemlist.mfr_cat_num} like {?mfrcatnum})) or {?mfrcatnum} = 'All') or
(({?lotno} <> 'All' and (isnull({caseproimplant.lotnumber}) or
{caseproimplant.lotnumber} like {?lotno})) or
{?lotno} = 'All') or (({?serialno} <> 'All' and (isnull({caseproimplant.serialnumber}) or
{caseproimplant.serialnumber} like {?serialno})) or {?serialno} = 'All')

Ideas?

 

[lbass]

Explain what you want to happen with null records. I thought you always wanted to include records with nulls.

-LB

 

[Garyjr123]

LB,

I don't want to see any records that don't have at least one of the parameters I asked for (mfrcatnum, serialno, or lotno) so if there are records with all three parameters that have null values then I don't want to see them.

 

[lbass]

Try the following:

(
(
{?mfrcatnum} <> 0 and
{caseproitemlist.mfr_cat_num} like {?mfrcatnum}
) or
{?mfrcatnum} = 0
) or
(
(
{?lotno} <> 0 and
{caseproimplant.lotnumber} like {?lotno}
) or
{?lotno} = 0
) or
(
(
{?serialno} <> 0 and
{caseproimplant.serialnumber} like {?serialno}
) or
{?serialno} = 0
)

-LB

 

[Garyjr123]

LB,

I am using a string so I changed the '0' to 'All' but I am still having the same issue. This formula returned over 3500 records instead of 5. I am just not sure what I am doing wrong.

Record Selection:

toNumber({caseproitemlist.is_smda}) = toNumber({@smda}) and
{caseproitemlist.consumed_qty} > 0.00 and
((Minimum({?mfrid}) = 0) or {mfr.mfr_id} in {?mfrid}) and
((Minimum({?devtypeid}) = 0) or {devicetype.devicetype_id} in {?devtypeid}) and
{casemain.resunit_id} in {?resunitid} and
{casemain.actcase_start_datetime} in {?frmdt} to {?todt} and
{caseproitemlist.surgsuptype_id} = 3.00 and
((Minimum({?serviceid}) = 0) or {casepro.pro_service_id} in {?serviceid}) and
(({?mfrcatnum} <> 'All' and {caseproitemlist.mfr_cat_num} like {?mfrcatnum}) or {?mfrcatnum} = 'All') or
(({?lotno} <> 'All' and {caseproimplant.lotnumber} like {?lotno}) or {?lotno} = 'All') or
(({?serialno} <> 'All' and {caseproimplant.serialnumber} like {?serialno}) or {?serialno} = 'All') and
((Minimum({?surgeonid}) = 0) or {casepro.primpract_res_id} in {?surgeonid})

Gary

 

[Garyjr123]

LB,

You are a genius and it is working. Don't ask me why or how but that last selection formula works perfectly (ignore my last post).

Thank you so much.

Gary