Help with paramater/formula please 2

[gillf]

My report is on contracts. I need to give the user a parameter to select from: All CONTRACTS, COMPLETED CONTRACTS ONLY, EXCLUDE COMPLETED CONTRACTS.
The field that controls this is {Ccs_Quotes.Ccs_Quo_Dec_Reason} which has a value 'FIN' for completed and 'WON' for uncompleted.

I created the parameter with the 3 options, then a formula:
IF {Ccs_Quotes.Ccs_Quo_Dec_Reason} = 'FIN'
then 'COMPLETED CONTRACTS ONLY'
ELSE if {Ccs_Quotes.Ccs_Quo_Dec_Reason} = 'WON'
THEN 'EXCLUDE COMPLETED CONTRACTS'
ELSE if {Ccs_Quotes.Ccs_Quo_Dec_Reason} in 'WON, FIN' then 'ALL CONTRACTS'
I then used a Select Expert on the field so that the parameter equalled the formula.
This returns no contracts whatever parmeter I choose.
I've tried reversing the formula so that
IF {?CompletedContracts} = 'COMPLETED CONTRACTS ONLY'
then 'FIN'ETC,
but this creates a Boolean True/False on the Select Expert which I can't think how to use.
Can someone help, please?

 

[lbass]

Try this:

(
{?ContractStatus} = 'COMPLETED CONTRACTS ONLY' and
{Ccs_Quotes.Ccs_Quo_Dec_Reason} = 'FIN'
) or
(
{?ContractStatus} = 'EXCLUDE COMPLETED CONTRACTS' and
{Ccs_Quotes.Ccs_Quo_Dec_Reason} = 'WON'
) or
{?ContractStatus} = 'ALL CONTRACTS'

-LB

 

[gillf]

Thank you for that. Still have a problem. I have now got the following in the formula @completedcontracts:
(({?Include Completed Contracts} = 'COMPLETED CONTRACTS ONLY' and {Ccs_Quotes.Ccs_Quo_Dec_Reason} = 'FIN')

or ({?Include Completed Contracts} = 'EXCLUDE COMPLETED CONTRACTS' and {Ccs_Quotes.Ccs_Quo_Dec_Reason} = 'WON'))

(A colleague advised to leave ALL CONTRACTS off this formula, as that should be the default if neither of the above are true. It makes no difference to the outcome anyway at the moment)

The SQL statement reads:

SELECT

Ccs_Quotes."Ccs_Quo_Quote_Number", Ccs_Quotes."Ccs_Quo_Ref_Number#2", Ccs_Quotes."Ccs_Quo_Company_Ref", Ccs_Quotes."Ccs_Quo_Date", Ccs_Quotes."Ccs_Quo_Address#1", Ccs_Quotes."Ccs_Quo_Dec_Reason", Ccs_Quotes."Ccs_Quo_User_Value#1", Ccs_Quotes."Ccs_Quo_User_Value#2",

CcsVwUnionActualCostsAndRevenue."Ccs_Doc_Date",

Ccs_Notes."Ccs_Not_Text#1"

FROM

{ oj ("Montrose_Building_Services_Ltd"."dbo"."Ccs_Quotes" Ccs_Quotes LEFT OUTER JOIN "Montrose_Building_Services_Ltd"."dbo"."Ccs_Notes" Ccs_Notes ON

Ccs_Quotes."Ccs_Quo_Note_Number" = Ccs_Notes."Ccs_Not_Number")

LEFT OUTER JOIN "Montrose_Building_Services_Ltd"."dbo"."CcsVwUnionActualCostsAndRevenue" CcsVwUnionActualCostsAndRevenue ON

Ccs_Quotes."Ccs_Quo_Quote_Number" = CcsVwUnionActualCostsAndRevenue."Ccs_Quote_Number"}

WHERE

Ccs_Quotes."Ccs_Quo_Date" >= {ts '2000-01-01 00:00:00.00'} AND

Ccs_Quotes."Ccs_Quo_Date" <= {ts '2006-02-14 23:59:59.00'} AND

Ccs_Quotes."Ccs_Quo_Ref_Number#2" >= '0000000000' AND

Ccs_Quotes."Ccs_Quo_Ref_Number#2" <= 'ZZZZZZZZZZ' AND

(Ccs_Quotes."Ccs_Quo_Dec_Reason" = 'FIN' OR

Ccs_Quotes."Ccs_Quo_Dec_Reason" = 'WON')

ORDER BY

Ccs_Quotes."Ccs_Quo_Ref_Number#2" ASC

The report works OK for COMPLETED CONTRACTS ONLY, but returns no data for ALL CONTRACTS or for EXCLUDE COMPLETED CONTRACTS
Also - I would expect to put a 'Select Expert' on the formula to make it equal to the parameter, but I only get the options of True/False/Formula (and it won't accept a formula...)

 

[lbass]

I don't know what you are doing. Go to report->selection formula->record and add my formula (with ALL clauses) to your existing selection criteria, putting parens around my formula like this:

(
(
{?ContractStatus} = 'COMPLETED CONTRACTS ONLY' and
{Ccs_Quotes.Ccs_Quo_Dec_Reason} = 'FIN'
) or
(
{?ContractStatus} = 'EXCLUDE COMPLETED CONTRACTS' and
{Ccs_Quotes.Ccs_Quo_Dec_Reason} = 'WON'
) or
{?ContractStatus} = 'ALL CONTRACTS'
)

This assumes that you have created {?ContractStatus} with the above three options.

-LB

 

[gillf]

Thanks, LBass. I don't know what I did wrong the 1st time, but I deleted parameter & formula & created them from scratch and all is well. Thank you for your patience and help