Working with database tables and SQL

The procedures and images in these topics reflect Crystal Reports XI Release 2. For more information on later versions, see the corresponding User Guide available on the Crystal Reports documentation site.

Table linking

In most cases, reports contain data from multiple database tables. They need to be linked together based on common fields.

For example, suppose you want to include the Bill To customer’s phone number on a report that only references the TLORDER table. The phone number resides in the CLIENT table.

Go to Database > Database Expert.
In the Available Data Sources list, go to Current Connections > TMWIN > Tables.
Locate and double-click CLIENT. It is added to the Selected Tables list along with TLORDER.
On the Links tab, both tables are present but unlinked. The common link between them is TLORDER.BILL_TO_CODE and CLIENT.CLIENT_ID.

In the TLORDER table, locate the BILL_TO_CODE field and drag it to the CLIENT_ID field in the CLIENT table.
Click OK. When prompted, refresh your data.

You now have access to fields in the CLIENT table.

clienttbl

Table joins used in TruckMate reporting

The two types of links, or joins, used in TruckMate reporting are:

INNER JOIN
Returns records that are common in both tables
LEFT OUTER JOIN
Returns all records in your "main" table and the common records in your "lookup" table

In the example above, the two tables represent an INNER JOIN. To change it to a LEFT OUTER JOIN:

Go to Database > Database Expert.
In the Database Expert window, click the Links tab.
Click the line that links the tables together.
Click Link Options.
On the Link Options window, change the join type to Left Outer Join. Leave the other options as is, then click OK.
Click OK on the Database Expert window.

SQL expression fields

If you have a good grasp of SQL, expressions are helpful when you want to use data that is not easily accessed through table linking.

In this example, we will create an expression field that shows the total fuel surcharge for each freight bill.

In the Field Explorer window, right-click SQL Expression Fields and select New.
The SQL Expression Name window opens.
Enter a name for the expression.
Click OK.
The SQL Expression Editor opens.
Use the features on this window to build the following expression:

(SELECT SUM(CHARGE_AMOUNT) FROM ACHARGE_TLORDER WHERE ACODE_ID LIKE 'FUEL%' AND DETAIL_LINE_ID = )

Note that the expression must be encased in parentheses.
Click Save and Close.
Drag the field from the Field Explorer to the report.

Preview the report and make adjustments as needed.

Command objects

Command objects are useful if you prefer using SQL to table linking, or if you are working with complex reports.

Open the Database Expert.
On the Data page of the Standard Report Creation Wizard, select Add Command.

The Add Command To Report window opens.
Type your SQL query into the field as indicated.

Note: Any SELECT statement supported by DB2 works within a command object. Keep this in mind if you convert a Command Center KPI to a Crystal Report.
Click OK.
Click OK in Database Expert.

The command object appears in the Field Explorer under a Command table.

Core tables and links

If you are having trouble locating tables for your report:

In TruckMate, go to the application and grid that contains the field(s) you want to include in your report.
Right-click on the field.
From the shortcut menu, select Show Grid’s SQL.

Remember to use the _ALL views in place of a table if you are creating a report showing data from multiple companies.

These illustrations show some common table links. Click an illustration to enlarge it.