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
-
From the report navigation menu, click
.
-
Click 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.
-
Double-click each query that makes up the join query and add data items to the query.
-
Return to the Queries work area.
- Click Join.
-
Click the Show properties icon
, and in the
Properties pane, double-click the Join relationships
property.
- Click New Link.
- To create the link, click a data item in the left query, and then click a data item in
the right query.
- For each query, click Cardinality and click a cardinality
option.
- From the Operator drop-down list, click an operator. Click
OK.
- 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.
- 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.
- Double-click the join query.
-
Click the Data icon
, and from the 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.