Using the ISNULL Function in Crystal Reports

Crystal Reports

The IsNull function in Crystal allows us to deal efficiently with nulls, or empty fields, in a data source. I talked about this in an earlier post. We don’t want to skip partly empty records, in most cases. But if we bring in nulls, we have to be able to work around them or compensate for them.

The first example shows what happens if there is nothing in the Region field, and no IsNull to help. We get a comma between city and state/region, but there is sometimes no state/region to display. And some people find this distracting, or even irritating. And it can be misleading. (Remember “This page intentionally left blank”?)

So we need to set up a formula field which will show the comma if there’s something to show after it. And ONLY if this is the case. The IsNull function is the key.

IsNull tests for exactly that: In combination with the If function, it asks “Is (there a) Null in the Region field?” The rest of the formula details what to do about it.

If IsNull comes up positive, i.e. there’s nothing in Region, show only City. No comma, no Region. If IsNull comes up negative for Region—if it is NOT null—then show City, concatenate with a comma and a space, and hook the Region on the end. And proceed to put the rest of the address in place. (The “ChrW(13)”s are the code for paragraph returns. We have to insert them this way since we can’t have the program hit Enter all the time.)

One point which is kind of subtle, but nevertheless important, is the location of the parentheses in this formula. The parentheses tell us that the part about the IsNull gets done first, just as in regular math. The old catchphrase, “Please Excuse My Dear Aunt Sally,” tells us anything in parens gets priority. And this means the IsNull test happens before anything other than getting the source data to go into the report.

For more info, give our Crystal Reports courses a look.