Multi-Pass Processing Model

book list add book to my bookshelf create a bookmark purchase this book online

mastering crystal reports 9
Chapter 14 - The Report Engine Processing Model
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

To fully evaluate design elements and build a report, the Crystal Report engine goes through the actual report three times before the final result appears on screen. During each pass, different design elements come to life and their values become available in the report. Figure 14.1 depicts this process from the starting point at the data source to the ending point on a user’s screen or printed report.

click to expand
Figure 14.1. Three-pass processing

Pass 1 focuses on the data source and field-oriented portion of the report cycle, while Pass 2 concentrates on complex processing of data that is now in Crystal’s memory. Pass 3, the smallest of the passes, can almost be considered a post-processing step.

Tip 

The Crystal Reports help file contains a visual flowchart of the three pass-processing steps. The easiest way to locate it is to search the help file using the phrase “Multi-pass reporting flowchart.”

Pass 1 Processing

Visualize what happens when you preview a report in Crystal Reports: The data is retrieved, processed, formatted, and written to the screen. The process, therefore, begins with data. The list presented here shows the sequential tasks that take place in Pass 1; a discussion of each task follows the list.

  • Set values for all constants.

  • Retrieve the data from the data source.

  • Evaluate formulas that return a direct result and that contain database fields.

  • Perform any additional record selection needed using Crystal elements locally on the data returned from the initial query.

  • Sort the data.

  • Group the data.

  • Calculate group subtotals.

  • Calculate group summaries.

  • Generate cross-tabs that contain database fields or formulas that contain database fields.

  • Generate charts that contain db fields or formulas with database fields.

  • Generate maps that contain db fields or formulas with database fields.

  • Store data records and interim values (such as totals) in memory or temporary files.

Constants

The first data values initialized in a Crystal report don’t actually come from an external data source; instead, the values are internal to the report. The very first thing Crystal does is initialize values for any constant formulas that were used in the report. These data values are completely internal to the report. There are several predefined constants that may have been used, such as Pi (3.14 when rounded to two decimal places). In addition, you may have created formulas that have static values, for instance, a variable called TaxRate that was assigned the value 8.06 and is never changed by any other formula. While Crystal does not have a keyword that identifies a constant, the fact that it is not used in another assignment formula tells Crystal that it is a constant. Here’s an example of some typical formulas that are considered constant values using Crystal syntax:

DaysInTheWeek := 7; GolfDay := "Wednesday"; TaxPercentage := 8.07 / 100; OurStandardValue := PI * 100;

Some of these values are direct assignments and the others are calculated results. The key to being evaluated in the constants portion of the processing model is that the assignment does not involve any database values.

Note 

The evaluation of constant formulas is referred to as Pre-Pass 1, or the Before Reading Records pass, because formula evaluation is taking place prior to the retrieval of data from a data source.

Data Retrieval and Server-Based Record Selection

After constants are initialized, Pass 1 officially begins. During this pass, the result data set from the data source is initiated and data is retrieved and loaded in your computer’s memory. Depending on how you asked for the data and what data source you’re accessing, the data set may come back sorted and grouped with much of the processing done on the server, or it may come back as a complete block of data that will be sorted and grouped locally within Crystal.

The data to be retrieved is specified in three places in Crystal Reports, and what is retrieved is outlined in Table 14.1. As a result of the mechanisms used, Crystal generates a SQL (Structured Query Language) statement that is passed to the data source for processing.

Table 14.1: Specifying Data

Crystal Reports Mechanism

SQL Query Component

Purpose

Field Explorer

SELECT

Identify fields to retrieve

Database Expert

FROM

Specify data source and tables

Select Expert

WHERE

Filter data based on field-level comparisons

If the Standard Report Creation Wizard was used to create the report, much of the information in the above three areas could be specified through the wizard, but the query can be modified using the mechanisms described above. Also, SQL commands can be directly used in Crystal Reports 9 to generate the query for the data source, which replaces the mechanisms in Table 14.1.

Information in the SELECT and FROM portions of a query is always sent to the data source for processing. The WHERE clause, however, may be processed by the data source or by Crystal Reports after the data is received from the data source. Pass 1 deals with retrieving all possible data from the server based on the query. Here’s an example of a query that can be completed executed by the data source:

SELECT `Resorts`.`ResortName`,`Resorts`.`ResortCode` FROM   `Resorts` `Resorts` WHERE  `Resorts`.`FiveStarRating`=TRUE

Since no internal Crystal functions were used in the WHERE clause, it can be sent to the data source for processing. All record selections that are created with Crystal’s Select Expert dialog window and that avoid Crystal formulas can be sent to the server. Oftentimes a Crystal formula can be replaced with a direct SQL-supported function, which would therefore enable the data source to process it in its entirety. For example, the following criterion for the Select Expert specifies a user-created function:

{@TaxPercentage} < .08  

where the code for the @TaxPercentage formula is (TaxRate / 100). If the statement is rewritten in the Select Expert to use a database function instead of a Crystal formula, it can be passed directly to the data source as part of the initial data query:

