Using Join Conditions

You can override join types at design time, for example, you can define a report that includes all account codes where there are no transactions in the selected period range.

  1. Display the report properties by clicking the report icon on the Report Design pane.
  2. Select the Join Conditions property. Where there is no associated business object No Associations is displayed as the property value. Where the associations have been set to the system default then Default is displayed as the value. If you amend the associations the value is shown as User Defined. Click the query button to display the Join Conditions, which comprises two main panels, a table of associations on the left and a detail panel on the right where the association's join type can be amended:
  3. Specify this information:
    Associations
    All Associations for the currently selected business object are listed. You can expand/contract the list using the plus and minus signs and resize the panes as required.
    Type
    The type of association is indicated by one of the following:
    • Inner - All rows with matching key fields.
    • Left Outer - All rows from the specified From value.
    • Right Outer - All rows from the specified To value.
    • Full Outer - All rows from the specified From value and all rows from the specified To value.
    Used
    If the association is included in the current report design a tick is displayed in this column.

    For Join Conditions:

    Association
    The parent association name of the selected association.
    From
    The object from which the association refers.
    To
    The object to which the association refers.
    Join Type
    Use the drop-down menu to select an alternative join type if required. The details are updated in the association table accordingly. The available join types are the same as those used in a standard relational query. Use the Reset button to reset the associations back to the default if necessary.
    Description
    A description of the currently selected Join Type is displayed.
  4. Save your changes.

Example

When you select the account association to be Right Outer in a ledger line-to-account association, the expected output is as follows.

The header line is printed for all accounts, whether there are transaction lines in the selected period or not.

Account Period 2 Opening Balance Period 2 Movement Closing Balance O/B Period 3
11000 -13,234.23 -1,000.00 -14,234.23
12000 -10,000.00 0.00 -10,000.00
13000 -12,234.19 -1,600.00 -13,834.19

Although there are no rows for account 12000 in Period 2, the sum of the period movement values is zero (0.00) and not null.

When you select the account association to be an Inner Join in a ledger line-to-account association, the expected output is as follows:

The header line is only printed for all accounts, where there are transaction lines in the selected period.

Account Period 2 Opening Balance Period 2 Movement Closing Balance O/B Period 3
11000 -13,234.23 -1,000.00 -14,234.23
13000 -12,234.19 -1,600.00 -13,834.19