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.
Leave a Reply