. Union datasets from multiple datasources in BIRT Report

 

If you require to collect the same table data from multiple data sources and display it in a single table, you need to combine multiple results into a single. You can create a scripted data set and apply the business logic necessary to produce the unified view you need. But if the data sets should be merged, grouped, or done other kinds of computing before giving it to the report, you need to do lots of hard codes and handle parameters from the data preparation to the report.

Here is an example of showing a simple method:

A telecom enterprise stores user service information with the table userService. A Current report needs to show the duration of calls, the number of calls, the duration of calls to the local area, and the number of calls to the local area of various telecom products.

In esProc, the script is as follows:


A

B

1

[mysql1,mysql2,mysql3,mysql4]

2

fork A1

=connect(A2)

3


=B2.query@x("select product_no,sum(allDuration) sallDuration,sum(allTimes)   sallTimes, sum(localDuration) slocalDuration, sum(localTimes) slocalTimes from userService where I0419=? group by product_no", argType)

4

=A2.conj()


5

=A4.groups(product_no;sum(sallDuration):ad,sum(sallTimes):at,sum(slocalDuration):ld,sum(slocalTimes):lt)

The script not only can simply union and grouping the data sets from multiple datasources but also can improve the performance for parallel computing. Statement fork uses four threads in parallel. Each thread takes data from the corresponding database and returns the grouped and aggregated results to the main thread. The main thread merges the calculation results of each sub-thread, performs the grouping and aggregation again, and finally obtains the result set required by the report.

The integration of BIRT with esProc is not introduced here. Please refer to How to Call an SPL Script in BIRT.