You can use the Join Editor to include information on reports from other tables that are not included by default. For example, to include nominal information on a sales ledger report. It is not possible to join every table as a join must be between two matching variables that are the same type. For example, you cannot join a numeric variable to an alphanumeric variable.

There must be a valid SQL relationship between the two variables. Certain joins will not be possible at all due to the fact that the relevant data is not stored in a way that Report Designer can use it, in one or both of the tables, meaning that it isn't possible to create a valid join. In these scenarios, you may need to consider creating a SQL view to extract your desired data.

This article contains information on how to use the Join Editor.

NOTE: This article has been prepared and issued to you as a goodwill gesture only and Sage accepts no liability or responsibility for its use. For further support please refer to your Business Partner.

Explanation of the Join Editor

Using the Join Editor you can add new tables to reports, layouts, letters and labels. This gives you greater control over the reports you can produce. Using the Join Editor you can add the following types of tables:

• Static - A static table is a table that is not linked to any of the other tables on the document. ( For example SYSCompanies is a static table)
• Non-static - A non-static table is a table that is joined to another table on the document. This means that a relationship exists between the tables, which are dependent on the Join Type you specify. When you add a variable to your document, the data reacts according to the Join Type.

Once a join has been created between two tables the first table selected becomes the parent and the second table becomes the child. The parent table is signified by a key.

To create a join

Before creating a join, it is a good tip to examine the tables in SQL and see if there is a straightforward relationship between them, which will give you a strong indication of whether the join will succeed.
From the Report Designer:

1. Data > Joins.
2. Drag and drop the required table from the Toolbox pane into the main Join Editor pane.
3. Depending on what table you have selected, you may get a pop up box for automatic join linking asking 'Do you want to add these links?' We would recommend choosing ‘No’ to this and selecting the joins manually.
4. Within the tables that you want to join locate the appropriate variables > drag the variable from the original table to the new table.Select the join between the tables > set the appropriate Join Type then set the Conditional Join Type to be the same.
   • If Include Records with no Transactions is set to False, the Join Type applies.
   • If Include Records with no Transactions is set to True, the Conditional Join Type applies.

   NOTE: If you need to change a Join Type, for example, from Inner to Parent Outer, we recommend that you change both the Join Type and the Conditional Join Type. This ensures that the join still works if the Include Records with no Transactions setting is changed.The Include records with no transactions is a setting in the properties of the whole report which can be used to instruct Report Designer to display information about records and transactions even when not all of the information included on the design of the report or layout is available to be printed.

5. Click OK.

Join types

The following join types are available in Report Designer: Inner, Parent Outer, Child Outer and Full Outer. The below joins are supported by the Sage 200 SQL database.