IMOS On-Prem - Joining Tables and Fields

When adding columns to a Report Designer report, fields from logically linked tables can be added to the same report, including custom elements created in the Data Dictionary. Between any two tables there can be two types of relationships:

• One-to-many: Created by joining tables.
• One-to-one: Created by joining fields.

Joining Tables

1. After selecting one of the primary tables, click the + button to join a table. The tables for selection here are tables that have multiple records for each of the rows in the primary table:
   
2. Click  and select one of the following table joining methods:
   • Default: The main table record appears the same number of times as the corresponding secondary table records.
     
     
     
   • Aggregate: The values from the secondary table will be concatenated within each of the secondary table columns. You can use this method to consolidate records.
     

Tips on Joining Tables

• If all the required fields are not extracted from the root table, the additional tables will result in multiple records for each of the rows in the primary table.

• Try to avoid joining one-to-many twice from the same source.
• Example:
  • Start from Voyage > Voyage Itinerary.
  • To know which cargo is loaded/discharged, adding Voyage > Cargo Handling directly results in too many lines. A better approach is Voyage > Voyage Itinerary > Cargo Handling.
• If you have no choice but to join one-to-many more than once, plan each join carefully:
  • Start with a plan for which columns you expect. If those are not available as columns in Report Designer, you might have to export to an Excel output and then use a PivotTable for the result.
  • If there is a logical link between the two tables added, there should be a logical filter to be applied, as in the example below.
  • Identify your primary table and the relationships you will use; make sure you have everything mapped.

Joining Fields

The join icon, , indicates that a link can be made to another table via that field.

For example, you can click  next to Company Code to create a direct link to the Address Book table, in which that field is located.

Example: Report on Payment Detail Lines

Payment detail lines can be allocated to the different invoice lines. In IMOS, it looks like this:

However, if you try to report on this information, you might get this result:

The challenge is to get rid of the redundant lines. This can be achieved by filtering on the Seq and Seq No columns.

• Create a new custom column, with an expression that checks if Seq==SeqNo. 
• Filtering on this column gives you this result: