Crystal Reports: Effective Date and Expiry Date Fields

Sometimes, database tables have a structure that maintains a history by having fields to indicate an effective date and expiry date of each record. A currently active record may be one where::

• either today falls within the two dates,
  or
• the effective date is before today and the expiry date is NULL.

It’s the NULL that causes the problem. It makes the record selection formula quite complex. A solution is to use a formula instead of the effective date in your report.

If isnull({table.eff date}) then currentdate +7 else {table.eff date}

This suggests that all current records will expire in one weeks time. If your report spans a wider date range you might like to increase this number. I’m now using + 365 in many of my reports because my reports look more than one week into the future.

A variation of this for version 7 users extracting data via SQL or ODBC is to use an SQL expression instead of the formula. Use one of the SQL expressions below in the record selection formula.

NVL({‘table’.’eff date’}, sysdate + 7) (for Oracle, etc.)

{fn IFNULL(table."EXP_DT",{fn NOW()}+7)} (for ODBC, etc.)

The performance advantage of using SQL expressions is staggering. On a large database I’m finding my report runs in less than 10% of the time it used to. Well worth investing some time improving the performance of your report.

One problem with the above technique is when the database table includes future records. These are where the

effective date is in the future but with a null expiry date. For example, the values for the following fields are:

Today 1-Dec-1999

Eff Date 12-Dec-1999

Exp Date null therefore {@Exp Date) is 7-Dec-1999

The effective date range of the record is 12-Dec to 7-Dec (i.e backwards in time). If you use a {User date}

parameter of 9-Dec in record selection formula of

{?User Date} in {table.Eff Date} to {@Exp Date}

you will discover the record above will be selected for the report. That is because the "in" operator is bi-directional.

The above record should not be selected as the Effective date isn’t until 12 Dec.

The solution to get the correct records into your report is to use the following in your record selection:

{?User Date} >= {table.Eff Date} and

{?User Date) <= {@Exp Date}

Thanks to Alister Louis of Aldridge Punter for this suggestion.

Today 1-Dec-1999

Eff Date 12-Dec-1999

Exp Date null therefore {@Exp Date) is 7-Dec-1999

The effective date range of the record is 12-Dec to 7-Dec (i.e backwards in time). If you use a {User date} parameter of 9-Dec in record selection formula of {?User Date} in {table.Eff Date} to {@Exp Date} you will discover that the record above will be selected for the report. That is because the "in" operator is bi-directional.

The above record should not be selected, as the Effective date isn’t until 12 Dec.

The solution to get the correct records into your report is to use the following in your record selection:

{?User Date} >= {table.Eff Date} and

{?User Date) <= {@Exp Date}

The prize goes to Alister Louis of Aldridge Punter for this suggestion.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.