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.