Question Re: NULL Value in Crystal Reports Parameter in Technology Q&A

NULL Value in Crystal Reports Parameter

Former Member — Fri, 22 Oct 2010 13:31:40 GMT

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.

Re: NULL Value in Crystal Reports Parameter

Former Member — Fri, 22 Oct 2010 15:06:32 GMT

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)

Re: NULL Value in Crystal Reports Parameter

Former Member — Fri, 22 Oct 2010 16:29:59 GMT

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.

Re: NULL Value in Crystal Reports Parameter

Former Member — Fri, 22 Oct 2010 17:31:58 GMT

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.

Re: NULL Value in Crystal Reports Parameter

Former Member — Fri, 22 Oct 2010 17:53:05 GMT

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

Re: NULL Value in Crystal Reports Parameter

Former Member — Fri, 22 Oct 2010 18:42:31 GMT

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

Re: NULL Value in Crystal Reports Parameter

Former Member — Sat, 23 Oct 2010 04:47:50 GMT

I hadn't noticed that : D

Re: NULL Value in Crystal Reports Parameter

Former Member — Tue, 26 Oct 2010 14:12:47 GMT

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.

Re: NULL Value in Crystal Reports Parameter

Former Member — Tue, 23 Aug 2016 11:29:14 GMT

Dear,

Use this formula

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