Tables that aren't directly linked together in the database
can still be joined by going through intermediate tables that are
common to both.
If you attempt to use fields from two tables related to
different areas of the application, it may not be possible to join
them using an intermediate table. In these instances, both tables
will be considered primary tables in the report and you will need
to manually edit the table relationships before you can save the report.
However, if you encounter this scenario, we recommend reconsidering
the report’s design since a single report isn't intended to report
on unrelated information.
- Add a column or filter to the report that requires an intermediate
table join.
The Select Intermediate Table and Join window opens.
- In the Join From column, select the option next to the
table you want to join the new table to.
- In the Intermediate Table column, select the option next
to the table you want to use as the intermediate table to join the
new table with the table selected in the Join From column.
- In the First Join column, click the drop-down list and
select the fields you want to join from the intermediate table and
the table selected in the Join From column.
- In the Second Join column, click the drop-down list and
select the fields you want to join from the intermediate table and
the new table you’re adding.
The join tree you define displays on the window.
- To prevent the Select Intermediate Table and Join windows
from displaying again when editing the current report, select the Do Not Show This Dialog Again check box.
If you then add another field that requires defining an intermediate
table relationship, you need to define the relationship from the Data
Set window.
- Click OK.