Advanced record selection formulas

This section is of interest if you are creating reports from large databases and want to reduce the time it takes to process and display the results.

Note:    This section assumes that you are familiar with the Select Expert and that you are reporting off of a SQL database.

Limiting amount of data fetched from the database

The most important thing you can do to speed up report processing is to limit the amount of data that is fetched from the database.

A primary tool for doing this is the record selection formula. Seagate Crystal Reports analyzes your record selection formula and generates a SQL query from it. This SQL query is then processed by the database and the resulting records are sent to Seagate Crystal Reports. Seagate Crystal Reports then evaluates the record selection formula locally for each of the records retrieved from the database, to get the set of records that is used to generate the report.

Unnecessary records are eliminated at two stages: at the database with the SQL query and within Seagate Crystal Reports by the record selection formula. For speed, you want as many records as possible to be eliminated in the first stage. This is commonly called pushing record selection down to the database server.

Record selection formula examples

In the Xtreme sample database, there are 2,001 records in the Orders table of which 169 have order dates prior to 1997. Suppose you want to report on only those records. A possible record selection formula is:

Year ({Orders.Order Date}) < 1997

The SQL query generated will send all 2,001 records to Seagate Crystal Reports and then the record selection formula will reduce this to 169. To see this, invoke the Show SQL Query dialog on the Database menu and notice that the SQL query has no Where clause. This is because Seagate Crystal Reports is not able to push down the Year ( ) function in the WHERE clause.

On the other hand, a record selection formula that generates the same
report is:

{Orders.Order Date} < #Jan 1, 1997#

This record selection can be performed on the database server, so it is pushed down. The SQL query generated will send only 169 records to Seagate Crystal Reports and then when the record selection formula is evaluated by Seagate Crystal Reports, no further records need to be eliminated. Notice that the resulting SQL query has a Where clause.

For speed, it is better to write record selection formulas like the second than like the first if possible; they both generate the same report, but the second takes advantages of the power and optimizations available on the database server itself for handling its own data.