parameters in selection formulas 1

[charlieintegral]

Hi,

I have a number of parameters in the selection formula, but want the end user to be able to use all or as little as one parameter when entering the report. I can't quite seem to be able to use my little knowledge of SQL to make this work, it doesn't wnat to know when I exchange AND for OR. have any of you any ideas?

e.g.

{ACTIVE_SEPARATE.WO_NO} = {?WORK ORDER NO}
AND {ACTIVE_SEPARATE.MCH_CODE_CONTRACT} like {?area}
AND {ACTIVE_SEPARATE.COST_CENTER} like {?site }
AND {ACTIVE_SEPARATE.STATE} like {?status}
AND {ACTIVE_SEPARATE.PLAN_S_DATE} = {?date range}
AND {ACTIVE_SEPARATE.VENDOR_NO} like {?Contractor Number}

 

[Madawc]

Create a set of formula fields, 'boolians' which return true or false and can be included in a selection by just including the formula field name.

For @Area, you might enter

{?area} = "" 
{ACTIVE_SEPARATE.MCH_CODE_CONTRACT} like {?area}

And so on for the other tests. In selection,

{ACTIVE_SEPARATE.WO_NO} = {?WORK ORDER NO}
AND @Area

Include all of the other tests. Use zero rather than space for a numeric code.


Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[lbass]

We need to know the datatype of each, but let's pretend that the work order is a number, and the state is a string in the following example. You can expand it to the other criteria.

(
if {?WORK ORDER NO} <> 0 then
{ACTIVE_SEPARATE.WO_NO} = {?WORK ORDER NO} else
if {?WORK ORDER NO} = 0 then
true
) and
(
if not({?status} = "ALL") then
{ACTIVE_SEPARATE.STATE} like "*"+{?status}+"*" else
if {?status} like "ALL" then
true
) and
(
if {?date range} <> date(1900,1,1) then
{ACTIVE_SEPARATE.PLAN_S_DATE} = {?date range} else
true
)
//etc.

If you are using like and you want to pick up an instance within a set of text, I think you have to add the asterisks.

You need to add the "0","All", and the default date in the parameter setup screen as the default values.

-LB