Progress
Report Builder
User’s Guide


Defining Database Joins

Joins connect database tables, making fields in each joined table available for your report. Follow these steps to create a database join:

  1. Choose Database Joins. If you have already defined a join for the report, a Joins dialog box similar to the following appears. It lists the joins already defined for the report:
  2. If there are no joins defined for the report, the New Join dialog box appears.

  3. Choose the New button to define a new join. The New Join dialog box appears:
  4. Select the table you want to join (the related table) from the Join To Table drop-down list.
  5. Specify the Table alias. By default, Report Builder uses the table name as the table alias. You must specify a table alias if you have already joined to a table with the same name or if the table name happens to be a Progress reserved word.
  6. Choose the New Field Pair button. The New Join Field Pair dialog box appears:
  7. Select the table from which you want to make the join (the controlling table) from the From Table drop-down list or choose Calculated Fields to join on a calculated field.
  8. Select the join field from the controlling table from the From Field selection list. You can base joins on any table field except a logical field and some calculated fields.
  9. Select the join field in the related table from the To Field selection list. The selection list displays the available join fields.
  10. Choose OK. Report Builder returns you to the New Join dialog box. The join fields you specified appear in the Join Fields list.
  11. You can specify more than one pair of join fields for a single join. To do so, choose New Field Pair again, and specify the join field pair. You can create up to 10 pairs of join fields. Each join field pair can have a different controlling table.

  12. Specify the join type. (The default is an inner join).
  13. Choose OK to create the join and return to the Joins dialog box.
  14. Choose Close to return to the report layout.

You can define more than one join field pair for a single join. For example, to calculate the shipping charges for an order with data from the Newsport database, you must define several joins, including one with two field pairs. Figure 8–4 shows the joins you need to create between the tables. The direction of the arrow indicates the controlling and related tables in the join. The controlling table is the table from which the arrow originates, and the related table is the arrow destination.

Figure 8–4: Example of Two Join Field Pairs in a Single Join

Notice that there are two arrows to the Shipping-Charges table. These arrows indicate that the join consists of two field pairs: 1) Shipping-Charges.From-Region and Order.Warehouse and 2) Shipping-Charges.To-State and Customer.State.

The result of the join with two field pairs produces a record from the Shipping-Charges table that indicates the cost per pound for shipping an order. You can then calculate the weight of each line item with a calculated field that multiplies the quantity of the item being ordered by the shipping weight of the item. Then, create a total aggregate field that sums that result of the calculated field for each order. You then calculate the shipping charge for the order by taking the sum for the order and multiplying it by the value in the Shipping-Charges.Price-Per-Pound field.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095