Crystal Reports Custom IsNull Function

Question

I am trying to create a custom IsNull Function in Crystal Reports; the function must act the same way as the IsNull Function in MS SQL Server. I want to specify a field, and if the field is null, then it must be returned with a value I have specified.

IsNull({myField},0) or
IsNull({myField},'Hello World') 

I have encountered that I have to create a separate function for number fields and a separate function for text fields. I also found that Crystal does not allow the use of standard functions inside of a custom function, for instance the ISNULL Function:

Function(NumberVar param, Numbervar setter)
IF ISNULL(param) THEN setter ELSE param

and

Function(StringVar param, StringVar setter)
IF param = NULL THEN setter ELSE param

Does anyone know how I can create a function like this in Crystal and a work around for the ISNULL inside of a custom function?

Solution 1

You can't pass a null value into a custom function, so it's pointless to use crystal's isnull function inside one. Only option is to write it like...

if isnull({myField}) then 0 else {myField}

Solution 2

I found this issue, in the formula editor there is a drop down in the header that indicates:

• Exception for Nulls
• Default values for nulls

Select the second one ( Default values for nulls)

 

Solution 3

I've encountered the same behavior, but I have yet to see a documented reason for this.

I would suggest that you use a SQL Expression:

//{%myField}
(
  ISNULL({myField},'Hello World') 
)

Solution 4

This worked for me:

if  (isnull({dbvalue}) or ({dbvalue} ='')) then 
       "Display the required text"
else
       {dbvalue}

View more solutions