Working with SQL Statements
Crystal Reports talks to databases using the computer language call Structured Query Language, commonly referred to as SQL. With SQL, Crystal Reports can specify the data that it wants to retrieve as well as how it should be filtered and sorted. As a user, you aren’t required to understand SQL because Crystal Reports does all this for you behind the scenes. But even though you aren’t required to know it, being able to use it can help you write more sophisticated reports and improve performance. Just as you aren’t required to be an auto mechanic to drive a car, knowing how they work can help get you out of a jam when you’re stuck on the side of the road. This section of the chapter gives you the basics of SQL so that you can get a better understanding of how Crystal Reports talks to databases. But this is by no means a complete tutorial. If you want to learn SQL so well that you become comfortable writing it all on your own, then you can find plenty of helpful books about it at the bookstore.
Although SQL can be quite complex, for our purposes we will focus on three distinct parts: table selection, filtering records, and sorting/grouping. Each of these parts is identified by a SQL keyword. They are listed in Table 10-8.
Table 10-8. The three primary SQL keywords
| SQL Keyword | Description |
|---|---|
| SELECT…FROM | Specifies the tables and fields to retrieve. |
| WHERE | Filters the data so that only specific records are returned. |
| ORDER BY / GROUP BY | Performs sorting and grouping |
The following sections explain these three parts of the SQL statement and show the options for customizing them.
Selecting Tables and Fields
The SELECT … FROM portion of the SQL statement is created in the Database Expert dialog box when selecting the tables and fields to print. For example, if you told the Database Expert that you wanted to use the tblCustomer table and print the fields CustomerID and CustomerName, then the SQL statement would look like this:
The first portion of the statement is the SELECT keyword. It specifies the fields to display. The next keyword is FROM. It specifies which table to pull the data from.
A good way to understand how SQL works is to look at the SQL statements that Crystal Reports creates for your reports. You can get a “behind the scenes” look at what Crystal Reports is doing by selecting the menu items Database > Show SQL Query. This opens the Show SQL Query dialog box. Figure 10-11 shows the exact SQL statement that Crystal Reports uses for the customer example we just discussed.
Figure 10-11. The Show SQL Query dialog box.
This dialog box displays the SELECT…FROM portion of the SQL statement, but it doesn’t let you change it (it is read-only). Notice how it is a bit more verbose than my previous example. Crystal Reports always lists the table name in front of each field and puts a lot of quotes around everything. This is a very formal version of SQL that Crystal Reports uses. As you create more complex reports, you’ll see how the corresponding SQL statement becomes more complex as well.
