Creating joins between tables
Joins between tables in a business cube can be created in the following methods:
Creating joins manually
To create joins manually, tables must be retrieved from a database and a relationship, called a join condition, must be specified between at least one column from each table. To do this:
- In the Business Cube - Join Editor window, position the mouse pointer over the field that will be the source of the join.
- Click and hold the left mouse button while moving to the destination field.
- The join relationship is then established between the tables. When more than one relationship is required between two tables, you can create multiple joins between them.
Creating joins automatically
Joins can also be made automatically by selecting the corresponding item from the Auto Join command of the Table menu in the Business Cube - Join Editor window. You can choose to automatically join tables either By Name or By Primary Key.
- When automatically joining tables by name, a join between two tables, within which two fields share the same name and same data type, will be added.
- When automatically joining tables by primary key, a join between two tables, within which two fields have the same name, same data type and one of the fields is and must be the table's primary key, will be added.
Creating joins by inheriting data source pre-joins
If the Pre-join feature is enabled for a data source connection, when you create joins for business cubes in this data source, the joins can be inherited from pre-joins of the data source.
To make business cube joins inherit from data source pre-joins:
- Make sure the Prejoin property of the data source connection is set to true (see details).
- In the Business Cube - Join Editor window, click Table > Add Table.
- In the Add Table dialog, add the required tables to the business cube and click Done.
Tables that have been defined some pre-join relationships in the data source will be automatically joined together.
When you use pre-joins for creating business cube joins, you may meet the following cases:
- If JReport Designer needs other tables to bridge the tables you have added, you will be prompted to decide whether to add another table to the business cube.
- If there is more than one path available in the pre-join, you will be asked to choose which path you want to use.
Notes:
- Any joins defined between two tables are represented as arrows connecting the key fields from the two tables.
- If the Pre-join option is enabled for a data source connection, the Auto Join feature will not take effect when defining joins for business cubes in the data source.
- If you use pre-joins for creating business cube joins, even though the tables involved in the business cube joins are not contained in the business cube as cube elements, the business cube joins inherited from pre-joins will be applied to any resources in the business cube, which reference table fields used in the pre-joins.
- If there are any loops between the joins, they will be listed in the All Loops panel. When you select a loop in the panel, the joins forming the loop will be highlighted both in the All Joins panel and the right panel of the Join Editor window. You can double-click any loop to get detailed information about the loops in the Properties dialog, and edit the selected join in a loop by clicking
at the end of the join line.
- After building a join in a business cube, the corresponding DBField names will be recorded in this join. If you rename any DBField later, the internal record will not change, and you have to re-establish a join to apply the new name.