ISInc Navigation

Record Selection and the Null Value Bug in Crystal Reports

One of the interesting issues I have found when building reports in Crystal is the nature of their record selection on blank data.

Let’s say, for example, I am building a report that displays my product names and sizes.

I don’t want to see any of the extra small (xsm) products so I add record selection to check if the size is not equal to “xsm”.

Formula:

{Product.Size} <> “xsm”

When I go back to my report, not only are all the extra small products missing, but so are all the products that didn’t have a size at all. This is normal database behavior that you might not be familiar with. Any time a database checks a null field (a blank field) against anything, it will always result in a value of false.

Thus, the following comparison results in a false.

“California” = Null

But, so does

“California” <> Null

Crystal will omit any records where the record selection criteria returns false, thus we will not see the products that don’t have a size. So how do we get those blank values back? Let’s add to our selection criteria a condition that checks for null values. Your new formula will use the IsNull() function to check to see if the size field is blank.

New Formula:

{Product.Size} <> “xsm” or IsNull({Product.Size})

Unfortunately, when preview your report, you will find that this doesn’t work. That is not because of a mistake in our logic but rather, what I consider to be a bug in Crystal Reports. If I took this exact same condition and applied it to the database records using a query analyzer or querying tool, I would see the blank records. Unfortunately, Crystal is not allowing the null values to come through even though our formula says that they should.

The trick to circumventing this bug is to put the IsNull() check FIRST in the formula.

Thus, if we rearrange the condition to this:

IsNull({Product.Size}) or {Product.Size} <> “xsm”

We will get our all of our products that aren’t extra small, including the products that have no value filled in for size.

As a rule of thumb, when using IsNull() in record selection criteria, make sure that it comes first in the formula.

That is really fascinating, You’re an excessively skilled blogger. I’ve joined your rss feed and stay up for in search of more of your great post. Additionally, I have shared your site in my social networks

tried your isnull formula to select just the null values for a field on my report and it returns zero records, it appears the report just locks up. We have several other criteria, after those selections i input the isnull formula as follows:
isnull ({fbdhr.HDR_RATE_TARIFF}) or {fbhdr.HDR_RATE_TARIFF ‘ALCAL’

am I stating it wrong or ar there other options to accomplish selecting the blank or “null” value records?

Hi,
Thanks for the post.
I am having a requirement where I have to sort records by total in desc order. In our DB itself we are having NULL in total column. In crystal report if i sort based on total desc, then NULL records are coming in the first, how to display the NULL records at the last.

I can think of creating a SQL command for it, but without creating command, is there a way to achive this?

Thanks for sharing this information but must disagree with statement “Any time a database checks a null field (a blank field) against anything, it will always result in a value of false.” A test against a null in my experience returns NULL, and that is considered ‘normal’ behavior.. It is neither true nor false. The NULL test must preceed the target logic as discussed.