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.


Comments

  1. this method has fixed my issue

    tas

  2. Brian Kleppinger Says:
    October 24th, 2011 at 1:49 pm

    Thanks for the tip. I was pulling my hair out trying to include blanks and exclude other data. This worked perfectly.

  3. Thanks for the posting! I’m fairly new to Crystal Reports and I’ve had this problem on several reports and couldn’t figure out why it wouldn’t work.
    The change in order fixed it.

  4. [...] I have found when building reports in Crystal is the nature of their record selection on blank data.http://www.isinc.com/2008/10/10/record-selection-and-the-null-value-bug-in-crystal-reports/ Share this:TwitterFacebookLike this:LikeBe the first to like this post. Tags crystal, null, [...]

  5. *very nice post, i certainly love this website, keep on it

  6. Your post is appreciated. The counsel regarding putting the IsNull test before the “actual data comparison” test was particularly helpful.

  7. 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

  8. Thank you! This solved a VERY frustrating problem I was having!

  9. Thanks for the tip … this also helped solved a similar problem that I was experiencing …

  10. My question however becomes … what if we have more than just one field in the data selection formular i.e.

    e.g. (Product.Qty >= 10) and (Product.Size ‘xsm’)

    Does that mean we have to use implementations such as :


    ( ( IsNull(Product.Qty) ) or ( Product.Qty >= 10 ) )
    and
    ( ( IsNull(Product.Size) ) or ( Product.Size ‘xsm’ ))

    if the answer is yes … then how am I ever going to be sure that my report is selecting only the correct kind of data that I want.

  11. This worked great! Thought I was going nuts. Keep posting. Big Thanks!

  12. It’s amazing how simple the answer is! Thanks!!

  13. Thank you!

  14. 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?

  15. Warren,

    Try using the select expert for the isnull from the menu instead of a SQL statement. If that doesn’t work, send me a screenshot if you can to sales@isinc.com and mention my name, Zach.

    Zach

  16. Thanks very much. Fixed the problem I was having in Crystal Reports 11.

  17. Thanks for saving what little hair I have left!

  18. Morten Trydal Says:
    May 10th, 2013 at 4:54 am

    Thanks.

    Saved my day.

  19. length(trim()) = 0

    No need for isnull and OR operator.

  20. Yes you saved my day too.

  21. 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?

    We are using CR XI.

  22. 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.

Leave a Reply