Overview

Table relationships define how data is one table relates to data in another table. This is extremely important within Cosmos reports because having these relationships set makes it much easier for business users to create reports because they are required to know much less about the underlying data model. This article covers how to set relationships between tables in the Data Model.

Details

To define relationships between tables, navigate to the Data Model section of Data Management. 

You will then click the table that you want to define the relationship for. Underneath the table you will see a section labeled Relationships. 

To add a new relationship, click the + to the right of the Relationships label. You will then see a New Relationships window. Type in the name of the new relationship and click Create. If you need to cancel without creating a relationship, click the "X" in the upper right corner of the window.

This relationship name is what will be shown to users that are creating reports if there are multiple relationships available and they are asked to pick which one to use. 

In the example above, we are adding a relationship to the GL Transactions table that will relate it to the GL Account table, so we have named the relationship "GL Account".

Once the relationship has been created, you can click on the relationship to display the mappings for it.

To define the secondary table used in the relationship, click the Related Table dropdown. You can then select the table to be used for the other side of the relationship. For our example above we will add the GL Account table.

Next, click the Add Relationship Mapping button to insert a row. On this row you can specify the field mappings between the two tables. In the case of our example, the "GL Account No" field that exists on both tables will be used to set the relationship between the tables. 

If additional fields are required to properly link the tables together you can click Add Relationship Mapping to add additional rows for fields.