Crystalkeen logo; we offer Crystal reports viewers and schedulers
 
Crystalkeen header image 3
Crystalkeen header image 5

Crystal Reports Tools: Improve Performance While Saving Time and Money

  Resources  
Best sellers:
cView
cViewSERVER
cViewBATCH

Articles:
Administration
Advanced
Basic
Database
Financial

Tools:

Analyzers
CR Schedulers
CR UFLs
CR Viewers
DataBase Tools
Graphics
International
Mail UFLs



 
 

Add'l:
About us

Contact Us
cViewSUITE Ppt

 

CrystalReports
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:
http://www.crystalkeen.com/tools/analyze.html

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.