• No results found

Crystal Reports. Overview. Contents. Table Linking in Crystal Reports

N/A
N/A
Protected

Academic year: 2021

Share "Crystal Reports. Overview. Contents. Table Linking in Crystal Reports"

Copied!
19
0
0

Loading.... (view fulltext now)

Full text

(1)

Crystal Reports

Table Linking in Crystal Reports

Overview

This document demonstrates the linking process in Crystal Reports (CR) 7 and later. This document discusses linking for PC-type databases, ODBC linking and frequently asked questions.

The linking processes are based on matching records, from a parent table (left table) to records in a child table (right table). The match is based on whether the value in the link field in the source record is identical to the value of the link field in the searched records. The linking process is a form of record selection.

Contents

INTRODUCTION... 3

GENERAL LINKING OVERVIEW... 3

Link Relationships...3

One-to-one ... 4

One-to-Many... 4

ODBCLINKING... 4

What is ODBC? ...4

SQL Join Types – ODBC connection ...4

Equal [=] join... 5

Left Outer [=(+), *=] join ... 5

Right Outer [(+)=, =*] join ... 6

Greater Than [>] join ... 6

Less Than [<] join... 7

Greater Than Or Equal [>=] join... 7

Less Than Or Equal [<=] join ... 8

Not Equal [!=, <>] join ... 8

HOW MULTIPLE TABLES AND MULTIPLE JOINS BETWEEN TABLES AFFECT THE RECORD SET RETURNED... 9

Join Types Between Two Tables ...9

Two equal joins between two tables...10

Two Left Outer joins between two tables ...10

Multiple Tables ...10

Equal Joins between tables A, B, and C... 11

Equal Join between tables A and B and Left Outer join between tables B and C ... 11

Left Outer Join between all three tables... 12

Left Outer Join between tables A and B and Equal Join between tables B and C ... 12

(2)

NATIVE (DIRECT)LINKING FOR PC-TYPE DATABASES... 12

Linking Between Two Tables ...13

The A to B Relationship ... 13

Linking Between Three Tables...14

The A to B to C Relationship... 14

The A to B, A to C Relationship... 15

Look up both at the same time ... 15

Look up all of one then all the others... 16

Look up all combinations of the two... 16

FREQUENTLY ASKED LINKING QUESTIONS... 17

Can you link tables from different databases? ... 17

Is it better to connect natively or through ODBC?... 18

How do I know which fields I should link? ... 18

What if I cannot link the fields?... 18

What are those colored markers? ... 18

All link warnings were ignored and the report still works. Is this OK? ... 18

What is smart linking? ... 18

(3)

Introduction

Linking tables is not required when reporting off only one database table. However, it is often necessary to report off more than one database table. To report off more than one table in a single report, the tables MUST be linked. The linking process defines the relationship between the database tables. The relationship compares records between the tables.

The available linking options in Crystal Reports differ with the type of

connection to the database. In other words, the linking options are different for ODBC and native (direct) connections.

NOTE VERIFY IF THE REPORT IS USING A NATIVE OR ODBC CONNECTION IN CR Under the Database command, left-click the ‘Convert Database Driver’ option.

In the ‘Convert Database Driver’ dialog box, verify what the text is beside the grayed out “From:” line.

If the text is “PDSODBC.DLL” then the report is using an ODBC connection. If the text is anything else, then the report is using a native (direct) connection. * *”PDSOLEDB.DLL” is another possible type of connection. This is an OLE DB connection.

General Linking Overview

Link tables to compare records from one database table to another database table.

Using tables from Xtreme.mdb, the sample database that is shipped with Crystal Reports, select the Orders table and the Customers table to create a new report. These tables can be linked to match all the orders with the customer.

This is achieved by linking based on a field that is common to both tables. When linking tables, the link starts from one table to another. The from table is considered the parent table (also referred to as the primary table or the left table) and the to table is the child table (also referred to as the lookup table or the right table).

Which table do you use as the parent table? This depends on the type of link relationship that is required to return the correct records.

Link Relationships

When records are linked from one table to another table they will have a relationship type of either:

• One-to-one • One-to-many

(4)

One-to-one

In a one-to-one relationship between records in two linked tables, for every record in the primary table (left) there is only one matching record in the lookup table (right).