WHERE {Orders.TaxRate}/100 < .08

The benefit of server-side record selection is smaller data sets returned from the server and therefore better performing reports. Your report will perform better when Crystal processes 100 records returned from the data source rather than 10,000 records!

Simple Formula Evaluation

Once the query has been passed to the data source and data retrieval begins in the report, the evaluation of formulas for Pass 1 can take place on each record being read. This formula pass is known as the While Reading Records phase because the formulas are being processed as the data records are being read from the data source. The formulas that are evaluated in this pass are ones that do either or both of the following:

  • Return a direct result

  • Contain a single database field

These formulas do not reference subtotals or summarizations because that would require access to database fields outside the current data record. The formulas evaluated in this pass are generally in the Details section and apply to a single detail data record that will recur for each detail data record in the data set. Because they recur for each detail record, they are also known as recurring formulas. Here’s an example of three simple recurring formulas using Crystal syntax:

FirstInitial := (left{Cust.FName},1); FullName := ({Cust.FName}) + " "+ ({Cust.LName}); RefID := "REF" + ({Cust.CustID}) + ({Order.OrderNum}); TaxAmount := ({Order.TotalPurchase}) * TaxPercentage;

Notice that although some of the examples reference more than one database field, because they stay within the same record in the Details section and do not reference subtotals or summaries, they are considered simple recurring formulas. Simple formulas, therefore, must require only the current record of data in order to calculate a result.

Local Record Selection

After Crystal receives the data and simple formulas are carried out at the record level, local record selection for the record is performed. This is record selection that cannot be performed on the server and is therefore processed directly in Crystal.

The following are examples of when local record selection will take place (versus being passed to the data source with the original query):

  • A Crystal function is used in the Select Expert (WHERE clause).

  • No index is available on the data source.

  • For non-ODBC and non-OLE data sources, WHERE clauses uses OR logical operator.

Sorting

Following record selection, the data is sorted. Unsorted data is displayed in the Details section in the order in which it was originally added to the database (known as original order in Crystal Reports).

Grouping

Sorted data can optionally be grouped. For ODBC, OLE, and other SQL-capable databases, grouping can take place on the server; for non-SQL data, Crystal does the grouping.

To enable server-side grouping for SQL-type data sources for the current report, choose File > Report Options and enable the Perform Grouping On Server option. To enable server-side grouping of SQL-type data sources for all reports you design, choose File > Options, switch to the Database tab, and in the Advanced Options section, enable the Perform Grouping On Server option.

Group Subtotals

The records within groups can be subtotaled for each occurrence of a group. This happens after the sorting and grouping because it involves more than one record of data. If a subtotal is based on a field that involves a formula, the formula must be a simple recurring formula (see above) so that it already has a value.

Group Summaries

After processing all the detail records one at a time and creating subtotals where required, Crystal can summarize the groups. To do this, it sorts the data, places each item into a group, and then creates a summary of all the values in each group. If a summary is based on a field that involves a formula, the formula must be a simple formula (see above) so that it already has a value.

Cross-Tabs

After Crystal builds grouping summaries, it can build cross-tabs, charts, and maps since all of these rely on summarized data. The cross-tabs constructed during this pass can contain database fields or formulas that reference database fields but cannot contain any grouping and summary information. If a cross-tab cell summary is based on a field that involves a formula, the formula must be a simple formula (see above) so that it already has a value.

Charts

After creating cross-tabs, Crystal adds chart components. The charts constructed during this pass can contain database fields or formulas that reference database fields but cannot contain any grouping and summary information. If chart summary data is based on a field that involves a formula, the formula must be a simple formula (see above) so that it already has a value.

Maps

After all charting in the report is complete, Crystal adds geographic maps and any data points populating the map to the report. The maps constructed during this pass can contain database fields or formulas that reference database fields but cannot contain any grouping and summary information. If a map uses fields involving formulas, the formulas must be simple formulas (see above) so that values already exist.

Storing the Data

At the end of Pass 1, the data set is saved in memory and other interim values including grouping, summaries, and formula results are stored in local temporary (TMP) files. This combination represents preprocessed data, which is used as the starting point for Pass 2 evaluation.

Pass 2 Processing

The following tasks are carried out sequentially in Pass 2 processing:

  • Sort any groups created if Top/Bottom N or Hierarchical Grouping is used.

  • Execute group-selection formulas.

  • Calculate running totals.

  • Calculate formulas that contain the formula WhilePrintingRecords.

  • Generate cross-tabs that contain running totals or PrintTime formulas.

  • Generate charts that contain running totals or PrintTime formulas.

  • Generate maps that contain running totals or PrintTime formulas.

  • Generate OLAP grids.

  • Generate in-line subreports.

  • Generate pages on demand including on-demand subreports.

Complex Sorts

The sorting of data for Top and Bottom N reports as well as hierarchical reports takes place in a pass that is officially known as Pre-Pass 2. It uses the in-memory data set and any group information calculated during Pass 1, reorders it for top, bottom, or hierarchical slicing, and then makes it available for the complex processing in Pass 2.

