Crystalkeen logo
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money




CR Schedulers
CR Viewers
DataBase Tools
Mail UFLs


About us

Contact Us

on Steroids

Crystal Reports: SQL Expressions and Sub Selects

One of the more advanced technical sessions at a late 2006 conference was Kurt Rheinhardt’s talk on “the Power and Possibilities of SQL expressions”.

Kurt is a very experienced report designer and he demonstrated some amazing things with SQL expressions.

In particular, we were impressed to see you could do sub select clauses inside SQL expressions. If you can use these in your reports, performance improvements can be dramatic.

Our sample report extracts a few customer names from the Xtreme sample database. We then created a SQL expression of:

(Select Sum("Order Amount") from Orders

where "Customer ID"=`Customer`.`Customer ID`)

The report before using the SQL expression returned six records from the region we selected. Using the SQL expression we still had six records, and had got the database to do all the hard work.

The key to making this work is to enclose the entire expression inside parentheses. It is also important that the subselect clause returns a single record with a single field.

Because SQL expressions are calculated in the database, the entire select clause is passed to the database engine to evaluate. This is a fast way to process the data. The major disadvantage of SQL expressions is that they are database and driver specific. Kurt recommends you build your SQL using a SQL editor. Once you have that working, you can paste the SQL into the SQL expression in the report.

Used this way, One SQL expression can replace a subreport that returns a shared variable. It simplifies your report and makes it faster to run, and easier to design.

Report Analyzer

Document your SQL expressions with Report Analyzer:

Click here to see what you're missing by not having Report Analyzer.

This article is copyrighted by Crystalkeen, Mindconnection, and Chelsea Technologies Ltd. It may be freely copied and distributed as long as the original copyright is displayed and no modifications are made to this material. Extracts are permitted. The names Crystal Reports and Seagate Info are trademarks owned by Business Objects.


  • Questions? Please write to We do want your business.
  • Do you have your own tips for Crystal Reports administrators and designers? Write to and we'll post your tips with a link to your Website (or with some other attribution if you choose).
  • Crystal Reports is a subsidiary of Business Objects, which is owned by SAP.