NULL Value in Crystal Reports Parameter

[Former Member]

I have trouble getting a parameter work in Crystal Reports. I have defined a Parameter variable in the report. The parameter should restrict the data if a value is entered or it has to bring back all the data if the Value is NULL. In the SELECT EXPERT I have a condition that says IF ISNULL (Parameter_FIELD) then a Default Value.

When I run the report I get an error saying that the Parameter has NULL Value. Even If I default it with some Value the report is not working the way we want to. Is there a way I can do what I am trying to achieve. Please advice.

[Former Member]

Dear,

Use this formula

(not HasValue({?My Parameter}) OR {TABLE.FIELD} = {?My Parameter})

[Former Member]

I'm not sure how this is even an issue... The user shouldn't be able to leave a parameter empty. CR won't close the parameter screen until all parameters have a value.

[Former Member]

Hi Jason

I have an optional parameter that can be left empty. It is only printed on the report though and never used in the data selection.

Debi

[Former Member]

Huh... well it appears that String type parameters can be left blank... But it wouldn't let me leave any other parameter type blank... Including dynamic.

Jason

[Former Member]

I hadn't noticed that : D

[Former Member]

Here is what I found. Any numeric cannot be blank in Crystal Reports. We have to create a Non Numeric Parameter.

In the following example ARG_SRC_ID is a non numeric parameter. By default it will prompt the user with % which means that if the user enter % sign it will fetch all the data.

Since we are defining the ARG variable as Numeric I have to change the non numeric to Numeric format using TONUMBER function. Hope this helps.

IF {?ARG_SRC_ID} = "%" then

{SRC_AUD.SRC_ID} = {SRC_AUD.SRC_ID} else {SRC_AUD.SRC_ID} = tonumber ({?ARG_SRC_ID})

Crystal reports should come up with better way to handle this. This should not be very tricky.

[Former Member]

You could pick a value to represent a null. 'Null' would work if it is a text parameter.

Now create a formula {@null}

If parameter ="null" then 1 else 0

In your select expert use

({@null}=1 or field=parameter)

[Former Member]

you could also try

if isnull(parameter) or (parameter)="" then true else

=

the null might not be working because your database blank spaces arent defined as null but as blank ""

go to file

opition

make sure you check convert null values check boxes.