The Multi-Pass Reporting Process of the Crystal Reports Engine

Despite all the Crystal Reports functionality covered, to this point, it would be understandable if you assumed that the Crystal Reports reporting engine provides all this power with a single pass through the data it retrieves from the database. This would be a faulty assumptionCrystal Reports actually uses a three-pass reporting methodology to generate reports. Understanding the multi-pass nature of the reporting engine can facilitate effective report design and expedite the debugging of potential reporting challenges. Figure 4.25 from the Crystal Reports online Help file provides a good starting point for understanding the different passes through the data that Crystal Reports makes and what is calculated on each pass.

Figure 4.25. Understanding the Crystal Reports Multi-Pass report engine flow can help in creating and debugging Crystal Reports.

Crystal Reports Processing EnginePre-Pass #1

In the Pre-Pass phase of report creation, only constant formulas are processed. A constant formula example might be 1967*10. These formulas are evaluated at the beginning of the print generation process and are never evaluated again. This process is known as "BeforeReadingRecords."

Crystal Reports Processing EnginePass #1

After the constant formulas have been processed, Crystal Reports begins reading the database records. During the record reading process, known as "WhileReadingRecords," the following occurs:

• Database connection and Record retrieval Record selection and sorting are pushed down to the database in this step if possible.

• Evaluates recurring formulas These formulas are those that contain database fields but do not contain references to subtotals or summary information. This evaluation time is referenced as "WhileReadingRecords" and can be specified within a formula. Formulas that contain references to subtotals or summary information are processed in the second pass.

• Local Record Selection applied If the record selection is too complex to be pushed down to the database, it is applied by Crystal Reports in this step. This is common where the record selection could be not specified in a proper SQL Expression.

• Sorting, Grouping, and Summarizing The data is sorted, separated into groups, and then subtotals and summaries are calculated for each group.

• Cross-Tab, chart, and map generation Only Cross-Tabs, charts, and maps that are based entirely on database fields and recurring formulas are generated in Pass 1.

• Storage of saved data After the totaling process is complete, all the records and totals are stored in memory and to temporary files. Crystal Reports does not read the database again, but instead uses this saved data during all subsequent processing.

Crystal Reports Processing EnginePre-Pass #2

During Pre-Pass #2, groups are ordered in the report for Top/Bottom N and/or Hierarchical Grouping. The reporting engine looks at group instances from Pass 1, and takes the Top N as appropriate, or orders the groups based on the specified Hierarchical Group settings.

Crystal Reports Processing EnginePass #2

Crystal Reports moves through the saved data, if required, to complete any remaining operations and initiates printing of the records in this phase known as "WhilePrintingRecords." During this phase, the following takes place:

• Application of Group selection formula, if applicable.

• Evaluates print-time formulas. These formulas are those that contain any print-time formula functions like Previous() or Next() or explicitly use the "WhilePrintingRecords" function within the formula.

• Running totals calculations, if applicable.

• Charts, maps, cross-tabs and OLAP grids. Cross-Tabs, charts, and maps that include running totals or PrintTime formulas, and charts that are based on cross-tabs or OLAP grids are generated.

• Subreports. All in-place subreports are calculated during Pass #2. When you're using variables within subreports and expecting certain behavior in the main report based on these shared variables, keep in mind when they are processed relative to everything else in the main report.

Caution

Subtotals, grand totals, and summaries might appear incorrectly if the report has a group selection formula. This occurs because the grand totals and summaries are calculated during Pass 1, but the group selection formula filters the data again in Pass 2. Running total fields or Formula fields with variables can be used instead of summaries to total data successfully with group selection formulas.

Crystal Reports Processing EnginePass #3

In the third and final pass, the total page count is determined. This applies to reports that use the total page count, or Page N of M special fields.

Understanding the multi-pass reporting paths of the Crystal Reports engine helps in the general development and debugging of your production reports. Additional leveraging of the built-in Formula functions discussed previously (BeforeReadingRecords, WhileReadingRecords, and WhilePrintingRecords) in combination with the EvaluateAfter() function enable you to design more flexible reports and formulas. These functions also enable you to leverage advanced variable usage and successful sharing among subreports.