Filtering on NULL in CR

[Former Member]

I really hate to ask such a simple question here but I have searched the forum, bought books, and tried to get help from SAP Support without any luck. I've tried all of what seem to be the obvious choices such as search on NULL, empty string ("" or '' ) and nothing is working. I've also tried using string functions as opposed to just using the Select Expert with the same results. If anyone can point me in the right direction it would be much appreciated. And if there is a more appropriate forum or online resource I would love to know about it (I thought buying "Crystal Reports 2008, The Complete Reference" would have sufficed, but I can't find anything in there on testing for an empty field, which I find incredible).

[former_member292966 · Accepted solution]

Hi Daren,

How you handle NULLs depends on the type of data you are working with and Crystal.

Usually, I go into Crystal and go into File | Options. In the Reporting tab are two options:

Convert Database NULL Values to Default and Convert Other NULL Values to Default.

Put a check mark in both and Crystal will handle the nulls depending on the type of the field. So a NULL numeric field will be 0 and a NULL string field will be a space. This helps and makes things a bit easier sometimes.

If you do not want this option, then have the report use the ISNULL function in a formula to check for a NULL before evaluating.

If ISNULL ({table.FIELD}) Then ...

For dates, instead of ISNULL have it check for a zero date like:

If {table.DATEFIELD} = Date (0, 0, 0) Then ...

Hope this helps,

Brian

[Former Member · Accepted solution]

Thanks Brian. I am working with a string field at the moment. I had already tried setting the NULL handling preferences as you mentioned, but the text I am using as a reference indicates that this treats NULL as an empty string rather than as a single space character string. In any case, after doing this when I tried matching records with no value in the string field by comparing it to an empty string ("") or a single space (" "), I still was not able to isolate the records I was looking for. Using the IsNULL() function however worked perfectly. Thanks again.

[Former Member]

To filter, I take it as you want to include records where a particular field is populated.

Let's say you want to retrieve information from table Customer and the Country field is not always populated.

In Crystal Report, you go to Report> Selection Formulas> Record and put in the following:

NOT(ISNULL(Customer.Country) or Trim(Customer.Country)='')

Note that for you to go through Report> Selection Formulas> Record is the same as using Selection Expert and click on the button 'Show Formula'

[Former Member]

Thank you! I was just about to post that I had found the IsNULL function when I saw your response. I posted this question after I tried every string function listed in the Formula Editor. It seems strange to me that IsNull is not listed there. Thanks again for your help. I've been trying to solve this off and on for months!