Join tables

Introduction

The information used in your reports is stored in a database, and the variables containing this information are stored in tables. A single table will contain a set of related variables, such as account details, and typically a report will need to use variables from more than one table. When more than one table is used in a report, related tables are connected together using joins, which link the related variable that is contained in each table.

For example, say an invoice report provides details about invoices and the related customer accounts. The invoice table contains variables for information related to the invoice, including an account reference variable. The sales ledger table contains variables for information about customer accounts, and also includes an account reference variable. The two tables can be joined by the account reference variable in each table, meaning that the information for an invoice can be linked to the information for the appropriate customer.

Report Designer's set of reports already join suitable tables. If you are editing these or creating your own reports, you may want to include variables from a new table, and will need to join tables using the Join Editor.

You can

Have greater analysis of information in your report.
Control the information returned to the report; whether to include or exclude based on the relationship between tables. See Join properties.

Join tables in your report

Choose Report > Joins.

The Join Editor appears.
Find the table you want in the Toolbox pane and drag it to the Join Editor's centre pane.

Tip: You can search for a table by entering its name in the Find box at the top of the Toolbox pane.
To join the table, click the variable you want to join from, and drag it to the variable you want to join to. A join appears between the two tables.

Tip: To get a clear picture of the overall structure of tables and their joins, you can sort the tables. In the centre pane right-click a blank area then choose Auto Arrange > Top to Bottom / Left to Right.

Joining multiple variables

You can connect more than one pair of variables in a table join (called a compound key).

Click the join connector line.
In the Properties window, select the From Field and enter a comma (,) followed by the variable name to join from.
Select the To Field, enter a comma (,) followed by the variable name to join to.

Go to top