Tuesday, January 3, 2012

Crystal Reports : Dealing with (and testing for) NULLS

NULLS are a pain. Because they are functionally equivalent to blanks. A NULL field and a "blank" field return the same thing; nothing. But they are technically different values and as a Crystal Reporter, you will need to know how to deal with them.

Let us consider the field {CONTACT2.USTATUS}, which tracks a customers' Service Status.

For those records with an explicit value (i.e. No Service), this is returned normally as a string.

For those records that HAD a value at some point in the past, but have since been "blanked out", these blank values are also returned normally as a blank string.

For those records where USTATUS has NEVER been written to, this will return a NULL. A NULL is "less than" a blank.

So when you propose the following Selection Criteria on USTATUS like this (selecting for anyone who doesn't have a Status);

{CONTACT2.USTATUS} = ""

This is only half of the battle. We must also test for NULL values as well, like this;

{CONTACT2.USTATUS} = "" AND ISNULL({CONTACT2.USTATUS}) = TRUE

Unlike a SQL statement, which can simply use USTATUS IS NULL, we must use the special ISNULL() Crystal function. This function returns a BOOLEAN, which is either TRUE or FALSE.

Where your particular NULLS spring up will be determined by how well-written your front end software is. For example, they are more often found in so called "legacy" systems than newer, more modern ones.

2 comments:

邓洁 said...

Audra Dior was basically some The language fashion designer what individuals passed by the age of fladskærm by a spirit encounter. And yet your partner's replica tag heuer business enterprise don't pass on with the help of your man. Dior is perfect termed typically the director from one of the many uk's main type stores. Your partner's business enterprise, Audra Dior, keeps Louis Vuitton, 's. Some., Moët Hennessy healthier termed LVMH a The language multinational high-end possessions conglomerate which may be omega replica sale headquartered through London. The guy was basically some trendy from shirts or dresses, wholesale handbags and various other fashion accessories. Audra Dior's grandfather hoped who your partner's child , would definitely turn into a diplomat, and yet Audra found various objectives. The guy was basically a particular painter not to mention was going to turn out to be mixed up in art form environment. By way of your partner's grandfather, by the age of 23, the guy departed higher education not to mention the guy and then a buddy from your partner's started a nice art gallery. The pair were successful where many could actually offer for sale paintings tag heuer replica uk because of actors along the lines of Pablo Picasso. Alas, subsequent to basically 36 months, typically the art gallery had to be enclosed. Dior's grand mother not to mention friend passed not to mention your partner's dads fertilizer industry was not able within Amazing Sadness.

Anonymous said...

My group is showing preview as grouphead and groupmember, i want head to be followed by member displayed in same page...plz help