phone: (540)338-0194

The Expert's Guide to SQL Expressions, Options and Commands

This guide is now a FREE Download


Improving the SQL used in your reports can improve their performance, solve data related problems and add new capabilities. And yet these features, particularly SQL Expressions, are one of the most poorly documented features of Crystal Reports.  When you add the fact that there are several significantly different flavors of SQL you have a recipe for frustration.  I know.  I have been researching and experimenting with Crystal and SQL a long time. Recently I decided to spend some time unraveling these features for my reports.  I learned some things that aren't widely known and are rarely clearly explained.  So why not let memshow you how to make SQL Expressions, Options and Commands go to work for you.

In the section on SQL expressions I provide 16 essential SQL calculations in each of the following 4 SQL flavors: SQL Server, Oracle, MySQLandand MS Access.  These include string manipulations, boolean tests, data type conversions and date calculations.  Many of these demonstrate useful functions that are not listed in the SQL expression editor.  I even explain the rules for writing subqueries within a Crystal SQL Expression.   You will also learn how to use all of the options for joins and how to use Commands to solve several reporting challenges.  See the full outline below.  

I also provide Email support and telephone training on any Crystal Reports topic.  I charge $180 per hour billed in 15 minute increments.  Remote support is a great way to learn Crystal Reports, one-on-one, with a Crystal Reports expert.

The Expert's Guide to SQL Expressions, Options and Commands:

How Crystal Reports options translate into SQL clauses: 
Performing grouping on the server
Selecting distinct records
Understanding Joins: 
What joins (links) do 
Auto (“smart”) linking 
Inner and outer Joins 
Enforcing joins  (v9 and later)
Join comparisons 
Table Inflation 
SQL Expressions: 
When to use SQL Expressions 
Limitations of SQL Expressions 
How to add and use SQL Expressions 
Using "unlisted" functions in SQL Expressions 
Writing subqueries within SQL Expressions
SQL Commands:
Creating and editing SQL commands 
The down side of using commands 
Adding parameters to commands 
Enhancing command parameters 
Creating a multi-value parameter for a command 
Common problems that can be solved by Commands:
Creating UNION queries 
Filtering on an outer join 
Using a summary query (GROUP BY) to prevent table inflation 
Allowing users to change tables, fields and join types by parameter 
Enhancing dynamic parameters (versions 11 and 12 only) 
Modifying the auto generated SQL (v.8.5 and prior)
Using Crystal SQL Designer (v8.5 and prior) 
Appendix A - SQL syntax variations 
Appendix B – Other SQL resources 
Appendix C – Reporting on views and stored procedures