Create a Join Relationship

You can create a join relationship to join two queries.

In general, join relationships should be created in the IBM® Cognos® Framework Manager model. Create a join relationship in IBM Cognos Analytics - Reporting if what you are trying to do cannot be modeled in Framework Manager.

Procedure

  1. Click the Queries icon source tab, and click Queries.
  2. Click the Toolbox icon The toolbox icon and do the following:
    • Drag Query to the work area.
    • Drag Join to the right of the query.

      Two drop zones appear to the right of the operator.

    • Drag a Query object to each drop zone.

      Two queries are created in the work area, and a shortcut to each query appears in the drop zones.

  3. Double-click each query that makes up the join query and add data items to the query.
  4. Return to the Queries work area.
  5. Click Join.
  6. Click the Show properties icon Show properties icon, and in the Properties pane, double-click the Join relationships property.
  7. Click New Link.
  8. To create the link, click a data item in the left query, and then click a data item in the right query.
  9. For each query, click Cardinality and click a cardinality option.
  10. From the Operator drop-down list, click an operator. Click OK.
  11. To optimize the join relationship, specify the Filter type property.
    (Default)
    The join relationship is not optimized.
    In

    The generated filter is an IN predicate that is composed of constant values of the join keys from the "one-side" join operand.

    Between

    The generated filter is a BETWEEN predicate that is composed of the minimum and maximum of the join key values from the "one-side" operand.

    Table

    The generated filter is a table value constructor form of the IN predicate.

    For more information, see Optimizing joins by applying filters.

  12. To convert the join relationship to an expression, click Convert to expression.

    Convert the join relationship to an expression to make changes to the join definition.

    Note: After you convert the relationship to an expression, you cannot change it back to a relationship.
  13. Double-click the join query.
  14. Click the Data icon Data icon, and from the Source tab source tab, drag data items to the Data Items pane.

Results

The join query is complete. You can now link the join query to a data container in the layout.