For example, the Xtreme.mdb database contains the Product and Product_Type tables. These two tables are linkable on the common Product Type ID field. For each Product Type ID there is only one Product Type Name. This is considered to be a one-to-one relationship.

One-to-Many

In a one-to-many relationship between fields in two linked tables, for every record in the primary table (left), there can be multiple matching records in the lookup table (right).

For example, the Xtreme.mdb contains the Orders and the Orders_Detail tables. The two tables are linked by the Order ID field. For each Order ID there can be multiple Order ID’s in the Orders Details table. This is considered to be a one-to-many relationship.

ODBC Linking

What is ODBC?

ODBC stands for “Open Database Connectivity”. It is a standard developed by Microsoft through which different types of data can be accessed. An application and a database driver communicate through a set of ODBC files known as the ODBC layer. The ODBC layer uses a common language, SQL (Structured Query Language) to communicate to any type of database.

For more information on ODBC, visit the following link: Open Database Connectivity (ODBC) Architecture -

http://support.crystaldecisions.com/communityCS/TechnicalPapers/cr_odbc_arc hitecture.pdf.asp

SQL Join Types – ODBC connection

To link tables, there are 8 different ODBC join types: • Equal Join

• Left Outer • Right Outer • Greater Than • Less Than

• Greater Than or Equal • Less Than or Equal • Not Equal

(5)

To change the join type in Crystal Reports:

1. Under the Database command, left-click the ‘Visual Linking Expert’ option. 2. In the ‘Visual Linking Expert’ dialog box, right-click on the link, which is

represented by an arrow, and left-click ‘Options’.

3. In the ‘Link Options’ dialog box, select one of the available SQL join types. Each different join type affects record selection between two tables. The two sample tables below represent the records, as they are stored in the database. The two sample tables are used to demonstrate how each join type affects record selection.

Sample Database Tables

Color_ID Color Color_ID Fruit_Veg

1 Orange 1 Orange 2 Red 2 Apple 3 Blue 2 Strawberry 4 Green 4 Lime 4 Broccoli 5 Banana

Equal [=] join

The record set from an equal join includes all the records where the linked field value in both tables is an exact match. All records where there is an exact match between the linked field values of two tables are returned.

Notice that there are no blue fruits or vegetables in the second sample table therefore, no matching records are returned for 3 Blue. Also 5 Banana is not returned because there is no matching record in the first sample table. Record Set Returned From an Equal Join

Color ID Color Color ID Fruit_Veg

1 Orange 1 Orange

2 Red 2 Apple

2 Red 2 Strawberry

4 Green 4 Lime

4 Green 4 Broccoli

Left Outer [=(+), *=] join

The record set from a left outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the primary (left) table for which the linked field value has no match in the lookup (right) table.

Notice that 3 blue from the left hand table is now returned. 5 Banana from the right hand table is still not returned, as the record is not from the left table.

(6)

Record Set Returned From a Left Outer Join Color ID Color Color ID Fruit_Veg

1 Orange 1 Orange 2 Red 2 Apple 2 Red 2 Strawberry 3 Blue 4 Green 4 Lime 4 Green 4 Broccoli

Right Outer [(+)=, =*] join

The record set from a right outer join includes all the records in which the linked field value in both tables is an exact match. It also includes a row for every record in the lookup (right) table for which the linked field value has no match in the primary (left) table.

Notice that 5 banana is returned because it is in the right hand table but 3 blue is not included in the record set because there is no matching record in the right table.

Record Set Returned From a Right Outer Join Color ID Color Color ID Fruit_Veg

1 Orange 1 Orange 2 Red 2 Apple 2 Red 2 Strawberry 4 Green 4 Lime 4 Green 4 Broccoli 5 Banana

Greater Than [>] join

The record set from a greater than join includes all records in which the linked field value from the primary table is greater than the linked field value in the lookup table.

Records are returned when the Color ID of the LEFT table is greater than the Color ID of the RIGHT table.

Record Set Returned From a Greater Than Join Color ID Color Color ID Fruit_Veg

2 Red 1 Orange 3 Blue 1 Orange 3 Blue 2 Apple 3 Blue 2 Strawberry 4 Green 1 Orange 4 Green 2 Apple 4 Green 2 Strawberry

(7)

Less Than [<] join

