Crystal Reports: Using PreviousIsNULL and NextIsNULL

There can be a temptation to use the PreviousIsNull function to indicate the first record of the report. We suggest you use a different technique because while PreviousIsNull does return True at the start of the table, it can also return True for other records.

For example you will get false positives where there is a null value in a field or where you are doing a left join and are looking at the index field of the details table. Even applying the function to a Primary Key field will find null values if you are using a Left or Right Join.

Similarly, using NextIsNull to find the last record may give you a false indication.

Here is a potential use of PreviousIsNULL:

You have a help desk application and database where the CallCompleted field had a null value when the call was still unresolved.

You could use this function in a schedule planning to calculate a weekly workload report.

• Group the data by call center operator.

• List all the calls assigned to them by priority and calculate the operator workload using the actual or estimated time to complete.

• If there are a large number of incomplete calls it would be useful to show if this workload is actual or an estimate.

• If the previous call has no completed date, it is open and the total is an estimate, rather than an actual value.

So, we can use this formula to display some qualifying information:

If PreviousIsNull ({call.callcompleted}) then “estimate” else “actual”

First Record

Now let's address that question of indicating the first record of a report. If you design the report properly, this should never be necessary.

If your report consists of multiple sections, then use a good section title. It will then be obvious where information begins.

if you avoid producing a report that's merely a shell for raw data or barely processed data, the user won't be looking for where that first record is.

Consider the following scenario. The national sales manager wants to stay on top of problems. One way to do that would be to identify the top five accounts that have not increased their sales in the last six months. Presumably, if business is not growing there is probably cause to find out why.

It isn't necessary to show any actual numbers. Providing drilldown ability to make the data available is a nice touch, but the main thing is to identify those accounts. With a short list that can receive personal attention from the national sales manager identified with preceding text "Top Five Accounts With No Increase in Sales Over Preceding Six Months" you do not need to indicate the first record.

A complex, data-centric report might rank all accounts in descending order. But that is really too much information. It isn't necessary to focus on all those accounts, at least not for the national sales manager.

You could have another report showing activity for the most critical accounts, but the purpose of this one is to show this very narrow slice of business intelligence. That gives the national sales manager an clear picture, at a glance, of where to focus now.

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.