Selection Formula for 3 cases 2

[blueboyz]

I'm using Crystal Reports 2008 with Peachtree 2011, a pervasive sql database engine.

I created a report to list sales orders and invoices with inventory item information.

I want the report to pull open sales orders, closed sales orders or both depending on what the user enters in the sales order status parameter.

Peachtree has a field, JrnlHdr.POSOisclosed. This field has a 0 if the sales order is open or a 1 if the sales order is closed. I have a formula {@SO Status} = If {JrnlHdr_SO.POSOisClosed} = 1 then "Closed" else "Open".

The parameter {?Sales Order Status} is setup with the values:
Both
Open
Closed

The Value Options of the parameter include:
Prompt with Description Only: False
Optional Prompt: True
Default Value: blank
Allow Custom Values: True
Allow Multiple Values: False
Allow Discrete Values: True
Allow Range Values: False
No Minimum or maximum length
No Edit Mask

I need help with the selection formula.
The formula currently reads:

and
(not HasValue({?Sales Order Status}) or (If {?Sales Order Status} = "Both" then True
else (If {?Sales Order Status} = "Open" then {JrnlHdr_SO.POSOisClosed} = 0))) or
else (If {?Sales Order Status} = "Closed" then {JrnlHdr_SO.POSOisClosed} = 1))

I get an error (with the cursor highlighting the last "else"):
A number, currency amount, boolean, date, time, date-time, or string is expected here.

How can I fix the selection formula so if the user selects "Open" only the open sales orders appear, or if the user selects "Closed" only the closed sales order appear or if the user selects "Both", all sales orders appear on the report?

Thank you in advance for your help.

 

[MCuthill]

BlueBoyz,

I believe parathesis is the cause of the error. I have not used "HasValue" - I swapped "not HasValue" with "IsNull".

Please try the following and advise, I am unsure if I have achieved what you are seeking.

[green]~~Assumed Other Criteria~~[/green]
[blue]AND[/blue]
(
[blue]IF IsNull[/blue]({?Sales Order Status}) or {?Sales Order Status} = "Both" [blue]THEN True ELSE[/blue] 
(
[blue]IF[/blue] {?Sales Order Status} = "Open" [blue]THEN[/blue] {JrnlHdr_SO.POSOisClosed} = 0
) [blue]ELSE[/blue] 
(
[blue]IF[/blue] {?Sales Order Status} = "Closed" [blue]THEN[/blue]  {JrnlHdr_SO.POSOisClosed} = 1
)
)

Hope this helps! Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[briangriffin]

Since you're entering the values of Both, Open, and Closed, and there are no other possibilities, you don't really need to use an optional prompt here.

Try changing Optional Prompt to false, Allow Custom Values to false, and select "Both" from the Default Value dropdown. Then your selection formula becomes:

if {?Sales Order Status} = "Both" then true
else
if {?Sales Order Status} = "Open" then {JrnlHdr_SO.POSOisClosed} = 0
else
{JrnlHdr_SO.POSOisClosed} = 1

 

[blueboyz]

Thank you, both. Your answers let me on the right path. I want to use the "Not HasValue" because I don't want the user to have enter anything in the parameter, only if they need to.

Here's what the selection formula ended up as (and it works like a charm thanks to you both for your replys):

(not HasValue({?Sales Order Status}) or If {?Sales Order Status} = "Both" then True
else If {?Sales Order Status} = "Open" then {JrnlHdr_SO.POSOisClosed} = 0
else {JrnlHdr_SO.POSOisClosed} = 1)