The result set from a Less Than join includes all records in which the linked field value in the primary table is less than the linked field value in the lookup table.

Records are returned when the Color ID of the LEFT table is LESS than the Color ID of the RIGHT table.

Record Set Returned From a Less Than Join Color_ID Color Color_ID Fruit_veg

1 Orange 2 Apple 1 Orange 2 Strawberry 1 Orange 4 Lime 1 Orange 4 Broccoli 1 Orange 5 Banana 2 Red 4 Lime 2 Red 4 Broccoli 2 Red 5 Banana 3 Blue 4 Lime 3 Blue 4 Broccoli 3 Blue 5 Banana 4 Green 5 Banana

Greater Than Or Equal [>=] join

The result set from a Greater Than Or Equal join includes all records in which the linked field value in the primary table is greater than or equal to the linked field value in the lookup table.

Records are returned when the Color ID of the LEFT table is greater than or equal to the color ID of the RIGHT table.

Record Set Returned From Greater Than or Equal To Join

Color ID Color Color ID Fruit_Veg

1 Orange 1 Orange 2 Red 1 Orange 2 Red 2 Apple 2 Red 2 Strawberry 3 Blue 1 Orange 3 Blue 2 Apple 3 Blue 2 Strawberry 4 Green 1 Orange 4 Green 2 Apple 4 Green 2 Strawberry 4 Green 4 Lime 4 Green 4 Broccoli

(8)

Less Than Or Equal [<=] join

The result set from a Less Than Or Equal join includes all records in which the linked field value in the primary table is less than or equal to the linked field value in the lookup table.

Records are returned when the Color ID of the LEFT table is LESS than or Equal to the Color ID of the RIGHT table.

Record Set Returned From Less Than or Equal To Join Color_ID Color Color_ID Color

1 Orange 1 Orange 1 Orange 2 Apple 1 Orange 2 Strawberry 1 Orange 4 Lime 1 Orange 4 Broccoli 1 Orange 5 Banana 2 Red 2 Apple 2 Red 2 Strawberry 2 Red 4 Lime 2 Red 4 Broccoli 2 Red 5 Banana 3 Blue 4 Lime 3 Blue 4 Broccoli 3 Blue 5 Banana 4 Green 4 Lime 4 Green 4 Broccoli 4 Green 5 Banana

Not Equal [!=, <>] join

The result set from a Not Equal join includes all records in which the linked field value in the primary table is not equal to the linked field value in the lookup table.

Records are returned when the Color ID of the LEFT table is NOT EQUAL to the Color ID of the RIGHT table.

(9)

Record Set Returned From Not Equal Join Color ID Color Color ID Fruit_Veg

1 Orange 2 Apple 1 Orange 2 Strawberry 1 Orange 4 Lime 1 Orange 4 Broccoli 1 Orange 5 Banana 2 Red 1 Orange 2 Red 4 Lime 2 Red 4 Broccoli 2 Red 5 Banana 3 Blue 1 Orange 3 Blue 2 Apple 3 Blue 2 Strawberry 3 Blue 4 Lime 3 Blue 4 Broccoli 3 Blue 5 Banana 4 Green 1 Orange 4 Green 2 Apple 4 Green 2 Strawberry 4 Green 5 Banana

NOTE The most common join types are the Equal and Left Outer.

How multiple tables and multiple joins between tables affect

the record set returned

Join Types Between Two Tables

The following demonstrates how different join types affect the record set returned. The two tables below represent records, as they are stored in the database.

Sample Database Tables

Table 1 Table 2

First Name Last Name First Name Last Name

Jack White Jack White

Elizabeth Black Mary Black

Jane White Jim Green

John Brown John Brown

Mary Black Laura Green

(10)

Two equal joins between two tables

The more joins there are between two tables, the more restrictive the record set returned. For example if you had two tables that each contained a first name and last name field, then, with two equal joins, you would get back records where the first name AND the last name matched in both tables.

Record Set Returned From Two Equal Joins FirstName LastName FirstName LastName

Jack White Jack White

Mary Black Mary Black

John Brown John Brown

Two Left Outer joins between two tables

When there are two left outer joins between tables, the record set returned is also more restrictive. For example, if you had two tables that each contained a first name and last name field and they were linked with two left outer joins, then ALL the records from the first table would be returned but only records where the first AND last name matched in the second table would return.

