Parameters - how to make optional?

[Rob22]

I'm using CR 8.5 and am having trouble writing a formula for a parameter. I'd like to give the user the ability to run the report for a particular record or for all records.

Here is the standard formula:
{purchase_order_segment_ship.status_cd} in ["C", "O"] and
{purchase_order.po_number} = {?Purchase Order Number}

This requires the user to enter a Purchase Order Number, but I want it to be optional. I tried putting a default value of 0, and then testing the expression like:
{purchase_order_segment_ship.status_cd} in ["C", "O"]
If {?Purchase Order Number} <> 0 Then
and {purchase_order.po_number} = {?Purchase Order Number}

This doesn't work, and CR doesn't let me put the &quot;and&quot; on the line under the IF Then. It will only accept the formula if I put the &quot;and&quot; before the IF THEN, but then it doesn't work.

Can someone shed some light???

Thanks is advance,
Rob

 

[synapsevampire]

I'd prepopulate the parameter list with 0 as the first choice, and then add something akin to this to your record selection criteria:

(
If {?Purchase Order Number} <> 0
then
({purchase_order.po_number} = {?Purchase Order Number})
else
if
{?Purchase Order Number} = 0
then
True
)

and

(
{purchase_order_segment_ship.status_cd} in [&quot;C&quot;, &quot;O&quot;]
)

(This allows for your other criteria which states that it will only return rows where status_cd in C or O.

If you want to allow for multiple purchase orders, and the default of 0 is the first in the list, use:

(
If {?Purchase Order Number}[1] <> 0
then
({purchase_order.po_number} in {?Purchase Order Number})
else
if
{?Purchase Order Number}[1] = 0
then
True
)

and
(
{purchase_order_segment_ship.status_cd} in [&quot;C&quot;, &quot;O&quot;]
)

I intentionally form this SQL this way with the reverse qualification to try to ensure that CR will pass the SQL to the database, it looks awkward, but it generally provides the best chance at pass through (increased performance).

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Rob22]

Thanks a bunch! It worked like a charm. I really appreciate it.

Rob