SQL Commands and Crystal Reports

Crystal Reports allows users to create reports not only by linking tables using the Database Expert screen but also by using SQL Commands.  SQL Commands allow the results of a SQL query statement to be used as a table.  

If a SQL statement has already been created that provides the data needed for a Crystal Report, add this to the data source instead of selecting individual tables.

1. On the Main Menu, select Database and then Database Expert. 
2. On the Database Expert screen, in the Available Data Sources panel, expand the existing database connection.
3. Select the Add Command option and click the [>] button. Selecting the option opens a dialog box for entering the SQL statement. Only fields listed in the SELECT clause of the SQL will be available to add to the report.

Parameters can also be used in the query by following these steps:

1. On the Parameter List pane, click the Create button to add the parameter.
2. Active parameters from Ungerboeck Software, such as @Organization, @Event, etc., can be used.  Set the Value Type according to the data type of the database field the parameter is being linked to.  
3. Use the parameter in the SQL statement by typing it in the format of {?@Organization} or double-clicking on the parameter name. 

Notes:

• When creating a SQL Command in Crystal Report, try to avoid using special operators, such as "*", on the SELECT statement and specify the name of each field to be retrieved in the query.  This increases the report performance and avoids potential problems when running complex reports.