Record Set Returned From Two Left Outer Joins FirstName LastName FirstNameLastName

Jack White Jack White

Elizabeth Black

Jane White

John Brown John Brown

Mary Black Mary Black

Laura Green

NOTE If there are two links between two tables, when the join type for one link is changed, the

join type for the other link will automatically change to the same type of link. It is not possible to have two different kinds of joins between two tables.

Multiple Tables

As more tables and more links are added, it becomes more and more difficult to understand how the linking will affect the record set.

The following example examines the record sets returned when there is linking between three tables.

(11)

Sample Database Tables

Table A Table B Table C

Color ID Color Colors ID Fruit_Veg Fruit_Veg_ID Food Fruit_veg ID

1 Orange 1 Orange 1 Marmalade 1

2 Red 2 Apple 2 Apple crumble 2

3 Blue 2 Strawberry 3 Strawberry shortcake 3

4 Green 4 Lime 4 Banana cream pie 6

4 Broccoli 5 Peach cobbler 10

5 Banana 6

Equal Joins between tables A, B, and C

When there are equal joins connecting multiple tables, records return when there are matching records that meet all the equal joins specified.

For example, with the above three-table scenario, records are returned that have matching values in all three tables.

Notice that “green” and “lime” from the first two tables are not returned because there is no match in the third table.

Record Set Returned From Equal Joins Connecting Multiple Tables Colors ID Color Colors ID Fruit_Veg Fruit_Veg_ID Fruit_veg ID Food

2 Red 2 Apple 2 2 Apple crumble

1 Orange 1 Orange 1 1 Marmalade

2 Red 2 Strawberry 3 3 Strawberry shortcake

Equal Join between tables A and B and Left Outer join

between tables B and C

The following is the record set returned when there is an equal join between tables A and B and a left outer join between tables B and C

Notice that now the green lime and broccoli are returned even though there is no matching food in table C.

Record Set Returned From Equal Join Between A and B and a Left Outer Join Between B and C

Colors ID Color Colors ID Fruit_Veg Fruit_Veg_ID Fruit_veg ID Food

1 Orange 1 Orange 1 1 Marmalade

2 Red 2 Apple 2 2 Apple crumble

2 Red 2 Strawberry 3 3 Strawberry shortcake

4 Green 4 Lime 4

(12)

Left Outer Join between all three tables

The following is the record set returned when there is a left outer join between tables A, B and C (A to B, B to C).

Notice that 3 blue is returned even though there is no match in either table B or C.

Notice that “banana” and “banana cream pie” is NOT returned. This is because table A is the Driving table and the Left Outer join between Table A and Table B excludes this data.

Record Set Returned From Left Outer Joins Between Tables A, B and C Colors ID Color Colors ID Fruit_Veg Fruit_Veg_ID Fruit_veg ID Food

1 Orange 1 Orange 1 1 Marmalade

2 Red 2 Apple 2 2 Apple crumble

2 Red 2 Strawberry 3 3 Strawberry shortcake

3 Blue

4 Green 4 Lime 4

4 Green 4 Broccoli 5

Left Outer Join between tables A and B and Equal Join

between tables B and C

This linking scenario resulted in the following error:

This means that the Microsoft driver is unable to interpret the SQL for this linking scenario. In order to create a report that uses this linking, one would have to create a main report based on table A and then a linked subreport that is based on tables B and C. The linked subreport will give you the equivalent of a left outer join link.

For more information on Linked and Unlinked Subreports, visit the following link:

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_subrepor ts.pdf.asp

Native (Direct) Linking for PC-type Databases

This section describes how Crystal Reports defines file links for PC-type databases using a native (direct) connection. The join type created when connecting natively to a PC-type database is a Left Outer join.

(13)

Examples of PC-type databases are:

• Microsoft (MS) Access, Act!, Btrieve, Fox Pro, Goldmine, Paradox, Visual DBase, Visual FoxPro

NOTE Connecting Natively to an SQL- type database using SQL-type native (direct) database

drivers defaults to an Equal join.

Examples of these client/server databases are:

DB2, Informix, Lotus Notes, Oracle, Pervasive, SQL Server

Linking Between Two Tables

The A to B Relationship

The two sample database tables are used in the following linking examples. Sample Database Tables

Table A Table B

