Operation Manual

Designing Optimized Web Reports
Using enhanced record selection formulas
8
150 Crystal Reports User’s Guide
58 records are returned for your report, instead of the 270 records that were
returned before you put the parameter field into a record selection formula.
By enhancing this report, you’ve retrieved all the information you needed and,
at the same time, you’ve ensured that the fewest number of records is
transferred from the database server.
Related topics:
• To create enhanced parameters, see “Advanced parameter features” on
page 461.
• For general information about parameter fields, see “Parameter and
prompt overview” on page 438.
• For general details regarding record selection formulas, see “Record
Selection” on page 155.
Using SQL expressions where appropriate
SQL expressions are like formulas, but they are written in Structured Query
Language (SQL). They are useful in optimizing report performance because the
tasks they execute are normally performed on the database server (as opposed
to a regular formula, which is sometimes executed on the local machine).
Don’t use SQL expressions exclusively, because Crystal Reports includes its
own formula language, which is much more powerful than standard SQL.
Both Crystal Syntax and Basic Syntax allow you to enhance and hone your
formulas in ways that are either difficult or impossible with SQL. In certain
circumstances, however, creating and using SQL Expression fields can
speed up the processing of your reports.
Key uses of SQL Expression fields
To maintain optimum report processing speeds, avoid using formulas
(whether Crystal or Basic syntax) within record selection formulas. Instead,
replace the original formula with an equivalent SQL Expression field, and then
incorporate the SQL Expression field into your record selection formula.
Doing so will greatly improve the chances of your record selection being
pushed down to the server.
In addition, avoid sorting, grouping, or totaling on a formula field (whether
Crystal or Basic syntax). Instead, replace the original formula field with an
equivalent SQL Expression field, and then sort, group, or total on the SQL
Expression Field. Again, this will greatly improve the chances of the
processing being done on the server.