Linking Tables

Although you may have rare instances in which you design a report based on just one database table, you usually need to use at least two, and often more, tables in your report, because most modern relational databases are normalized. Database normalization refers to breaking out repetitive database information into separate tables in the database for efficiency and maintenance reasons. Consider the following Employee table:

Employee Name	Department Name	Salary
Bill	Information Technology	50,000
Karen	Human Resources	32,500
Renee	Information Technology	37,500
John	Executive	85,000
Carl	Mail Room	24,000
Jim	Information Technology	48,000
Julie	Executive	87,000
Sally	Mail Room	23,500

Although this makes for a simple reporting environment, because you don t need to choose more than one table to print an employee roster or paycheck, it becomes more difficult to maintain. Notice that department names repeat several times throughout this small table. (Think about this same scenario for a 50,000-employee company!) This not only takes up a large amount of storage space, but if a department name changes, much work has to be done to make the change in this table. For example, if the Information Technology department changes its name to Information Systems, a search-and-replace function must be performed through the entire Employee table, replacing every occurrence of the old name with the new name.

Contrast this single-table layout with the following database environment:

Employee Table:

Employee Name	Department Number	Salary
Bill	25	50,000
Karen	17	32,500
Renee	25	37,500
John	8	85,000
Carl	13	24,000
Jim	25	48,000
Julie	8	87,000
Sally	13	23,500

Department Table:

Department Number	Department Name
8	Executive
13	Mail Room
17	Human Resources
25	Information Technology

Here, you can see that the database has been normalized by placing the department information in its own lookup table. In this environment, much less storage is used by the Employee table, because only a department number is stored for each employee, not the entire department name. And if the Information Technology department s name changes, only one record in the Department table has to be changed in the entire database.

Database Expert Links Tab

Using multiple tables complicates the reporting environment, because you need more than just the Employee table to print an employee roster or paycheck. In the preceding example, you not only have to include the two tables in your report, but must also link them together with a common field. Linking tables (often also known as joining tables) consists of choosing a common field or fields that will allow the second table to follow the main table as the main table is read record by record. You link tables in Crystal Reports with the Database Expert Links tab, illustrated in Figure 16-5.

Figure 16-5: The Database Expert Links tab

The Database Expert Links tab appears if you initially choose two or more tables when you first create a report, or whenever you choose additional tables within the Database Expert later in the report design process. If you want to work with database links at other times, simply display the Database Expert by clicking the Database Expert toolbar button or by choosing Database Database Expert from the pull-down menus (as discussed earlier in the chapter). Then, click the Links tab.

You are free to move the individual tables around in the Links tab if you wish to see them in a different organization. You can also resize each table window to make it taller, shorter, narrower, or wider. If you wish to have Crystal Reports automatically rearrange the tables according to the way they re linked, click the Auto-Arrange button.

The Database Expert Links tab typically choses links between the tables when you first display it. If you see lines with arrows connecting fields in the tables, the Database Expert Links tab has already automatically linked the tables for you (automatic linking is discussed later in the chapter). You may need to delete these existing links if they are incorrect, or add new links yourself.

To delete a link, click the line connecting the two tables. The link line, along with the fields it connects, will be highlighted. Click the Delete Link button or press the DEL key. If you want to change link options, such as the join type (discussed later in the chapter under Join Types, Link Types, and Join Enforcement ), the index used by the link, or multiple-table link behavior, click the Link Options button or double-click the selected link.

Tip  

You may also make choices from a pop-up menu by right-clicking on a selected link line.

To draw a new link, click a field in the table you want to link from, drag your mouse to the other table you want to link, and then drop onto the field you want to link to. A link line will be drawn between the two tables and fields.

If Crystal Reports detects no potential problems with the link you ve drawn, the link line simply appears and you see no messages. If, however, Crystal Reports detects a potential problem with the link, such as mismatched field types, you ll receive a warning message and the link may not be created.

Significance of the Index Arrows

Most database systems allow database fields to be indexed by the database designer. These indexed fields are indicated by colored arrows. An index is a special setting that the database designer creates to speed up access to a table. Searching for specific records from that table will be much faster when the search is based on an indexed field. The different colors of the index arrows indicate that different indexes have been created by the database designer for the table. To display a key to the index arrow colors, click the Index Legend button. The window will appear showing the different colors and their indexes.

