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 the pre-join feature which can be used for creating/editing queries and setting up joins in business cubes. 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 between tables all at once.

To predefine joins between 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 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. If you want to further edit the join, double-click its join button . The Join Options dialog appears. See the dialog.

   

7. To make the join an outer join, check the Outer Join option, then click either the Left, Right or Full radio button. Regardless of where the tables are placed in the Query Editor, Left is where the arrow starts and Right is where the arrow points.
8. Edit the join condition in the Condition panel according to your requirements.

   From the column drop-down lists, select the columns in the two tables on which the condition will be built, then specify the operator to compose the condition.

   To add another condition line, click the Add Condition button and define the condition as required. Then from the logic drop-down list, specify the relationship between the two condition lines. Repeat this to add more condition lines if necessary.

   To make some conditions grouped, select them and click the Group button, then the selected conditions will be added in one group and work as one line of condition expression. Conditions and groups together can be further grouped.

   To take any condition or group in a group out, select it and click Ungroup.

   To adjust the priority of the conditions, select it and click the Up or Down button.

   To delete a condition line, select it and click the Delete button.

9. Click the OK button to accept the changes and close the Join Options dialog.
10. Repeat steps 5 to 9 to create more joins.
11. If you want to delete any join, double-click its join button and then click the Delete Join button in the Join Options dialog.

Outer Join

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 created joins between the tables in the Pre-join Editor window, you can then use them to define paths. To do this:

1. Click the Paths button on the toolbar of the Pre-join Editor window. The Save Pre-join Path dialog appears. See the dialog.

   

2. 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, and so on), and then click OK. The Select Pre-join dialog appears. See the dialog.

   

3. You will see that all the joins you have created are displayed in the All Joins box. Choose the joins you want and click to add them to the left box, then click OK to dismiss the dialog and return to the Save Pre-join Path dialog. The joins included in the path will be displayed in the Detail box.
4. 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.
5. For the paths you have defined, you can further edit, delete or rename them if needed.

   To edit or delete a path, select it in the Pre-join Paths box, then click the Edit or Delete button.

   To rename a path, select it in the Pre-join Paths box, then in the Rename box, type the new name and then press Enter to confirm.

6. After you have finished specifying paths, click OK in the Save Pre-join Path dialog to confirm the settings.

You must define at least one path for the joins you create. If no path is defined, when you click the OK button on the Pre-join Editor:

• If there are no join loops among the joins, a default path named Path1 which includes all the joins you have created will be defined by JReport Designer. A message box will then be displayed to ask whether you accept the default path (click Yes), or you can modify the default path (click No).
• If there are join loops among the joins, the Save Pre-join Path dialog will appear for you to define at least one path.

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.