Easy Insight Logo
Call 1-(720)-316-8174

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), composite (Order Number/Customer Number to Order Number/Customer Number), or multi source composite.

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:

Custom Field Start

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

Custom Field Start

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:

Custom Field Start

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

Custom Field Start

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.

Multi source composite joins help you to join data where one side of the join goes across multiple data sources by itself. For example, if you're joining project and assignee from your project management system to a budget spreadsheet of project and assignee, you would choose multi source composite as the join type, choose project and assignee from the project management system as the source, and choose project and assignee from the spreadsheet as the target. With joins like this, you'll typically want to set a many to one cardinality as well, since you probably have numerous tasks feeding in on the source side against a single entry on the budget side.

Twitter Logo