 |
|||
 |
|||
| |||
| |||||||||
|
Crystal Reports Tools: Improve Performance While Saving Time and Money |
|
Crystal Reports: SQL Expressions and Sub SelectsOne 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:
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.
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. | |||||||||||||||
|
|
|
