Scroll

Joins

Caution
The Joins window is recommended for advanced users only.

Joins describe how the categories on a report are related to each other. When two categories are joined, a field in the first category is associated with a field in the second category. Wherever a value in the first category's field matches a value in the second category's field, that value's rows from each category come together to form a composite row. The table produced by all the composite rows is the resulting data that appears on the report.

For example, take the following categories, Orders and Products. The Orders.ProductId field corresponds with the Products.Id field. When the categories are joined from Orders.ProductId to Products.Id, the rows are connected wherever those two fields have matching values.

custom.joins_basic_demo.png

Categories joined on Orders.ProductId >> Products.Id

The result of this join is the following composite rows. These categories have a one-to-one relationship, because each row in the "left" category joins at most one row in the "right" category.

custom.joins_basic_row.png

Joined categories. Products.Id is omitted.

Tip
Categories could be joined along more than one set of fields; composite rows are formed only when all sets have matching values.

For two categories to be copresent on a report, there must be a join path between them. They are either directly joined, or there is a path through one or more intermediate categories. You do not have to configure joins manually - they already exist in the environment. However, if you want to learn how to add or adjust joins on a per-report basis, this topic will explain the options that are available.

Join Types

The join that was previously described is the most common type of join, an inner join. When an inner join is applied, rows in either category that have no matching row in the other are excluded from the resulting table. However, you may not want to exclude these rows. To do so, you can change the type of join to an outer join.

For example, the row in the Products category with Id: 12 has no matching row in the Orders category. With an inner join, this row is excluded from the output. If you want to see the Products rows that have no matching Orders row, you can change the join type.

To do so, from the Options.png Report OptionsAdvanced.png AdvancedJoin.png Joins window, select the Products data that does not have Orders data check box.

screen.join_type.png

Left outer join

This changes the join between these categories to a left outer join, because all rows from the left category are included. The following rows result:

custom.joins_left_outer.png

Joined categories with all Products rows. Products.Id is omitted.

Similarly, selecting the Orders data that does not have Products data check box changes the join to a right outer join, which includes all rows from the right category. Selecting both check boxes includes all rows from both categories; this is a full outer join.

Relationship Types

There are two types of join relationships: one-to-one and one-to-many.

In the previous example, the relationship between the categories is one-to-one, because each row in the left category joins at most one row in the right category. Some categories have a one-to-many relationship, where each row in the left category joins zero or more rows in the right category.

A one-to-many relationship from categories X to Y is represented in the following diagram:

custom.1M_detail.png

Each X is joined to one or more Y

Reports with a single one-to-many join are well suited to grouping by the left category. The data in these reports is generally well-formed and understandable.

However, when a report has multiple categories with one-to-many joins, data can appear more disorganized and confusing. For example, the following diagram represents data from three categories, X, Y, and Z, where the relationships between X - Y and X - Z are both one-to-many:

custom.1Mx2_detail.png

Each X has 1 or more Y, and 1 or more Z

Because Y and Z are not directly related to each other, there are many rows with only Y or only Z. This can cause the report to be significantly larger, and to be difficult to read and interpret. This occurs even with inner joins, the most restrictive type, because by default there is no logic that deals with the relationship between Y and Z.

Read on for different ways of improving the structure of a report with multiple one-to-many joins.

Cartesian Processing

You could fill the blank spaces with supplementary data by disabling Special Cartesian Processing from the Joins window. Blank cells are filled in with data that is repeated directly from the previous row. The following diagram demonstrates how this works:

custom.1Mx2_no_cartesian.png

Disabling Special Cartesian Processing

The shaded cells represent data that has been repeated from the previous row. This can make the report more readable. However, this approach poses a problem: Blank cells indicate a lack of a relationship between two fields, so filling in these spaces with artificial data can obfuscate any relationship between Y and Z. This can decrease the accuracy of the report.

There are better ways to improve the readability of such a report without sacrificing accuracy:

  • Use repeating groups to show the X - Y and X - Z relationships in entirely separate sections. This is suitable if any relationship between Y and Z is irrelevant or nonexistent. See Sections for more information.

  • Hide some or all of the rows which do not have data for both Y and Z. This is suitable if you want to highlight an implicit or indirect relationship between Y and Z. This is done by imposing Must constraints. Read on for more information.

Must Constraints

Although Y and Z are not directly joined, they are both related to X, so there is an implicit relationship between them. If you examine Y and Z alone, you will notice that they technically exhibit a full outer join.

custom.1Mx2_YZ.png

Y and Z, without X

Because all the rows from Y and Z that relate to X are shown, there are rows with both Y and Z, or with only one of either. Must constraints allow you to change the implicit join type, and in doing so, eliminate rows that lack data from one or both categories.

To set Must constraints, from the Joins window, locate the MUST panel for the applicable categories:

screen.join_must.png

Setting Must constraints

Do one of the following:

  • Select the Y check box - Left outer join: Any rows without Z are removed

  • Select the Z check box - Right outer join: Any rows without Y are removed

  • Select the Y and Z check boxes - Full outer join: Any rows without Y or Z are removed

custom.1Mx2_mustY.png  custom.1Mx2_mustZ.png  custom.1Mx2_mustBoth.png

Effect of setting various Must constraints

Modifying Joins

The Joins window shows all direct and implicit joins on the report. Direct joins can be added, modified, or removed from the report.

To add a new join:

  1. Select From and To categories.
  2. Click Add2.png Add.

  3. Click Add2.png Add Condition then select From (left column) and To (right column) fields.

    Tip
    If there are multiple conditions, only the rows that satisfy all the conditions are joined.
  4. Click OK.

To remove conditions, click the Delete DeleteItem.png icon next to the condition to delete.

To modify a join's fields:

  1. Click the Edit Edit.png icon next to the join to edit.

  2. Add, remove, or modify conditions.

  3. Click OK.

To remove a join, click the Delete DeleteItem.png icon next to the join to delete, then click OK.

To restore the default joins, click Refresh.png Recreate, then click OK.

Advanced Joins

You may be able to specify join conditions that are more complex than column equality.

Note: Advanced Joins cannot be applied across different data sources.

Type

Instead of joining between two columns, one or both sides of the join may instead be an arbitrary expression, constant, or SQL sub-query that you specify.

To change the expression type for one side of a join condition, select one of the following from the Type list:

  • Value: One or more constant values separated by commas

  • Expression: Formula or calculation

  • SubQuery: SQL query

Then enter the value in the Value field.

Operator

As opposed to the default equality (=) operator, which joins fields from the left expression to matching fields from the right expression, a join condition can use one of several alternative operators instead. For example, the inequality (!=) operator joins fields on the left to non-matching fields on the right. To do so, select one of the alternative operators from the Operator list:

  • != not equal
  • >  greater than
  • >= greater than or equal
  • <  less than
  • <= less than or equal
  • IN match one or more values, either specified or calculated from a subquery

Grouping

When a join has two or more conditions, you can specify how the conditions should be met as a group in order for the join to take effect.

To specify that either one of two conditions will satisfy part of the clause, select OR from the Conjunction list for the first condition of the two.

To add parentheses around two conditions, select the Group check box for the first condition of the two.

You can preview the full join clause in the Summary field.


Hidden Article Information

Article Author
Exago Development
created 2017-11-15 15:26:31 UTC
updated 2018-05-10 16:34:51 UTC

Labels
cartesian, advanced, field, category, object, link, join, must, constraint, inner, outer, left, right, full, relationship,
Have more questions? Submit a request