Working with Links in Crystal Reports

With all the background information in this chapter, you may worry that linking multiple tables is difficult. Actually, once you understand the relationships between your database tables, Crystal Reports makes the process of linking them together relatively easy.

In Crystal Reports, you link multiple tables together using a tool that you explored earlier in Chapter 2. This tool is none other than the Database Expert.

To get a feel for writing multi-tabled reports, let's create a product inventory report. To create such a report, you'll need to select a few tables from the xtreme.mdb database and then successfully link the matching fields within these tables.

To begin, start Crystal Reports and create a new blank report. Use the xtreme.mdb file as your data source. Once you're in the Database Expert, perform the following steps:

1. Double-click the Tables node of the xtreme.mdb connection.

2. Add the Product, Product_Type, and Supplier tables.

   Click the Add (>) button to move these tables to the Selected Tables list box. Once you add these three tables, you see two tabs display at the top of the Database Expert: the Data tab and the Links tab.

   The Data tab enables you to define your data source connection, whereas the Links tab enables you to define how your tables link together. Your screen should look like Figure 6-2.

   
   Figure 6-2: The Database Expert with the Data and Links tabs available

3. Select the Links tab.

By default, Crystal Reports creates links based on similar field names (a process known as Smart Linking).

The Smart Linking Feature

In this example, both the Product and Product Type tables have a Product Type ID, thus Crystal Reports' Smart Linking feature is applied. The Smart Linking feature automatically selects links for your tables, based on the following criteria:

• The field names are the same

• The field's data types are identical

• The field lengths are the same size (in the case of string fields)

The Smart Linking feature is a useful tool. However, you should still check with your database documentation to ensure that Crystal Reports has linked your tables correctly. Crystal Reports doesn't always guess correctly on how tables should link together.

Caution

As an example of how Crystal Reports might incorrectly smart link two tables together, consider a report that contains both Employee and Customer tables that might both include fields named Address, City, and State. These fields might very well have the same data types and field lengths in both tables. As a result, the Smart Linking feature would attempt to link the tables using these three fields.

The problem with these links is that just because the field names and data types are the same, it doesn't mean the fields are related. An employee's address is not the same as a customer's address.

Don't assume that the Smart Linking feature always links your reporting tables correctly. Make it a habit to always double-check how Crystal Reports links your tables together.

Removing Links

If for some reason you don't like the way Crystal Reports linked your database objects together, you can clear the links. To clear all links, perform the following steps:

1. Press the Clear Links button.

2. A message box displays.

   The message box asks if you're sure you want to remove all links.

3. Press the Yes button on the message box.

   You'll notice all links removed from your tables, as shown in Figure 6-3.

   
   Figure 6-3: Clearing all links within the Database Expert

   When you're satisfied with the results, press the OK button. The links are now officially removed from your report.

Of course, Crystal Reports also allows you to delete one link at a time, rather than removing all links at once. To delete one link at a time, perform the following steps:

1. Press the Auto-Link button to have Crystal Reports smart link your tables.

2. Left-click on the link that joins the two Supplier ID fields.

   This link joins the Product and Supplier tables. Notice that the Delete Link button now becomes active.

3. Press the Delete Link button.

   You'll notice only the Supplier ID link is removed, as shown in Figure 6-4.

   
   Figure 6-4: Deleting a single link within the Database Expert

   Tip

   You can also delete a single link by left-clicking on the link and then pressing the Delete key on your keyboard.

Adding Links

While the Smart Linking feature is helpful, you can also add and remove links manually. Linking tables together in Crystal Reports is as easy as drawing a line with your mouse. To link two tables together, perform the following steps:

1. Left-click and hold your cursor on the Supplier ID field.

   This field is located in the Product table.

2. Drag your mouse to the other Supplier ID field.

   This field is located in the Supplier table. Notice a link being created as you drag your mouse.

3. After you've connected these two fields, release the left mouse button.

   Figure 6-5 displays the results of your work.

   
   Figure 6-5: Adding links in the Database Expert

When you're satisfied with the results, press the OK button. The links are now officially added to your report.

Note

The table from which you're linking is referred to as the source or parent table. The table to which you're linking is referred to as the target or child table.