When linking database tables together, you may link on any field you choose ”there is not a requirement to link to or from an indexed field. For performance reasons, however, you may still want to try to link to indexed fields anyway. You may see improved record retrieval speeds.

So, Which Tables and Fields Should I Link?

You'll quickly figure out that you have to be very familiar with the database you are reporting against to accurately link tables and fields. You have to know the layout of the tables and the data that the common fields contain to successfully link them. This task is complicated even further by database designers who insist on protecting their jobs by creating confusing and cryptic table and field names.

Probably the most expeditious approach is to consult with someone who either designed the database or is familiar with its layout and contents. Barring that, you may be able to discern the proper tables and fields to link if they are named logically. If nothing else, you can browse individual fields in the Database Expert Links tab by right-clicking a field name and choosing Browse Field from the pop-up menu. By looking for similar data types and sample data that seems to match up in both tables, you can find good candidates for links.

Always make sure you test your report and verify that correct data is being returned once you've linked tables. It's very easy to create an incorrect link that displays no error message but doesn't return the correctly matched data to the report.

 

Link Order

When you add tables to the report and then display the Links tab, Crystal Reports makes an assumption about the order in which you wish to link tables (this assumption is made only if Crystal Reports automatically links tables, which it will often do). If you are only linking two tables together, this will not be an issue, as there is only one link between two tables.

However, if you link three or more tables (tables A, B, and C in this example), Crystal Reports will link the tables in a certain order: perhaps first linking table A to table B on one field, and then linking table A to table C on another field. If the database you ve used in the report is based on the SQL language (discussed in more detail later in the chapter), you ll be able to view the SQL query that Crystal Reports sends to the database and see the order in which these links will occur.

In most cases, the link order won t be significant and you won t need to change it. However, in certain situations with certain types of table structures and database systems or connection drivers, you may notice a difference in the resulting data if you choose an an A to C, then A to B order instead of the A to B, then A to C order. If this is significant, you can change the order by clicking the Order Links button. Clicking this button will display the Order Links dialog box.

Select the link that you want to move up or down in the order and click the up or down arrow at the top of the dialog box. Click the Link Ordering is Enforced check box to ensure that Crystal Reports reorders the link statements in the SQL query being sent to the database.

Using Multiple Database Types in the Same Report

Crystal Reports doesn t limit you to using just one type of database per report. You may, for example, wish to get the main transaction table for your report from a client/server database using a direct access database driver, one smaller lookup table from a Microsoft Access database on a shared LAN drive, and another lookup table from a Microsoft Excel spreadsheet on your C drive via ODBC or the Access/Excel (DAO) connection.

To accomplish this, simply choose all the different tables from different categories of the Database Expert when you first create the report. Or to add additional tables (even from different database types) after you ve already started designing a report, simply redisplay the Database Expert using the toolbar button or pull-down menu options discussed earlier. After choosing the additional table or tables, click the Links tab and link them appropriately.

In general, this type of mixed reporting is perfectly acceptable. However, because connections to the different database types cannot be accomplished with a single SQL query (this is because a SQL query, by its nature, can t cross database boundaries), Crystal Reports will link the tables itself internally. In limited cases, this may require you to only link on string fields. Most often, however, mixed database usage will result in a warning message that your report contains more than one database type and that you can t use SQL Expressions or server-based grouping (both topics are discussed later in this chapter).

Does Crystal Reports Automatic Linking Work?

When you initially add tables to a report, Crystal Reports will attempt to link the tables automatically. The result will often be link lines appearing in the Links tab when you first display it, even before you draw any manual links in yourself. This is a feature with good intentions, but it is often more trouble than it's worth. This linking automatically links fields in two adjacent tables if the fields meet these criteria:

• The field names are exactly the same.

• The data types are identical.

