Evaluation Time Defaults

Chapter 1 discussed the Two-Pass Processing Model. Knowing how this model works is especially important when writing formulas. The type of formula determines when it is processed. Where you place a formula on a report and the functionality within that formula affects when the formula is evaluated and whether it returns the expected value or not. A formula can be placed on any section of your report, but you should plan this in advance so that you can guarantee the proper results. To determine where to place a formula, you need to know the rules that Crystal Reports uses to evaluate functions. In the following list are the rules that are applied.

• A formula that only references variables (it doesn’t use group/summary fields or database fields) is evaluated before any records are read.
• Formulas using database fields are evaluated while records are being read.
• Formulas using group fields, summary fields, or page related fields (e.g. the page number) are evaluated after the records are read and while the report is being printed.
• You can’t determine which formula within a section will be called first. This is because formulas within the same section are not evaluated in any particular order.

There are times when the default rules listed above will not give you the results you desire. For example, there may be a situation where you have two formulas in the same section and one formula relies upon the other to be called first. According to the default rules listed above, you know that there is no way to determine which one will be called first. You need some way to force one formula to be called before the other. Fortunately, Crystal Reports lets you override the default rules to fit your particular situation. There are four keywords that let you set when a formula is evaluated: BeforeReadingRecords, WhileReadingRecords, WhilePrintingRecords, and EvaluateAfter. Each keyword is added to the beginning of a formula to set when it gets evaluated. These keywords are listed in Table 7-1.

Table 7-1. Evaluation Time Keywords

Evaluation Time Keyword	Description
BeforeReadingRecords	Evaluate the formula before any database records are read.
WhileReadingRecords	Evaluate the formula while reading the database records.
WhilePrintingRecords	Evaluate the formula while printing the database records.
EvaluateAfter(formula)	Evaluate after another formula has been evaluated.

If a formula isn’t returning the expected results, there is a good chance it’s because of when the formula is being evaluated. If you are trying to debug a formula, you have to take many things into consideration: does the formula only use variables; should the formula be put in a different section; is the formula being evaluated while reading records or while printing records; does the report use a grouping section that may be affecting the order of evaluation. This is a lot to think about. You can cut down on the amount of time spent debugging formulas if you start out by following some general guidelines. The remainder of this section gives guidelines and examples for you to think about when writing your formulas.

Place formulas that reset variables to their default value in the Report Header or Group Header section. This insures that they are called before the formulas in the detail section are evaluated.

Be careful when using formulas that only have variables in them without any database fields. They will only get calculated one time prior to reading the records. If the formula is cumulative in nature, you need to force the evaluation time to be WhileReadingRecords. If the report uses groups then you should use the WhilePrintingRecords keyword.

The keyword BeforeReadingRecords can’t be used with formulas that have database fields or grouping/summary fields in them.

When you place multiple formulas within the same section, you can’t assume the order that they are executed in. If you need one formula to be evaluated before another formula (its result is used in other formulas) then put the EvaluateAfter keyword in the dependent formula. The following example shows how this is used. This formula relies upon the formula ParseName to take the {Customer.Name} field and parse the first name and last name out of it. The values are put into the global variables FirstName and LastName. This Basic syntax formula returns the LastName variable so that it can be displayed on the report. The @ParseName formula isn’t shown here.

EvaluateAfter({@ParseName})
Global LastName As String
Formula = LastName

Summary calculations are performed while printing records. They can only do calculations on formulas that were evaluated beforehand (i.e. while reading records). Thus, a formula that uses WhilePrintingRecords can’t have summary functions performed on it.

Of course, these guidelines won’t be able to prevent every problem from happening, but they are here to help give you a start in the right direction.

To illustrate the importance of using the proper keyword in a formula, let’s look at a final example that shows you each stage of writing a formula and how the output is affected by the evaluation time keyword.

The example report is a customer report that uses the Xtreme.mdb database. The report has the first column as the row number. The row number is tracked using a variable called RowNumber. The formula is as follows:

Global RowNumber As Number ‘Don't use Local b/c that resets the variable
RowNumber = RowNumber + 1
Formula = RowNumber

Figures 7-11 shows the output of this report.

Figure 7-11. Row Number example output with same row number.

Notice that all the row numbers are 1. This is because the default rule states if a formula only has variables in it, it will be evaluated before any records are read. Even though this formula is placed in the detail section, it only gets evaluated once and the value variable never increases.

To fix this problem, use the keyword WhileReadingRecords in the formula.

WhileReadingRecords
Global RowNumber As Number ‘Don't use Local b/c that resets the variable
RowNumber = RowNumber + 1
Formula = RowNumber

Figure 7-12 shows that the row number is now accurate because the formula is evaluated every time a record is read.

Figure 7-12. Row Number example output with correct row numbers.

Let’s modify the example so that the report uses a grouping section based on the country name.

Figure 7-13. Row Number example output with grouping.

Figure 7-13 shows that the report now groups by country and uses a group header. Unfortunately, this change has introduced a bug in the report because the row numbers are now out of order (see the Australia group). This is because the row number is being calculated while the records are being read. After being read, the rows get resorted based upon their group. The row numbers get resorted as well. To fix this, change the formula so that the row number is being evaluated while the records are being printed and not while being read.

WhilePrintingRecords
Global RowNumber As Number ‘Don't use Local b/c that resets the variable
RowNumber = RowNumber + 1
Formula = RowNumber

Figure 7-14 shows that the row number is now accurate.

Figure 7-14. Row numbers are correct with grouping.

This series of examples shows that putting an Evaluation Time keyword at the beginning of a formula has a dramatic effect on the formula’s value.