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:
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
SELECT / FROM / WHERE / ORDER BY clauses
Performing grouping on the server
Selecting distinct records
Understanding Joins: Performing grouping on the server
Selecting distinct records
What joins (links) do
Auto (“smart”) linking
Inner and outer Joins
Enforcing joins (v9 and later)
Join comparisons
Table Inflation
SQL Expressions: Auto (“smart”) linking
Inner and outer Joins
Enforcing joins (v9 and later)
Join comparisons
Table Inflation
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:Limitations of SQL Expressions
How to add and use SQL Expressions
Using "unlisted" functions in SQL Expressions
Writing subqueries within SQL Expressions
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:The down side of using commands
Adding parameters to commands
Enhancing command parameters
Creating a multi-value parameter for a command
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) 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)
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