Combining data from multiple data sources
The capability to join data sets is a useful and easy way to combine data from two data sources. For example, you can combine data from two XML files, or combine data from a text file with data from a database table. Before joining data sets, you must create the individual data sets. For example, to combine data from an XML file with data from a text file, you must first create the XML data set and the text file data set.
Joining data sets is similar to joining tables in a database, but with the limitation that you can join only two data sets. In a database, you can join more than two tables.
Joining two data sets creates a BIRT object called a joint data set. Just as you can with a regular data set, you can add computed columns and filters to a joint data set, and preview the results it returns. Once you understand the concepts of joining data sets, you can be creative about combining data from more than two sources, assuming that the data from the various sources relate in some way.
Although each joint data set can join only two data sets, you can use a joint data set as one or both of those data sets. For example, you can create joint data set A and joint data set B, then join both of them. Doing so, in effect, combines data from four data sets. Figure 2‑9 illustrates this concept.
As Figure 2‑9 also shows, each data set can return data from different types of sources. You could also use joint data sets to join tables in a single database. For performance reasons, however, this usage is not recommended. Where possible, you should always join multiple tables through the SQL SELECT statement. You should create joint data sets only to:
Combine data from disparate data sources.
Combine data from non-relational data sources, such as XML or text files.
The types of joins used to join data sets yield the same results as the similarly named database joins. Like the database joins, you must specify a column on which to join the two data sets. The following list describes each join type:
Inner join returns rows from both data sets where the key values match.
Left outer join returns all rows from the first data set, even if there are no matches in the second data set.
Right outer join returns all rows from the second data set, even if there are no matches in the first data set.
Full outer join returns all rows from both data sets, even if there are no matches in either data set.
How to join data sets
1 In Data Explorer, right-click Data Sets, and choose New Joint Data Set.
2 Select the first data set for the joint data set from the drop-down list at the left of New Joint Data Set. The columns of the first data set appear in the panel below your selection.
3 Select the second data set for the joint data set from the drop-down list at the right of New Joint Data Set. The columns of the second data set appear.
4 Select the columns to join. Select one column from the first data set, and one column from the second data set.
Typically, you select the columns that are common to both data sets. BIRT Report Designer does not prevent you from selecting two unrelated columns. Doing so, however, typically does not provide the correct results.
Figure 2‑10 shows an example of a joint data set definition.
Figure 2‑10 Joint data set definition
5 Select a join type, then choose Finish.
6 In Edit Data Set, choose Preview Results to see the rows returned by the joint data set.
| Combining data from multiple data sources in BIRT Designer Professional |
Sometimes, the data that a report requires originates in several data sources. For example, an application system generates monthly transaction data and the data for each month is saved in a separate CSV file, or a system saves data in different formats, such as XML and CSV.
In BIRT Report Designer, you can combine data from multiple data sources by creating a joint data set. BIRT Designer Professional provides two additional options for combining data from multiple sources. You can create a union data set or a joined data set. The option you choose depends on the data structures and the results you want. Both options entail combining data sets, so before creating a union data set or a joined data set, you first create the individual data sets. For example, to combine data from an XML file with data from a CSV file, you must first create the XML data set and the flat file data set.