EmpID EmpName EmpID Project Amount

1 Kim Smith 1 Project 1 1,000.00

2 Linda Johnson 2 Project 3 5,000.00

2 Project 4 10,000.00

2 Project 3 10,000.00

Crystal Reports (CR) creates virtual records by taking each record containing a specific value in the link field in table A and connecting it to a corresponding record containing an identical value in the link field in table B.

In this case the link field is EmpID. When linking from Table A to Table B, using the data in this example, the following record set will be returned to the report:

Record Set Returned From Linking Two Identical Fields From Table A and Table B

EmpID EmpName EmpID Project Amount 1 Kim Smith 1 Project 1 1,000.00 2 Linda Johnson 2 Project 3 5,000.00 2 Linda Johnson 2 Project 4 10,000.00 2 Linda Johnson 2 Project 3 10,000.00

Crystal Reports (CR) concatenates the first record from the parent table (A) to each record containing a link field of identical value in the child table (B). When no further matches are found in the child table, CR moves to the next record in the parent table. This process is repeated for all the remaining records in the parent table.

Each record from the parent table is treated as a unique record regardless of whether it really is. In the example above, EmpID “1”, from the parent table has only one corresponding match in the child table (B). However, EmpID“2” has 3 matches. Consequently there are 3 virtual records created for Linda Johnson,

(14)

EmpID “2”, when only one source record really exists in the actual database (in Table A). This is the result of concatenation of records from the parent table to the child table.

Linking Between Three Tables

The A to B to C Relationship

In an A to B to C relationship, the rules for creating virtual records are the same as those used in the A to B relationship. A third table, “C”, is simply added as a final link to the A to B combination, to give:

A -> B ->C

The three sample database tables are used in the following linking examples. Sample Database Tables

Table A Table B Table C

EmpID EmpName EmpID Project Amount EmpID Class

1 Kim Smith 1 Project 1 1,000.00 1 Class 3

2 Linda Johnson 2 Project 2 5,000.00 2 Class 4

2 Project 3 10,000.00 2 Class 5

2 Project 4 10,000.00 2 Class 6

2 Class 7

Crystal Reports creates virtual records that follow along every valid “link” on EmpID that matches in all 3 tables. The following would be the record set returned to the report in this type of relationship:

Record Set Returned From Linking Three Identical Fields From Table A, Table B and Table C

Table A Table B Table C

EmpID EmpName EmpID Project Amount EmpID Class 1 Kim Smith 1 Project 1 1,000.00 1 Class 3 2 Linda Johnson 2 Project 2 5,000.00 2 Class 4 2 Linda Johnson 2 Project 2 5,000.00 2 Class 5 2 Linda Johnson 2 Project 2 5,000.00 2 Class 6 2 Linda Johnson 2 Project 2 5,000.00 2 Class 7 2 Linda Johnson 2 Project 3 10,000.00 2 Class 4 2 Linda Johnson 2 Project 3 10,000.00 2 Class 5 2 Linda Johnson 2 Project 3 10,000.00 2 Class 6 2 Linda Johnson 2 Project 3 10,000.00 2 Class 7 2 Linda Johnson 2 Project 4 10,000.00 2 Class 4 2 Linda Johnson 2 Project 4 10,000.00 2 Class 5 2 Linda Johnson 2 Project 4 10,000.00 2 Class 6 2 Linda Johnson 2 Project 4 10,000.00 2 Class 7

(15)

To determine the number of virtual records returned for a particular source record, multiply the number of occurrences of that record in each of the tables in the link together (A x B x C).

Since there is only one occurrence of EmpID 1 in Tables A, B, and C, the total number of virtual records created is 1 x 1 x 1 = 1. Since there are one

occurrence of EmpID 2 in Table A, three occurrences in Table B, and 4 occurrences in Table C, the total number of virtual records created for EmpID 2 is 1 x 3 x 4 = 12.

NOTE Depending on how you link tables/databases, and the number of valid “ links” between

them, the number of “ detail records” returned to the report can be much greater than the total number of actual records contained in the tables/databases. As in the example above, there are a total of nine (9) records in all three tables, but on the report, Crystal Reports would return thirteen (13) detail records.

The A to B, A to C Relationship

This section demonstrates record set returned from the linking of three tables using an A to B, A to C relationship.

