Setting up and editing joins
A business cube is based on tables (including views). By default, there is no relationship between the tables. In order to make them correlated, you need to define joins between them. To do this:
- In the Catalog Browser resource tree, select the business cube, right-click it and select Edit Business Cube from the shortcut menu.
- In the Business Cube Editor window, click Tools > Join Editor on the menu bar or the Join Editor button
on the toolbar. The Business Cube - Join Editor window will then be displayed. See the window.
- Add more tables to the business cube if needed and set up the joins between the tables, then click OK to save the settings.
See also Business Cube - Join Editor window for detailed explanation about options in the window.
Note: Because a report cube is based on a query, stored procedure, SQL file, or user defined data source, which may already contain some relationship among tables, you cannot set joins for it.
Adding tables
When you open the Business Cube - Join Editor, you will see all the tables (including views) that have been added to the business cube when you create it. If you find that you require some extra tables to create joins, you can add them as follows:
- In the Business Cube - Join Editor window, click Table > Add Table to display the Add Table dialog.
- Select a schema from the Schemas box if the database supports schema. The tables that are contained in the selected schema and are not included in the business cube will then be displayed in the Tables box. Choose the required tables, and then click the Add button.
-
Click the Done button to close the dialog. Then, all the selected tables/views will be shown in the right panel of the Join Editor window.
- Click the Arrange button to organize the tables.
Now, you can define joins among the selected tables as required.
Tips:
- Tables that have been added to a business cube can be deleted if not required. To do this, select the table in the Business Cube - Join Editor window, and then click Table > Delete Table. However, a table with a join to another table cannot be deleted.
- If you want to hide a table which is not a part of a join, click
in the upper right corner of the table. To redisplay hidden tables, in the Business Cube - Join Editor window, click Table > Table Properties to show the Table Properties dialog, then move the hidden tables from the Hide Tables box to the Show Tables box (you can also use this dialog to hide tables without join relations).
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.
Editing a join
After a join has been set up, you can edit it at any time as required. To do this:
- In the Business Cube - Join Editor window, right-click the join in the All Joins box and choose Edit Join (or double-click its join button
in the right panel). The Join Options dialog appears. See the dialog.
- Check the Outer Join option to set the join an outer join, then specify the format of the join as required: SQL92 or Use'+'.
- Edit the join conditions in the Condition panel according to your requirements.
- When done, click OK to accept the changes.
See also Join Options dialog for details about options in the dialog.
Besides, if you find that some joins are unnecessary or their names are not intuitive, you can delete or rename them.
- To delete an existing join, right-click the join in the All Joins box and choose Delete from the shortcut menu (or click the Delete Join button in the Join Options dialog).
- To rename an existing join, right-click the join in the All Joins box and choose Rename from the shortcut menu. The name of the join will then become editable. Enter a name in the name box and press Enter on your keyboard (or click somewhere outside the box).
