Beyond a Single Table
 |  |  |  |
 |
| |||||||||
 |
| ||
Some business questions cannot be answered by pulling data from just a single table. Database designers build tables to follow a series of rules in a process called normalization. In addition to this, they build in relationships to represent business rules. For example, if a company was tracking information about its inventory and used part numbers to identify the products they built and sold, a business rule might be that each part number must be unique and identify only one specific part. This business rule makes logical sense outside the database world, because we wouldn’t want to go looking for part number 2741 and find that it was associated with both a chair and a refrigerator in our inventory. To code the business rule, a database designer builds constraints into the database tables to prevent duplication of part numbers.
An example of a business rule in the VistaNations database is that one three-character region code corresponds to one textual description of the region. With this in mind, let’s take a look at a business question that will help demonstrate how to work with multiple tables.
Business Question: In addition to the region code, please display the region description on all reports.
To answer a question like this, you first need to identify which tables hold the data you want to use in your report. To do this, you generally use a database schema, which is a visual representation of the tables, usually printed on a piece of paper, showing how all the tables in a database are related to one another. If a database schema exists for your database, you are truly leading a charmed life. In many cases, however, one doesn’t exist, and you need to reverse-engineer one to identify the business relationships built into the table design. When a database designer creates the schema, they are defining the relationships between tables as well as the business rules. Relationships between tables are made by linking, or joining, fields that are common to a set of tables.
Visually Linking Tables
Crystal Reports has a mechanism for helping you visualize the tables and view or create relationships between them called the Links dialog, shown in Figure 9.3. When multiple tables are selected with the Database Expert, a tab for Links is added. When you’re working with a single table, this tab does not display.
| Note | The Database Expert and Data Explorer dialog windows are very similar in look and in purpose. It is the Database Expert that contains the Links tab. The Database Expert opens when you choose Database > Database Expert from the menu. The Data Explorer opens when you choose Database > Log On or Off Server from the menu. Both show a tree-like approach to Current Connections, Repository, Favorites, History, and Create New Connection options. The Data Explorer provides the additional capability to log onto and off of a server. Crystal remains connected to a database it connects to until you log off of it. |
Figure 9.3. Database Expert - Data tab
You can see in Figure 9.3 that two tables have been selected for use in this report, the Resorts table and the Regions table. You can add as many tables as you need to the report. When you switch to the Links tab, shown in Figure 9.4, the Links dialog presents visual representations of all the tables being used in the report.
Figure 9.4. Database Expert - Links dialog
| Note | The Links dialog replaces the Visual Linking Expert present in prior releases of Crystal Reports. |
From a visual look at the tables, you can see the table names and all the field names. The rectangles that represent the tables can be resized to show more or less data, and vertical and horizontal scroll bars automatically appear when they’re needed. You can also reposition the tables and line them up horizontally instead of vertically.
This peek at the links between the tables is telling you that there is apparently no relationship between the Regions table and the Resorts table. If the Links dialog noticed a relationship between any fields, you would see a line from the field in one table to a field in the other table. While there actually is a relationship between the two tables, the Links dialog didn’t have enough information available to figure it out. The Links dialog isn’t as smart as a human who has knowledge of the data, so it did the best it could.
The Links dialog uses the following two items to figure out if there is a relationship between tables:
-
Identical match on field name
-
Primary and foreign keys that exist in the tables themselves
Field Name Matching If a field with an identical name exists in two tables, Crystal creates a link between the two fields if the data types also match.
Key Matching Some databases allow for the option of creating primary keys and foreign keys in a table. If these keys exist, Crystal Reports can use them to link the tables. This is the preferred way to do linking because it ties directly back to the business relationships intended in the tables when the database designer built them. The design purpose of a primary key is to guarantee a unique identifying value for every record in a table. Foreign keys are specifically used in joins, and they link from one table (often called a secondary table) back to another table (called a primary table). Primary keys and foreign keys work together to define the business relationships between tables.
Take a peek back at Figure 9.4. No links were created. In this case, there were no matching field names between the two tables and there were no keys defined in them. Hang on, that doesn’t mean there isn’t a relationship! All it means is that the Links dialog could not help build the links. The Links dialog may not have enough information to build the links at all or it could make false assumptions and build the links incorrectly.
| Tip | Whenever the Links dialog is used, assume that you need to verify the accuracy of what it is showing you. The Links dialog is not infallible, and its link choices must be validated by the report designer. |
The buttons along the right side of the dialog shown in Figure 9.4 will help you verify, validate, and correct the links. Table 9.1 describes what each of the buttons does.
| Button | Description |
|---|---|
| Auto-Arrange | Repositions the tables within the white area using link information; you can maximize the screen in order to provide the most amount of room for viewing purposes. |
| Auto-Link | Creates links between fields either by name or based on primary/foreign keys. |
| Order Links | Chooses the order in which the links will be processed, from first to last. This option is not available if there is only one link between tables. |
| Clear Links | Removes all links. This option is not available if there are no links between tables. |
| Delete Link | Deletes a specific link that you select. This option is only available when a link is selected. |
| Link Options | Changes the type of join and link type between the tables. |
| Index Legend | Identifies the five-sided geometric shape to the left of fields that are indexed. |
Valid Links
The Links dialog makes an initial attempt at automatically creating links using either the field name or database keys as a guide. If database keys are used, the links will be accurate and will follow the design rules of the database schema. If just the name is used, however, the links have a reasonable chance of either not being created or being invalid for the intention of the data. Why? Let’s start with the name. In Figure 9.4, there is a field called RegionCode in the Regions table and a field called Region in the Resorts table. The Links dialog will not connect these two as a link because the field names are different.
As someone who works with the data, a report designer would browse the data, figure out that it was the same, and declare that the two fields should be linked regardless of the difference in names. You can browse the field data from the Links dialog by right-clicking the field and choosing Browse Field, as shown in Figure 9.5. Also visible is an index marker on the PostalCode field.
Figure 9.5. Browse Field
Browsing field data lets you inspect the actual data stored in the table for that column, as shown in Figure 9.6. If the data in two fields in different tables is the same, then a link can be made. There is no requirement in the world of relational tables that fields from two different tables must share the same name just because they share the same purpose. That is the case here. The purpose, or intent, of the RegionCode and Region fields is identical; they both contain a three-character code that identifies a region where resorts are located around the world.
Figure 9.6. Sample data
To create a valid link between two fields, the data type of the two fields must match one another. The RegionCode field has a data type and can only be linked to a field of this same data type.
The other very important concept in creating a valid link is that the intent of the two fields involved in the link must be the same. As an example, assume that in one table you have a field called PostalCode that is defined as String with a length of 5. In another table, you have a field called CompassDirections, which is also defined as String of length 5. The Links dialog in Crystal Reports will allow you to create a link between these two fields because the data type matches; however, the intent and purpose of the data do not match. The PostalCode field will contain values like 12508 and 06644, while the CompassDirections field will contain values like North and South. Clearly, the intent of the data is different, yet Crystal allows a link to be made between the two fields.
This is where knowledge of the data and the business rules that the data is modeling is crucial. It is also the human element involved in report designing. There is no way to build a good report if you’ve linked fields incorrectly. So, there are two ways you can potentially end up with bad links: You, the report designer, create them because you don’t understand the data, or the Links dialog creates them because fields are named the same but are used for different purposes.
| Warning | The toolbar icon used to identify the Database Expert in this version of Crystal Reports was previously used to enable the Visual Linking Expert. The Visual Linking Expert has been replaced by the Links dialog on the Database Expert. |
Manually Building Links
Manually building links is the surest way of guaranteeing that the linking respects the business relationships intended by the data, given that you are knowledgeable of the meaning of the data. To create a manual link, use a drag-and-drop technique: Click the field you want to link from and drag it to the field you want to link to. It is generally easiest if the tables are arranged in left-to-right order with the links flowing from left to right as well. As you drag from the starting field, the mouse pointer will change to a circle with a line through it when you are over a place where you cannot link and will change to a shortcut arrow, shown in Figure 9.7, when you are over a valid drop point.
Figure 9.7. Creating a manual link
In Figure 9.7, a manual link is being created between the RegionCode field in the Regions table and the Region field in the Resorts table. Once the link is created, a black line with an arrow from one field to the other field appears between the tables.
Automatically Building Links
Crystal defaults to building automatic links between tables whenever possible. When two tables are added in the Database Expert that share a common field name, the Links dialog will automatically show a link between the two tables. In Figure 9.8, the Resorts table and the UnitDescription table both contain the field ResortCode, and the Links dialog has connected the two tables using this field. Also notice in Figure 9.8 that the ResortCode and PostalCode fields have an index associated with them. Since one of the indexed fields is involved in the link, you are guaranteed of having the fastest possible data retrieval.
Figure 9.8. Automatic links
After adding new tables to the report, attempt to update the links using the Auto-Link button and associated radio button choices. If no keys are defined on the tables, enabling the By Key radio button will have no effect.
When a set of tables is linked by more than one common field, the Order Links button (shown as unavailable in Figure 9.8) lets you set the order in which the links should be carried out first, then second, etc. The Order Links option is also available when three or more tables are linked and can be used to control which set of links between tables are evaluated before the others. Changing the link order of tables may result in a change to the data that is returned from the database.
| Note | Crystal Reports does not allow Crystal queries (a feature of the product for releases 8.5 and earlier) to link to other database tables, views, stored procedures, and other Crystal queries. To work around this constraint, use a linked subreport embedded in a main report where the subreport and main report have separate data sources. See Chapter 7, “Selectively Refining a Report Using Data Values,” for information on how to link subreports to a main report. |
Indexed Fields
Linking to a field in a table that has an index will speed the retrieval of data from the database, so if an indexed field is available, try to use it. An index is created by a database designer or database administrator and can involve a single field or a group of fields. Crystal denotes an index in the Links dialog with a five-sided geometric shape (a sideways doghouse?) that we’ll call an index arrow. Index arrows are pentagons that are color-coded; the color tells you whether it was the first index created, second index created, etc., if there are more than eight indexes, or if there are multiple indexes. The color relationship is described in Table 9.2.
| Color | Meaning |
|---|---|
| Red | First index |
| Yellow | Second index |
| Blue | Third index |
| Green | Fourth index |
| Pink | Fifth index |
| Orange | Sixth index |
| Dark Grey | Seventh index |
| Light Grey | Eighth index |
| White | More than eight indexes |
| Black | Multiple indexes on same field |
| Note | It is important to note that indexes are created at the database level and not in Crystal Reports. Crystal can use only existing indexes. |
Link Types
When a join between tables is created using a field, the field value in both tables must match. For instance, if the value in the ResortCode field in the Resorts table is TRM and the value in the ResortCode field in the UnitDescription table is also TRM, a join can be performed. This is referred to as an equal link. Once a link is created, however, the link type can be changed using the Link Options button visible in Figure 9.8. There are six link types, which are depicted mathematically in Crystal, as shown in Figure 9.9, and which are explained below.
Figure 9.9. Link types
Equal The values in the two fields involved in the link are identical. For instance, "TRM" = "TRM".
Greater Than The value in the first field (the from field) is greater than the value in the second field (the to field). For instance, "TRM" > "RCB" for string data and 30 > 25 for numerical data.
Greater Than or Equal The value in the first field (the from field) is greater than or equal to the value in the second field (the to field). For instance, "TRM" >= "RCB" for string data and 30 >= 25 for numerical data.
Less Than The value in the first field (the from field) is less than the value in the second field (the to field). For instance, "TRM" < "ZEN" for string data and 25 < 30 for numerical data.
Less Than or Equal The value in the first field (the from field) is less than or equal to the value in the second field (the to field). For instance, "TRM" <= "ZEN" for string data and 25 <= 30 for numerical data.
Not Equal The value in the first field (the from field) is not equal to the value in the second field (the to field). For instance, "TRM" != "ZEN" for string data and 25 != 30 for numerical data. The symbol <> may also be used if the underlying database does not support the != symbol.
| Note | If a data source was created using a SQL statement that used inequalities on the linking field, the default link type will match the inequality used in the SQL statement. The link type is determined by the WHERE clause in a SQL statement. SQL statements and WHERE clauses are covered in Chapter 11, "Using SQL in Crystal." |
| ||
 | |
| Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement | |
| Conello © 2000-2003 Feedback | |
EAN: 2147483647
Pages: 217