There are three options available when setting up the linking relationship in Crystal Reports:

• Look up both at the same time • Look up all of one then all the others • Look up all the combinations of the two

CAUTION This is a relationship available only to PC-type databases, not SQL- type databases using

a native connection.

Look up both at the same time

Selecting the option ‘Look up both at the same time’, Crystal Reports starts with the source record in Table A and looks for a match in Table B. If a match is found, Crystal Reports will append the matching record from Table B to the source record from Table A.

However, if no match is found, it will append a blank record to the source record. Crystal Reports then searches Table C for a match to the source record. If a match is found, then this record is appended to the virtual record (which now contains the source record from Table A and either a match from Table B, or a blank).

Otherwise, a blank record will be appended to the virtual record. In the event that a blank is found from both look-up tables, the resultant virtual record will not be included and the read process for the next source record from Table A, initiated.

The number of virtual records created for a given source record is equal to the number of records in the table that has the maximum number of records matching the source record. In this example, Table C contains the maximum

(16)

number of matching records for EmpID 2, which is 4, so the number of virtual records created is 4.

Record Set Returned From Linking A to B, A to C Using the ‘Look up both at the same time’ Option

EmpID EmpName EmpID Project Amount EmpID Class 1 Kim Smith 1 Project 1 1,000.00 1 Class 3 2 Linda Johnson 2 Project 2 5,000.00 2 Class 4 2 Linda Johnson 2 Project 3 10,000.00 2 Class 5 2 Linda Johnson 2 Project 4 10,000.00 2 Class 6

2 Linda Johnson 2 2 Class 7

Look up all of one then all the others

Selecting the option ‘Look up all of one then all the others’, Crystal Reports takes the source record from Table A, and looks up matches in Table B. It concatenates a match from Table B to the source record to create a virtual record. It then appends blank to the created virtual record. The process is repeated for each match found in Table B.

After reading through Table B completely, Crystal Reports looks for matches to the source record in Table C. It appends a copy of the source record to a blank field, and then appends a match found in Table C to create the virtual record. This is done for each match found in Table C.

The total number of virtual records created per source record is determined by summing together the number of matched records for the source record in each table.

In this example the number of virtual records for EmpID “2” is 4+3=7.

Record Set Returned From Linking A to B, A to C Using the ‘Look up all of one then all the others’ Option

Table A Table B Table C

EmpID EmpName EmpID Project Amount EmpID Class

1 Kim Smith 1 Project 1 1,000.00 1

1 Kim Smith 1 1 Class 3

2 Linda Johnson 2 Project 2 5,000.00 2 2 Linda Johnson 2 Project 3 10,000.00 2 2 Linda Johnson 2 Project 4 10,000.00 2

2 Linda Johnson 2 2 Class 4

2 Linda Johnson 2 2 Class 5

2 Linda Johnson 2 2 Class 6

2 Linda Johnson 2 2 Class 7

Look up all combinations of the two

Selecting the ‘Look up all combinations of the two’ option, Crystal Reports appends the first record from Table B that matches the source record from Table A. This is then appended to each matching record from Table C.

(17)

Once all matching records from Table C have been exhausted, Crystal Reports returns to Table B to select the next record that matches the source record, concatenates the two records, and appends a copy to each matching record from Table C.

With this lookup option, the number of virtual records created equals the number of matching records in Table A x matches in Table B x matches in Table C. In this example, Linda Johnson, there are 1 x 3 x 4 = 12 virtual records.

Record Set Returned From Linking A to B, A to C Using the ‘Look up all combinations of the two’ Option

Table A Table B Table C

EmpID EmpName EmpID Project Amount EmpID Class

1 Kim Smith 1 Project 1 1,000.00 1 Class 3

2 Linda Johnson 2 Project 2 5,000.00 2 Class 4 2 Linda Johnson 2 Project 2 5,000.00 2 Class 5 2 Linda Johnson 2 Project 2 5,000.00 2 Class 6 2 Linda Johnson 2 Project 2 5,000.00 2 Class 7 2 Linda Johnson 2 Project 3 10,000.00 2 Class 4 2 Linda Johnson 2 Project 3 10,000.00 2 Class 5 2 Linda Johnson 2 Project 3 10,000.00 2 Class 6 2 Linda Johnson 2 Project 3 10,000.00 2 Class 7 2 Linda Johnson 2 Project 4 10,000.00 2 Class 4 2 Linda Johnson 2 Project 4 10,000.00 2 Class 5 2 Linda Johnson 2 Project 4 10,000.00 2 Class 6 2 Linda Johnson 2 Project 4 10,000.00 2 Class 7 Notice that the above result set is identical to an A to B to C link with no

