Writing Custom SQL Statements
The Database Expert, discussed in Chapter 10, gives you the ability to create sophisticated data sources by linking tables from different types of data sources (tables, views, stored procedures). Being able to customize how these tables are linked together can not only impact report performance, but it can create new ways of reporting on data that wasn’t possible with the basic Crystal Reports functionality.
For example, the UNION keyword is a very powerful way to connect data from two tables together and make them appear as a single table. But the Crystal Reports Database Expert doesn’t have a way to use the UNION statement. Linking tables using more advanced SQL statements requires creating custom SQL statements.
Building more advanced data sources with custom SQL statements requires adding a command object to your report. A command object is a custom SQL statement that you write yourself and it returns data to the report. In essence, you are creating a virtual table that Crystal Reports can build reports from. You can put any valid SQL statement in it that you want and it is passed directly to the database server. Of course, this requires more than just a cursory knowledge of SQL. Before you attempt adding command objects on your own, please make sure you have a solid knowledge of the SQL language or possibly ask someone who works on the database to assist you.
Not all databases let you create a new command object. If you are working with Sybase, DB2 or Informix, you have to use an ODBC connection. Connecting to these databases with native connections doesn’t support command objects. To find out if this is possible for your database, look in the Database Explorer. If the database allows it, you’ll see the Add Command option listed just below it. For example, in Figure 11-4 you can see how the SQL Server database and the MS Access database both have an Add Command option.
Figure 11-4. Adding a Command object on the Database Expert dialog box.
Before you can add the command, you first have to open the data source. This is the same way you would open any data source you need. Find the proper folder in Database Expert, specify its location, and set any user credentials necessary to login. Once you return to the Database Expert you’ll see the Add Command option listed. Double click on it to open the Add Command dialog box shown in Figure 11-5.
Figure 11-5. The Add Command dialog box.
The left side gives you a text editor to write the custom SQL statement. The SQL that is entered here is not modified by Crystal Reports at all. It is sent directly to the database server. Since each database server has a different set of syntax rules for its version of SQL, you have to make sure that what you enter is compliant with these rules.
When you click on the OK button to save the command, Crystal Reports sends the SQL statement to the database server to retrieve the records. Two things happen here. The first is that it checks whether you entered a valid SQL statement or not. If there is an error in your SQL statement, Crystal Reports alerts you to the exact error message returned by the database server and lets you go back and modify the SQL. If the SQL statement is error free, then Crystal reports retrieves the result set from the server and builds a field list that you can use to build the report.
Command objects can use parameters to filter out unwanted data on the server before it’s returned to Crystal Reports. The right side of the dialog box gives you the ability to add new parameters. To create a new parameter, simply click on the Create button. It opens a dialog box which prompts you for the parameter name, prompting text, data type and default value. Enter this information and click the OK button to save your changes. If you need to edit it or delete it, click on the Modify or Remove buttons respectively. You can add more parameters by clicking on the Create button again.
Once a parameter has been added, it appears in the Parameter List window on the right. To use it in your SQL statement, either type it in directly, or put your cursor where you want the parameter to appear and then double-click on it in the list. Crystal Reports will enter it into the SQL statement for you. Once you close this dialog box, you are prompted to enter the value for the parameter(s) used in the SQL statement. These values are passed to the database server with the rest of the SQL statement for processing.
If you have a lot of experience working with databases, then you might recognize the similarities between the command object and using stored procedures and wonder what the difference is between the two. To be honest, there really isn’t much difference. Both let you write custom SQL statements that are very complex. Command objects are for people who either don’t have permission to create stored procedures on the database server (database administrators are very protective of their servers), or you aren’t familiar with the database server administrative tools. Crystal Reports makes it easy for anyone to create new commands without having to be a database administrator to learn how to use the software.