Group-Selection Formulas

After completing Top N and Hierarchical sorting, Pass 2 officially begins with the execution of group-selection formulas. Group selection applies only when groups exist in the report. In the following group-selection criterion, for instance, the report will show only data where the count of the Country field is not equal to one, meaning not to count any country that contains only one resort.

Count ({Resorts.Country}, {Resorts.Country}) <> 1

Since group-selection formulas occur in Pass 2, they execute after subtotals, grand totals, and summaries, which were generated in Pass 1. This means that if you want to use subtotals, grand totals, and summaries to calculate groups, you should use running total fields instead of the normal subtotaling, totaling, and summarizing methods. In the above example, to get a total for how many countries have more than one resort, you would add a running total field instead of a total or subtotal field.

Running Totals

Running totals calculate after group selection and in a completely different processing pass than summaries, totals, and subtotals. This allows them to access and count all information available in the report.

WhilePrintingRecords Formulas

The WhilePrintingRecords formula is one of four formula statements that you can use to specify exactly in which pass to process data. Any formula in the Details section that includes the WhilePrintingRecords statement is evaluated during the second pass through the report. For more information, see the discussion later in this chapter in the "Specifying Execution Time" section.

Complex Cross-Tabs

A cross-tab that contains group information is generated after the group selection formula filters out unwanted groups. The cross-tabs built in this pass can contain grouping and summary information as well as cells that use complex formulas such as running totals and PrintTime formulas.

Complex Charts

In this pass, charts that contain grouping and summary information are generated. All formulas are valid at this point for inclusion in the charts, including running totals and PrintTime formulas. Charts that are based on complex cross-tabs can also be built.

Complex Maps

Geographic maps based on summary and group data are built during this pass. As with charts and cross-tabs, any valid formula can be used at this point in the process to generate mapping data points.

OLAP Grids

After cross-tabs, charts, and maps have been created, Crystal creates OLAP grids. OLAP grids by their very nature are more complex than traditional row and column-analysis grids, requiring therefore the sophistication of powerful formulas. All formulas are available at this point to juxtapose one variable against another for analysis.

In-Line Subreports

An in-line subreport is one that appears in a main report and opens with the main report as opposed to opening when a user clicks a link (an on-demand subreport). These subreports undergo their own two-pass evaluation process (which excludes the third pass for calculating the total page count). Since a subreport can contain any element that a traditional main report can contain (with the exception of a subreport), the Crystal Reports engine processes it according to all the same rules.

Pages on Demand

Crystal evaluates page formatting only when it is about to display that particular page; this is known as the page on demand architecture. When evaluating a page, the following two issues come into play:

  • The sequence of sections as they appear top to bottom

  • The physical placement of elements within a section

The elements located in the Page Header are evaluated after the elements in the Report Header, the elements in the Details section are evaluated after the elements in the Page Header, the elements in the Page Footer are evaluated after the elements in the Details section, and so on. In addition, the physical placement of the elements matters since elements are evaluated from the top-left corner of a report from left to right and top to bottom (in the same way you’re reading this page). Where you place formulas, therefore, matters a great deal in their correct evaluation.

As a part of the on-demand architecture, on-demand subreports are not processed until the user clicks their link. If you use on-demand subreports instead of in-line subreports, your report will be a single-pass report until the user clicks the link to open the on-demand subreport. At that point, the Crystal Report engine takes its second pass through the subreport. This will shorten the time it takes to open the initial report, thereby improving performance when the report first opens.

Pass 3 Processing

Imagine that you have just clicked the Refresh key in Crystal Reports to go from the Design tab to the Preview tab to view a report that includes 500 detail records. When the report displays, you are viewing the first page. In the page navigation area of the Preview tab located at the upper-right corner of the report, the text “1 of 1+” appears, as shown in Figure 14.2.

Have you ever wondered why it doesn’t just put the correct page number in there instead of using the “1+” placeholder? The reason is that the report engine doesn’t know how many pages the report has at this point of the processing. All it knows regarding page numbers when it displays the first page is that it is not displaying the last page.

click to expand
Figure 14.2. Page count indicator

The calculation of the total page count occurs in Pass 3. When you click the Show Last Page button, visible in Figure 14.2, (a right-pointing black arrow immediately followed by a short vertical black line), you’re forcing the immediate evaluation of the last processing phase. If you instead click the Show Next Page button, which is to the left of the Show Last Page button, the counter would increment to “2 of 2+” demonstrating Crystal’s page on demand architecture and reaffirming that the total page count is not yet available.

You can force the full evaluation of a report by using any of the following fields any place in your report:

Print state formulas:

  • TotalPageCount

  • PageNofM

Special fields:

  • Page N of M

  • Total Page Count

When one of these formulas appears in a report, previewing the report shows the page count in the page navigation area; instead of “1 of 1+,” you will see something like “1 of 11.”

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement
 
Conello © 2000-2003     Feedback


Mastering Crystal Reports 9
Mastering Crystal Reports 9
ISBN: 0782141730
EAN: 2147483647
Year: 2005
Pages: 217

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net