Two-Pass Report Processing Model

It’s quite possible that one of the most important parts of learning how to use Crystal Reports is understanding how Crystal Reports reads data and processes formulas while building a report. This is called the two-part report processing model and it is the reason why you can and can’t do certain things in a report. While it is certainly possible to create basic reports without understanding the internal workings of Crystal Reports, it won’t be long before you find that some subtotals don’t seem to be calculating correctly and that certain formulas aren’t available when you want them. This is due to the order in which report objects are processed. Throughout this book, I will warn you when these situations can cause you problems and I refer back to this section for more information. Understanding the two-pass report processing model will help you make proper design decisions and avoid problems later.

Crystal Reports processes reports in two stages. This is called the Two-Pass Report Processing Model. The first pass creates the primary data to be printed. During the second pass, Crystal Reports processes grouping data and formulas that can only be calculated while the report is printing. Figure 1-12 shows the two-pass report processing model diagram found in the Crystal Reports documentation. Along the left side of the diagram is the pass number. To the right are the actual processes performed during each pass.

Figure 1-13. Two-pass report processing model

The first thing you might notice is that it looks like a lot more is going on here than just two passes. This is because the diagram also shows “pre-passes” that get the data ready for the pass. There is also a third pass shown, but this is only used when the report prints a total page count at the bottom of each page. Semantics aside, there are two primary passes that are performed for the majority of reports.

The first pass reads individual records one at a time from the database and calculates any formulas that use them. This pass will only calculate the formulas that are based on raw data within a record or that perform simple calculations. As each record is read and the formulas are calculated, the results are stored in a temporary file to be used during the second pass.

After the first pass is finished, Crystal Reports performs a second pass where it evaluates all summary functions on the data and selects the data to be displayed for groups. This wasn’t possible during the first pass because all the data had not been read yet. During the second pass, all the raw data has been read into the temporary file and it can be evaluated and summarized as a whole.

The key to getting the most out of this diagram is to look at the relationships between each step and see where a process appears within the hierarchy. The steps are processed from top to bottom and from left to right. For example, in pass #1 the first step is to read the database records. After that, it will calculate formulas and the record selection formula. You certainly can’t calculate the formulas prior to reading the data.

A rule of thumb is that formulas should only reference formulas from a previous pass. For example, a formula that is calculated in pass #1 can’t reference a formula calculated in pass #2. The formulas calculated in pass #2 generally involve using more than one record and the formula’s value hasn’t been calculated yet in pass #1. Formulas that reference a formula in the same pass can cause unpredictable results as well.

For example, if you have Formula A and Formula B and they are both calculated in pass #1 you can’t be certain which one gets calculated first. If Formula B makes reference to Formula A, the calculation might be wrong because it’s possible that Formula A doesn’t have a value yet. Luckily, there are ways to fix this so that your formulas are accurate. We talk about this more in the chapters on formulas.

Many times, Figure 1-12 is used to explain why you can’t do something rather than explain why you can do it. For example, you can’t chart the value of a summary formula. This is because summary formulas and charts are both processed in pass #2. Since they are on the same level, the summary formula hasn’t been calculated yet.

Let’s define exactly what types of formulas and data are processed during each pass. This makes it easier to categorize your own formulas and determine how they can be used in a report. In many parts of this book, you’ll find that certain types of formulas aren’t allowed with certain report objects. You can refer back to this section to quickly identify which formulas are being referred to.

Pre-pass #1

Pre-pass #1 calculates formulas that don’t reference any database fields. This usually involves simple math or assigning constants to a variable. For example, the next two formulas are both calculated in pre-pass #1.

X := X + 1;Pi := 3.14;

Pass #1

Pass #1 reads in data from the database one record at a time. After each record is read, the record selection formula evaluates whether the data should be printed or if it should be filtered out. Formulas that only reference database fields from the current record are calculated next. The last step is to perform sorting, grouping and build cross-tabs. When finished, all the data is saved to a temporary table for use in pass #2.

In summary, the primary steps that happen during pass #1 are:

Read the data from the database.

Filter records using the record selection formula.

Calculate formulas that use database fields.

Sort and group data.

Pass #2

Pass #2 has steps that involve more complex data processing. The data is grouped, running totals are calculated, charts and maps are built, and complex formulas are calculated. As a general rule, the steps in pass #2 are performed while the report is printing data. All the data used during this pass is read from the temporary table created in pass #1 and the database isn’t read anymore.

The key aspect of pass #2 is understanding which formulas get processed during this stage. The list of formula types that get calculated in pass #2 is extensive. Formulas that have the following characteristics are processed in pass #2:

Using the WhilePrintingRecords function. This forces a formula to be calculated while the report is printing the individula records.

Using summary functions such as Sum, Count, Average, etc.

Using Print State functions such as Previous, Next, TotalPageCount, etc.

Using shared variables with subreports.

An easy way to determine whether a formula is calculated in pass #1 or pass #2 is to let Crystal Reports tell you. First, make sure the formula is being used somewhere on the report (if not, temporarily add it to the Details section until you are finished with this tip). Right-click on the formula and examine the pop-up menu. If you see the Insert menu option (for summary formulas), the formula is a first pass formula. If Crystal Reports doesn’t list the Insert option on the pop-up menu, it is a second pass formula. Summary calculations can’t be performed on second pass formulas, and therefore, Crystal Reports won’t give you that option on the pop-up menu.

Pass #3

In pass #3, Crystal Reports only calculates the total number of pages in the report. If your report doesn’t print the total number of pages on each page (e.g. “Page 1 of 20”), this pass isn’t run. For optimal report performance, you don’t want to print the total number of pages in the report because Crystal Reports has to internally generate the entire report prior to printing the first page. If you do need to print this on your report, it’s best to leave it off while you are designing the report so that testing of the report goes faster. Put it on the report once you feel that the report is finished.