Joins Tab

This documentation is an older version of JasperReports Server User Guide. View the latest documentation.

Joins are associations between tables enabling their rows may be presented together in the same report. Multiple joins associate columns across many tables to create powerful data visualizations when used in reports. The number of tables and joins in the Domain depends on your business needs, as described in Domain Use Cases. The Domain Designer supports the four most common join types, all based on equality of values between column.

You can create advanced joins using XML in the design file. Advanced joins are not supported in the Domain Designer. If you have a Domain that uses advanced joins, and you open it in the Domain Designer and then save it, your advanced joins will be lost or changed.

Both Left Table and Right Table panels display the list of selected and derived tables. Expand tables in both panels, select a column in the left table and one in the right table with the same logical meaning and compatible formats, then select a join:

Join Inner – The result contains only rows where the values in the chosen columns are equal. In the support case example in Example of Creating a Domain, the result of an inner join contains only support cases that have been assigned to a support engineer.

Join Left Outer – The result contains all the rows of the Left Table, paired with a row of the Right Table when the values in the chosen columns are equal or contain blanks. If the support cases are in the Left Table of the example, the result of a left outer join contains all support cases even if they do not have an assigned engineer.

Join Right Outer – The result contains all the rows of the Right Table, paired with a row of the Left Table when the values in the chosen columns are equal or contain blanks. If the users are in the Right Table of the example, the result of a right outer join contains all the users and the support cases assigned to each engineer, if there are any. In this example, a user might also appear several times if different support cases refer to the same support engineer user ID.

Full Outer Join – The result contains all rows from both tables, paired when the joined columns are equal, and filled with blanks otherwise.

The new join appears in All Joins | Joins on Selected Table panel.

In order to create a join between two tables, they must each have a column that's compatible with a column in the other table. For example, a table with data for support cases has a column for the assigned engineer user ID that can be joined with the table of user data that has a user ID column.

In some cases, you may need to duplicate a table in order to join it several times without creating a circular join, or in order to join it to itself. You can also duplicate a table so it may be joined with different tables for different uses. Click the following icons above the Right Table to make a copy, change the name, or delete a table:

•

Copy – Copies the selected table and gives it a name with sequential numbering. The copy appears in both Left Table and Right Table.

•

Change ID – Changes the name of the selected table. The new name becomes the ID of the table throughout the Domain, and is updated everywhere it appears in the Domain Designer.

•

Delete – Removes the table from both lists. If the deleted table was the only instance of a table, removing it on the Joins tab also removes it from the list of selected tables on the Tables tab.

You use the All Joins | Joins on Selected Table panel to see the defined joins, to remove a join, and to change the join type:

•

All Joins – Lists all joins defined for the Domain.

•

Joins on Selected Table – Lists only joins defined on the table you select, simplifying the view you have of many joins.

•

Join Type – Changes the type of join.

•

Remove – Removes the selected join definition from the list of joins and from the Domain design.

When you've created joins, one or more join trees appear on the Calculated, Pre-Filters and Display tabs. For example, if you join tables A and B, B and C, then join tables D and E, the result is two join trees. Columns of table A and table C may appear in the same report because their tables belong to the same join tree. Tables A and D are said to be unjoined; their columns may not be compared or appear in the same report. Tables that are not joined appear individually along with the join trees.