How to treat no data, null values, empty field for parameter prompts?

[pectin232]

I am fixing some crystal reports which has a few parameter prompts for certain case numbers and data ranges. If the user does not fill in any data it will crash crystal reports. How do I allow no values or no data in the parameters and insert a sysdate if no value is entered in the prompt? Thanks

 

[IanWaterman]

The easiest way is to all a default, for a string use 'All' numbers use 0

In select expert you then modify use to be like

(If YourParam <> 'All' then yourfield = yourparam else true)

Wrap in () so you can use with other similar filters.

Ian

 

[pectin232]

I have a command in the SQL itself now...but I am not sure how to encapsulate it in Select Expert. The 2 parameters are

if isnull({?caseNumber}) then ...... not sure if this is what you mean? Thanks

 

[IanWaterman]

Never used such parameters in a command.

If you try to use in Select expert I think command will bring back all data to report and then do filtering locally which will be very slow.

You might be able to use a case statement in conjunction with parameters in a command but off the top of my head I can not think how you would do that.

Ian

 

[pectin232]

I have this now but did not work.. this is also one of the several parameters. None work... not sure if I should use the NVL function..

If isnull({?Additional_Workers})=True then {?Additional_Workers}= (0,0

or

If isnull({?Additional_Workers})=True then {?Additional_Workers}= {0,0}

or
If isnull({?Additional_Workers})=True then {?Additional_Workers}= 0

 

[IanWaterman]

Where are you writing this in select or in Command?

What database are you using?

Ian

 

[beltmanjr]

If you would use 'ALL' as a default in your parameters prompt you could apply the following in the select expert:

(
    {?Additional_Workers} = 'ALL' // this will be true when selecting ALL from the parameter prompt
    or
    {?Additional_Workers} ={FIELDNAME}
)

Your solution

If isnull({?Additional_Workers})=True then {?Additional_Workers}= 0

Doesnt seem to make much sense. I'd have expected something like

If isnull({?Additional_Workers}) then TRUE
else {?Additional_Workers} ={FIELDNAME}

Or something like that