Using Parametres to exclude data froma select

[robbclaxton]

I wish to use a parameter to exclude data from my select query the problem is handling a null value..for example

I wish to offer the user the option to select from a list items that they do not want to be included or leave blank to include all records..

How would I write this into my select records ?

 

[IanWaterman]

Add a default value to your Parameter "All"

In select

(if minimum(param) <> 'All' then yourfield <> param else true)

Ian

 

[HowardHammerman]

You can also use the ISNULL() function and use the following:
if isnull(param) then true else table.field=param


Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[robbclaxton]

Howard are you sure that that syntax is correct as I am getting @ the remaining text does not appear to be part of the formula.

my syntax


{CUSTOMER_SITES.SITE_CUST_ID} = {?Customer} and
{ALARMS_INCOMING.ALI_VRSC_RCD_TIME} in {?Start Date} to {?End Date} and
isnull({?Alarm Removal})then true else {ALARM_TYPES.ALT_TYPE_DESC}={?Alarm Removal}

 

[HowardHammerman]

Try this:

{CUSTOMER_SITES.SITE_CUST_ID} = {?Customer} and{ALARMS_INCOMING.ALI_VRSC_RCD_TIME} in {?Start Date} to {?End Date} and IF isnull({?Alarm Removal})then true else {ALARM_TYPES.ALT_TYPE_DESC}={?Alarm Removal}

NOTE THE ADDITION OF THE WORD "IF"

sorry about that. Make sure you have NOT clicked "set null values to default" in the report options screen.

This may not work with all databases.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[fishymetrics]

You could make an "ALL" default for ?Customer and a "NONE" default for ?Exclude_Customer and then they could choose All, include some,or only exclude some.
({CUSTOMER_SITES.SITE_CUST_ID} = {?Customer} or {?Customer} = "ALL") and
({CUSTOMER_SITES.SITE_CUST_ID} <> {?Exclude_Customer} or {?Exclude_Customer} = "NONE")

you can display the choices with:

Multi-Parameter print formula
//This formula outputs a string which has
//a maximum value of 255 characters.
//for CR 8.5 and lower, and 64K characters in CR 9.
//Multi is the name of the multiple value parameter.

numbervar counter;
numbervar final;
stringvar holder;
counter := count({?Multi});
holder := "";
while counter > 0 do
(holder := {?Multi}[counter] + ',' + holder;
counter := counter - 1);
holder [1 to (length(holder)-1)]
//The above formula line removes the trailing ',' from the string