• In the case of string fields, the field lengths are the same.

  In an ideal setting (such as the XTREME sample database provided with Crystal Reports), automatic linking works perfectly. In the real world, however, things are usually quite different.

  Consider, for example, a report that includes a Vendor table and a Customer table. Both tables contain fields named Address, City, State, and Zip_Code. It's perfectly conceivable that these fields have identical data types and field lengths. Automatic linking will dutifully link the two tables together on all four fields. But, these aren't the proper fields with which to link these two tables together.

  Another type of automatic linking that Crystal Reports will attempt is linking by key. In this scenario, Crystal Reports attempts to determine a primary key/foreign key relationship between tables by reading the internal database structure provided by the database driver. You must typically choose this form of automatic linking yourself by clicking the By Key radio button, and then clicking the Auto-Link button in the Link tab. If the specific set of primary key/foreign key settings can't be found by Crystal Reports, you'll receive a message indicating that key linking can't be performed and asking if you wish to link via the field name scenario.

  You'll probably find that automatic linking, either by key or by field name, often results in incorrect link lines being drawn. You then are forced to delete the incorrect links and draw in the correct ones. While Crystal Reports 8.5 and earlier allowed you to turn this auto linking option off with a dialog box choice, you must use the Windows Registry in later versions. Modify the Registry key HKEY_CURRENT_USER/Software/ Crystal Decisions/10.0/Crystal Reports/Database Options/DoAutoSmartLinking to make this change in Crystal Reports 10. Make sure you fully understand how to modify Windows Registry settings before attempting this change.

 

Join Types, Link Types, and Join Enforcement

When you link two tables, you must consider carefully what records will be returned from both tables. Consider a slight modification to the normalized table structures illustrated earlier in this chapter.

Employee Table:

Employee Name	Department Number	Salary
Karen	17	32,500
Renee	25	37,500
John	8	85,000
Carl	13	24,000
Denise	32	125,000

Department Table:

Department Number	Department Name
8	Executive
13	Mail Room
17	Human Resources
4	Finance
25	Information Technology

A quick glance at these two tables reveals two inconsistencies: Denise has no matching record in the Department table, and the Finance department has no employees in the Employee table. These two tables are said to lack referential integrity ”a fancy computer term that simply means the two tables don t completely match up. Fancy term or not, this will be very important to you as a report designer. You have to decide how you want to deal with a lack of referential integrity.

Note  

Many databases can enforce referential integrity, so the situation described previously will never happen. If the database designer chooses to enforce referential integrity between these two tables, an employee cannot be given a department number that doesn t exist in the Department table, and a department record can t be deleted from the Department table if any Employee table records still contain that department. However, enforcing referential integrity often introduces other complexities in the database, and there are many times when the basic function of the database will not allow referential integrity to be enforced. The database designer or administrator should be consulted if you have questions about the way your database is designed.

A situation in which you would be very concerned about referential integrity is if you were designing a report to print paychecks for employees. Consider the tables previously shown as the basis for your paychecks . If you wish to have the employee s department printed on the check stub to help in check distribution, you would need to link the Employee and Department tables together on the Department Number field. It s a fair assumption that your paycheck run would get at least this far:

The big question for you, the report designer, is What happens to Denise? (the last employee in the Employee table). Considering that she s a highly paid employee, at least from most viewpoints, she will probably be very interested in being paid, regardless of referential integrity. Another interesting question is Will any checks print for the Finance department? The answers to your questions are dependent upon which join type you use when linking these two tables together.

The following are the two join types that you will be concerned with most of the time:

• Inner join (sometimes referred to as an equal join ) Includes records from both tables only when the joining fields are equal.

• Left outer join (sometimes simply referred to as outer join ) Includes all records from the left table, and records from the right table only when the joining fields are equal.

Even though Denise probably doesn t know what a left outer join is, she probably will be much happier if you choose it. This will result in her receiving a paycheck that simply doesn t have a department name printed on it. This is particularly important in Crystal Reports, because the default join type for SQL databases is an equal join.

Two other types of joins that you may use less frequently are the following:

• Right outer join Includes all records from the right table, and records from the left table only when the joining fields are equal.

Denise would be as displeased with this choice as she would with an equal join. You would also get a wasted paycheck with the Finance department on the pay stub, but no employee or salary printed on it.

• Full outer join Includes all records from both tables, whether the joined fields are equal or not.

Denise wouldn t mind this joint type ”she d get paid. However, you d also have a wasted check for the Finance department with no employee information on it.

Choosing the Join and Link Type in the Database Expert Links Tab

Choose the join type in the Database Expert Links tab by double-clicking the link line between the two tables you are interested in, by right-clicking on the link line and choosing Link Options from the pop-up menu, or by selecting the link and clicking the Link Options button. The Link Options dialog box, shown in Figure 16-6, will appear. Select the desired join type from the left side of the dialog box.

