Home > Catalogs > Managing resources in a catalog > Defining the join relationships between tables

Defining the join relationships between tables

After tables have been added to a catalog, you can now use them to build reports. However, in your daily work, you may often be dealing with a set of tables in your queries. You will have to define relationships and create joins among different tables every time you create a new report. For XML data source, if the parent and children nodes are transformed to different tables, joins will be embodied by the parent-child relationship, which is maintained by primary key and foreign key in tables.

JReport Designer provides you with a pre-join feature. The pre-join information is one kind of resource information stored in the catalog. It is saved in a standalone file with the extension .pre, and shares the same prefix as the catalog file. It is not used at report runtime. In JReport Designer, when you open a catalog, the pre-join file is opened at the same time. When you save the catalog, the pre-join file is also saved.

Creating joins between tables

The Pre-join Editor is a convenient tool for you to predefine the relationships among tables all at once.

To predefine joins among tables in a catalog, follow the steps below:

1. In JReport Designer, open the catalog on which you want to define join relationships.
2. In the Catalog Browser, select a data source to activate the Pre-join button on the toolbar if it is disabled.
3. Click the button, then in the Select Data Source dialog, select the date source on which the joins will be created and click OK. The Pre-join Editor window appears.
4. Click the Arrange button to organize the tables that have been added to the catalog.
5. Now you can make joins among tables by using mouse drag and drop. Point to one column in the source table, press and hold the mouse button, then, move the pointer to the other column in the target table and release the mouse button. A green line with a join button will be shown, linking the two columns. This represents that a join has been created.
6. Repeat the above step to set up more joins.

Modifying a join

Once a join has been established, you can modify it at any time if required. To do this:

1. Double-click the join button to display the Join Options dialog. See the dialog.

   

2. If you want an outer join made, check the Outer Join option, then specify the format of the join as required: SQL92 or Use'+'.

   Note: You cannot make outer joins for XML data sources.

3. Edit the join conditions in the Condition panel according to your requirements.
4. When done, click OK to accept the changes.

See also Join Options dialog for details about options in the dialog.

Outer Join

There are two kinds of join formats: SQL92 and Use'+'. With conventional joins, records that do not satisfy the join condition are eliminated from the result. An outer join preserves these records in the result and replaces the missing values with nulls. SQL syntax uses the left outer join if the records in the left side table are preserved and right outer join if the records on the right side table are preserved. The left side is determined by where the arrow begins and the right side is determined by the side the arrow points to. It is independent of the location of the table in the query editor. When you drag to make the join, you always drag logically from left to right even if your view in the query editor is right to left.

For example, consider the following two tables where the join arrow points from the Customer.C# to the Order.C#:

Table 1

Table 2

The inner join of Customer.C# = Order.C# will produce the following result:

The Customer LEFT JOIN Order ON (Customer.C# = Order.C#) will produce the following result:

The Customer RIGHT JOIN Order ON (Customer.C# = Order.C#) will produce the following result:

The Customer FULL OUTER JOIN Order ON (customer.C# = order.C#) will produce the following result:

Defining join paths

After you have made joins among the tables in the Pre-join Editor window, you can then use them to define paths, which can be used for creating/editing queries and setting up joins in business cubes later.

To define a join path:

1. Click the Paths button on the toolbar of the Pre-join Editor window.
2. In the Save Pre-join Path dialog, click the New button and type a name for the new path in the Input Path Name dialog (by default the name will be Path1, Path2, etc), and then click OK. See the dialog.

3. In the Select Pre-join dialog, you will see that all the joins you have made are displayed in the All Joins box. Choose the joins you want and click to add them to the left box. See the dialog.

   

4. Click OK to dismiss the dialog and return to the Save Pre-join Path dialog.
5. Click the New button and follow the steps above to form a new path. You can select Hide Joins Added in Other Paths to hide the joins that have been used by the existing paths in the All Joins box.
6. After you have finished specifying paths, click OK in the Save Pre-join Path dialog to confirm the settings.
7. Click the OK button in the Pre-join Editor.

Editing, deleting and renaming join paths

After you have specified the paths, you can edit, delete and rename them as required. To do this, click the Paths button on the toolbar of the Pre-join Editor window to bring out the Save Pre-join Path dialog. See the dialog.

• To edit or delete a path:
  1. Highlight the path you want to edit or delete in the Pre-join Paths box.
  2. Click the Edit or Delete button.
• To rename a path:
  1. Highlight the path you want to rename in the Pre-join Paths box.
  2. In the Rename box, type the new name, and then press Enter to confirm

Saving the relationships together with a catalog

You can save the relationships by clicking OK on the Pre-join Editor toolbar. Note that the pre-join information won't really be saved to disk until you save the catalog. After you have saved the catalog, you can then use the just created pre-join to develop reports.