Lookup Options, from the table on Page 3.

Why does the option ‘No Lookup’ yield the same results as the option ‘Look up all combinations of the two’?

Answer: When there is no common field between Table B and Table C, it is impossible to have an A to B to C link. Instead, you could set up A to B, A to C and use ‘Look up all combinations of the two’ to achieve the same results.

Frequently Asked Linking Questions

Can you link tables from different databases?

It is not recommended to link tables from different databases in the same report. Tables within the same database can be linked but errors may occur when tables from different databases are linked. If the fields must be linked, the second database can be brought in as a linked subreport.

For more information on this topic, visit the following links: • Connecting to Multiple Data Sources In a Report

(18)

• Linked and unlinked subreports -

http://support.crystaldecisions.com/communityCS/TechnicalPapers/scr_sub reports.pdf.asp

Is it better to connect natively or through ODBC?

There are advantages and disadvantages to both types of connections. In general the linking allowed with an ODBC connection is more flexible (allowing more join types). The ODBC data sources must be correctly configured and set up first.

A native connection requires less setup and may be faster because it connects directly to the database.

Which connection is preferred is also database specific. Some databases will only allow one type of connection.

How do I know which fields I should link?

It is important that you understand the structure of your database. If you are not sure which fields to link, check with your Database Administrator. If tables are linked incorrectly, it is probable that erroneous or no data will return.

With an ODBC connection, in order to link fields, they must be of the same data type and length.

With Native Connections, only indexed fields can be linked. These are identifiable by the colored markers beside them.

What if I cannot link the fields?

If the fields are not linkable in the Visual Linking Expert dialog box, it is possible to use a linked subreport instead. A linked subreport will give the equivalent join type of a Left Outer Join.

What are those colored markers?

Colored markers are seen in the Visual Linking Expert when there is a Native connection to the database. The markers represent indexed fields and primary keys that can be linked. There is no special relevance to the color of the markers other than if you have a composite index; the markers will be the same color.

All link warnings were ignored and the report still works. Is

this OK?

You should not ignore the link warnings. The report may run but may be unstable and/or return erroneous or no data.

What is smart linking?

Smart linking is the default linking that Crystal performs if the ‘Smart Linking:” option is enabled in the ‘Options’ | ‘Database’ tab under the ‘File’ command. Smart linking links fields of the same name and same data type and length. Smart linking is not necessarily the correct linking required for your desired result set.

(19)

Contacting Crystal Decisions for Technical Support

We recommend that you refer to the product documentation and that you visit our Technical Support web site for more resources.

Self-serve Support: http://support.crystaldecisions.com/ Email Support: http://support.crystaldecisions.com/support/answers.asp Telephone Support: http://www.crystaldecisions.com/contact/support.asp

References

Related documents

Melalui kesempatan yang berharga ini penulis juga tak lupa mengucapkan terima kasih kepada seluruh dosen Program Studi Ilmu dan Teknologi Pangan yang telah memberikan

Apart from foosball, the interchangeable table top allows you to play slide hockey, billiards, table tennis, shuffleboard, chess, cards, backgammon, bowling and checkers?. What is

To understand the fiscal implications of increased energy storage capacity in urban water systems, CU budgets are explored, electrical rate schedules are

When Jane’s build passes, the master node then pushes the code to the child nodes which are awaiting testing.. Similar results will be shown if a private build is successful but

Concept Media Young Children - Physical Assessment and Administration of Medication 2003. Concept Media High-Tech Monitoring of Traumatic Brain

Meat Animal Research Center (MARC), 2) determine carbon, energy, water, and reactive nitrogen footprints of the beef produced with current production practices, and 3)

Donor groups with crystal reports remove duplicates display the group header for example, and suppress formula below and other fields in the name.. Found out and to crystal

Our goal was, specifically, to understand, whether Central Asia is in any respect “different” from the rest of the former Soviet Union: first, whether the degree of