Figure 16-6: Link Options dialog box

Note  

If tables are linked by more than one field, choosing a join type for any of the links will set the same join type for all the links. You cannot have different join types for multiple links between the same tables.

Although most typical business reporting can be accomplished with inner joins and left outer joins, you may have occasion in specialized reporting situations to use these other join types.

Crystal Reports Link Types

In addition to a join type, Crystal Reports provides the additional choice of a link type that can be chosen along with a join type.

• Equal (=) Returns records from the tables, matching records from the right table every time the joining field in the left table is equal to the joining field in the right table.

• Greater than ( > ) Returns records from the left table, matching records from the right table every time the joining field in the left table is greater than the joining field in the right table.

• Greater than or equal ( > =) Returns records from the left table, matching records from the right table every time the joining field in the left table is greater than or equal to the joining field in the right table.

• Less than ( < ) Returns records from the left table, matching records from the right table every time the joining field in the left table is less than the joining field in the right table.

• Less than or equal ( < =) Returns records from the left table, matching records from the right table every time the joining field in the left table is less than or equal to the joining field in the right table.

• Not equal (!=) Returns all combinations of records from the two tables where the joining fields are not equal.

Various combinations of join and link types can return very interesting combinations of data. For example, choosing an Inner Join but a Less Than link type will return a much larger set of records than an Inner Join with an Equal link type. However, it still won t return records where there is no match at all between link fields. But if you choose a Left Outer Join and a Less Than link type, you ll receive a larger number of matching records, as well as records from the left table that have no match in the right table.

You can choose Database Show SQL Query from the pull-down menus to see how the combination of join and link types will affect the SQL Query. If you are familiar with the structure of the SQL query, you ll notice that the combination of join and link types will change different portions of the query. For example, an Inner Join with an Equal link type will create this type of query:

 FROM   "xtreme"."dbo"."Employee" "Employee" INNER JOIN 
 "xtreme"."dbo"."Orders" "Orders" ON 
 "Employee"."Employee ID"="Orders"."Employee ID" 

And, a Left Outer Join using a Less Than link type will return this type of query:

 FROM   {oj "xtreme"."dbo"."Employee" "Employee" LEFT OUTER JOIN 
 "xtreme"."dbo"."Orders" "Orders" ON 
 "Employee"."Employee ID"<"Orders"."Employee ID"} 

Of course, the exact format of the SQL query and where the joins are performed (the FROM clause in some cases, the WHERE clause in others) is dependent upon the database type and driver being used. Also, you ll see very different sets of returned data for the different join and link types you choose. If you aren t extensively familiar with join and link types, you d be best advised to stick with tried and true Inner or Left Outer join types and Equal link types. In any event, you are well advised to validate the results of your report against your original data source to ensure that all the proper data is being returned by the report once you make join and link choices.

Note  

Some join and link types won t be available, depending on the types of databases you have chosen for your report. In those cases, the unavailable join and link types will be dimmed in the Link Options dialog box.

New Crystal Reports 10 Join Enforcement

If you re familiar with Crystal Reports versions prior to 10, you ll notice a new category of choices in the Link Options dialog box (shown previously in Figure 16-6 and following in Figure 16-7). The Enforce Join category offers four radio buttons that allow you to choose whether to include tables you ve added to the Database Expert in the eventual SQL query sent to the database, even if you haven t used any fields from the table on the report.

Figure 16-7: Example of un-enforced join

Previously, if you added several tables to the Database Expert but only included fields from one table on the report, the other tables weren t even referenced in the SQL query and any joins and links you chose in the Links tab wouldn t take effect. Now, you are given the choice of whether to enforce the joins and links, even if fields from all tables aren t included on the report.

Consider the example depicted in Figure 16-7. Two Oracle tables (Orders is the from table and Customer is the to table) are linked together on one common field (Customer_ID) using an Inner join and an Equal link. Notice that the join is not enforced, which is the default behavior and the way previous versions of Crystal Reports behaved. The final important piece of this example is what fields are placed on the report, used in a formula or record selection, or otherwise added to the report.

Depending on the choice you make by clicking one of the Enforce Join radio buttons, you ll potentially see different database records on the report, as well as a different SQL query being sent to the Oracle server:

