Joining Data Sources

Joins define how you connect one data source to another. Here are some examples of joins:

• A common Project Name field between two data sources
• An Account ID field in an Accounts data source and a related Account ID in a Notes data source
• An Order Number/Customer Number pair between Order and Order Line data where Order Number isn't unique, but the combination of Order Number and Customer Number is

When you define a join, you'll need to specify whether the join is individual (Project Name or Account ID to Related Account ID) or composite (Order Number/Customer Number to Order Number/Customer Number).

You'll need to choose the data source and field for each end of the join. For example, to do Accounts - Account ID to Notes - Related Account ID, we would do the following:

For a composite join, it works the same way, but with the addition of an Add Join button to add multiple pairs of joins:

Cardinality defines the relationship between the two data sources for accurately calculating metrics. In the example below, one Order has multiple Order Lines. Each order has a tax amount and each order line has an amount.

When we add Order Tax into the report at the same time as Order Line Value, the value for Order Tax is incorrectly multiplied for each line:

By specifying a cardinality of one to many from Order to Order Line, the report properly performs the calculation as shown:

Join Behavior enables you to define an inner vs. outer join. You have three options:

• Default -- always include the source, but only include the target if a match is found to the source
• Force Inner Join -- only include data where both source and target exist
• Force Outer Join -- include data from both source and target

You can also customize joins at a report level by doing Configure -> Customize Joins from the report editor.