Up until this point, only reports based on a single table, view, stored procedure, or SQL Command have been discussed in this hour. However, it is quite a common occurrence to have several database objects in the same report. Crystal Reports treats all types of database objects as peers, which mean that a single report can contain multiple tables, views, stored procedures, and SQL Commands. Because all database objects are treated as peers, the term table will be used from now on to describe any of these database objects. Because of Crystal Reports' inherent basis on relational data, any time multiple tables are used, they must be linked together so that the sum of all database objects is a single set of relational records. The good news is that most of the time, Crystal Reports will take care of this automatically, and the report developer need not worry about linking. To see this in action, create a connection to the Xtreme Sample Database and add both the Customer and Orders tables to the report. When clicking Next in the Report Wizard, the linking between those tables is displayed as shown in Figure 3.8. Each table is represented by a window. In addition to the name, each field in the table is listed inside the window, and those fields that are defined as indexed fields in the database are marked with colored arrows. Any links defined between tables are represented as arrows connecting the key fields from two tables. Based on general database theory, linking to a field that is indexed will generally result in a better performing query. Figure 3.8. Linking multiple tables together.By default, Crystal Reports creates links based on name. In this case, both tables have fields with a name of Customer ID, so a link is already created. To accept this link, simply click Next to move to the next step in the Report Wizard. If there were not a common field name, selecting the By Key option and clicking Auto-Link would attempt to create a link based on the fields defined in the database as keys. If neither of these methods of automatic linking work, the link must be manually created. This is very simple to do: Simply drag the field to link from one table and drop it on to the field from a second table. After links are created, they can be configured by clicking on the link arrow connecting two tables (it will turn blue when selected), and then clicking the Link Options button. Links have two options: join type and link operator. These settings determine how Crystal Reports matches records from both tables. The default join type is an inner join, which means that only records with a matching key in both tables are included. The default link type is equal. For most cases, these two settings will not need to be modified. Understanding the Different Join TypesIn Crystal Reports, the Link tab of the Report Wizard (and Database Expert) provides a visual representation of the relationship between multiple database objects. Defining the appropriate join strategy for any given report should be reflective of the data within the database objects and on how the report needs to read and display that data. Join type settings allow you to more precisely control the query results based on your unique requirements. The following is a description of the most common types of joins:
|