• Not Enforced This choice does not add any extra tables to the SQL query that are not used elsewhere on the report. For example, if only the Order_ID field from the Orders table is added to the report, but no fields from the Customer table are used, the following SQL query will result:

   SELECT "Orders"."ORDER_ID" 
   FROM   "XTREME"."ORDERS" "Orders" 

  Notice that the Customer table is not included in the query and the join and link types chosen will not affect the set of data returned from the database.

• Enforced From This choice will include the FROM table in the query, enforcing its joins and links, even if no fields in the FROM table are used on the report. For example, if only the Customer_Name field from the Customer table is added to the report, and no fields from the Orders table are added, the following SQL query will result:

   SELECT "CUSTOMER"."CUSTOMER_NAME" 
   FROM   "XTREME"."ORDERS" "ORDERS", "XTREME"."CUSTOMER" "CUSTOMER" 
   WHERE  ("ORDERS"."CUSTOMER_ID"="CUSTOMER"."CUSTOMER_ID") 

  Notice that the Orders table is still included in the query and the join and link types chosen will affect the set of data returned from the database, even though no Orders table field is used on the report. The result is the inclusion of duplicate Customer Names in order to match each order record.

• Enforced To This choice will include the TO table in the query, enforcing its joins and links, even if no fields in the TO table are used on the report. For example, if only the Order_ID field from the Orders table is added to the report, and no fields from the Customer table are added, the following SQL query will result:

   SELECT "ORDERS"."ORDER_ID" 
   FROM   "XTREME"."ORDERS" "ORDERS", "XTREME"."CUSTOMER" "CUSTOMER" 
   WHERE  ("ORDERS"."CUSTOMER_ID"="CUSTOMER"."CUSTOMER_ID") 

  Notice that the Customer table is still included in the query and the join and link types chosen will affect the set of data returned from the database, even though no Customer table field is used on the report. The result is the inclusion of only order IDs that have a matching Customer table entry (because of the Inner Join), even though no Customer information is included on the report.

• Enforced Both This choice will include the FROM table and TO table in the query, enforcing their joins and links, regardless of which tables are included on the report. So, while the SELECT clause will be affected by whether you include fields from the Orders table, the Customer table, or both, the FROM and WHERE clauses will always refer to both tables:

   FROM   "XTREME"."ORDERS" "ORDERS", "XTREME"."CUSTOMER" "CUSTOMER" 
   WHERE  ("ORDERS"."CUSTOMER_ID"="CUSTOMER"."CUSTOMER_ID") 

The results will be a combination of only orders that have a matching customer record, and repetition of customer fields for each matching order.

Tip  

In earlier versions of Crystal Reports, you could still enforce links and joins without displaying data from all tables by simply adding fields from tables to the details section, and then suppressing their display. This would force the tables to be included in the SQL statement.

Which Should Be the Left From Table, and Which Should Be the Right To Table?

When you use the Links tab, you begin to draw a link by clicking a from table. You then drop the link onto the desired field in the to table. Initially, you may not be able to tell which table was dragged from and which table was dropped onto. In many cases, Crystal Reports won't indicate this if you are using the default Inner Join and Equal link types.

However, if you change to alternate join or link types, or you use certain databases or database drivers, you'll be able to see which table is the from and which is the to by looking carefully at the link line ”the from side of the line will display a small block, while the to side will display an arrow.

This begs the question, Does it make any difference which is the ˜from' and which is the ˜to' table? The answer, again, relates to the join and link types you choose, and whether you choose to enforce joins. Generally speaking, if you select an Inner Join and an Equal link and don't enforce them, it doesn't make a great deal of difference. However, if you use any other join or link types, or enforce the joins, it makes a great deal of difference, because the from or left table and the to or right table determine how the SQL query is formed and what records are returned.

A relationship between tables is often referred to as a one-to-one relationship or a one-to-many relationship. If there is always only one matching record in both tables, it doesn't matter which table is the from and which is the to ”it's a one-to-one relationship. However, if there are many matching records in one table for each record in another table, the direction of the link is significant. For example, if a table containing many orders placed by the same customer is linked to a table containing only one matching customer record, you will want to link from the Orders table (the many table) to the Customer table (the one table).

If you're concerned that the link may be in reverse order, and you'd like to switch the from and to tables, you can simply delete and redraw the link, or right-click the link and choose Reverse Link from the pop-up menu.