Combining data from multiple data sources
 
 
Creating a joined data set
A joined data set combines the results of two or more data sets that are related through a common key. Creating a joined data set is similar to joining tables in a database using a SQL JOIN clause. Use a joined data set to combine data from different data sources in which a relationship exists, as shown in the following example.
Figure 10‑8 illustrates data sets that return data about customers and orders. The data sets are related through the CustomerID field. You can retrieve order information for each customer by joining the data sets.
Figure 10‑8 Data sets with a common field returning customer and order data
Figure 10‑9 shows the results of joining the customers and orders data sets on the CustomerID key, and displaying only the CustomerName and Amount fields in the joined data set.
Figure 10‑9 Data rows returned when the customers and orders data sets are joined
Actuate BIRT Designer supports the functionality of joined data sets available in the open-source version, and provides the following additional features in an updated user interface:
*The capability to join more than two data sets
*The capability to join on more than one key
*Support for new join operators: <>, <, >, <=, >=
*Support for a new type of join, the side-by-side join
Unlike the other types of supported joins (inner, left outer, right outer, and full outer), the side-by-side join links data sets without requiring a key. The resulting joined data set displays the selected fields side by side. Figure 10‑10 shows two data sets that do not share a common field. The first data set returns customer data, and the second data set returns order data.
Figure 10‑10 Data sets without a common field
Figure 10‑11 shows the results of joining the customers and orders data sets using the side-by-side join. When using this type of join, do not misinterpret the results. As Figure 10‑11 shows, the data from the two data sets appear side by side, implying that each customer has a relationship with an order when, in fact, no such relationship exists.
Figure 10‑11 Results of a side-by-side join
For information about the other types of supported joins, see BIRT: A Field Guide.