Operation Manual

Designing Optimized Web Reports
Improving grouping, sorting, and totaling
8
Crystal Reports User’s Guide 153
Moreover, by enabling the Perform Grouping on Server option, you’ve
ensured that the initial processing is completed on the database server.
Consequently, only the necessary records are transferred to the report.
For more information on server-side processing, see “Server-side processing”
on page 516.
Using SQL Expressions for groups, sorts, and totals
For reports using Perform Grouping on Server, 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. This will greatly improve the
chances of the processing being done on the server.
For details on when else to use SQL Expressions, see “Using SQL
expressions where appropriate” on page 150.
Using SQL Expressions for Case Logic
If your database supports Case Logic, and your report needs to summarize
an If-Then-Else formula calculation, replace the formula with an SQL
Expression field. In such cases, SQL Expression fields enable Crystal
Reports to perform the report’s grouping on the server.
For instance, suppose that you’re reporting off of an MS SQL Server 7
database, which supports Case Logic. You need to include an If-Then-Else
calculation in your report, and you need to summarize that calculation for
each group in the report. By performing the calculation with an SQL
Expression field of the following form, you use your database’s ability to
process Case Logic:
CASE DatabaseTable."DatabaseField"
WHEN 'SpecifiedValue' THEN Calculation1
ELSE Calculation2
END
If a record’s DatabaseField value is equal to SpecifiedValue, then
Calculation1 is performed; for all other records, Calculation2 is performed.
By incorporating the SQL Expression field, you take advantage of your
database server’s ability to process Case Logic. Your report’s grouping
consequently takes place on the server, even when you summarize the SQL
Expression field elsewhere in the report.
Note: The SQL syntax in this example is specific to MS SQL Server 7. You
may need to refer to your database documentation or consult with your
Administrator in order to determine the syntax appropriate to your database.