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.

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 (see Relationship Types below), because each row in the "left" category joins at most one row in the "right" category.

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 co-present 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 as they already exist in the environment. However, if you want to learn how to add or adjust joins on a per-report basis, the following sections 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 Products.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  Report Options >  Advanced >  Joins window, select the Products data that does not have Orders data checkbox.

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:

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

Similarly, selecting the Orders data that does not have Products data checkbox changes the join to a right outer join, which includes all rows from the right category. Selecting both checkboxes 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:

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 to Y and X to Z are both one-to-many:

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:

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 to Y and X to 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.

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  Add.

3. Click  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 Okay.

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

To modify a join's fields:

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

2. Add, remove, or modify conditions.

3. Click Okay.

To remove a join, click the Delete  icon next to the join to delete, then click Okay.

To restore the default joins, click  Recreate, then click Okay.

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 checkbox for the first condition of the two.

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