RaqForum 28 No.
944 View  • 
     ...

Union Two Data Sets for BIRT

 
report(22)


【Question】

I want to Union two Datasets in BIRT:
Dataset1：
id    name    time
——-  —————  ———————–
1    name1    2010-07-22 11:01:02.903
2    name2    2010-07-22 11:01:02.903
3    name3    2010-07-22 11:01:02.903

Dataset2：
id    name    time
——-  —————  ———————–
1    t2_name1    2010-07-22 11:01:02.903
2    t2_name2    2010-07-22 11:01:02.903
3    t2_name3    2010-07-22 11:01:02.903
1    t3_name1    2010-07-22 11:01:02.920
2    t3_name2    2010-07-22 11:01:02.920
3    t3_name3    2010-07-22 11:01:02.920

The expected Dataset3 after union：
id    name    time
——-  —————  ———————–
1    name1    2010-07-22 11:01:02.903
2    name2    2010-07-22 11:01:02.903
3    name3    2010-07-22 11:01:02.903
1    t2_name1    2010-07-22 11:01:02.903
2    t2_name2    2010-07-22 11:01:02.903
3    t2_name3    2010-07-22 11:01:02.903
1    t3_name1    2010-07-22 11:01:02.920
2    t3_name2    2010-07-22 11:01:02.920
3    t3_name3    2010-07-22 11:01:02.920

 

【Answer】

BIRT offers Joint Data Set to join two data sets only, but this isn’t a union. A better way is first unioning the data sets outside the reporting tool. If the data sets come from a single database, just use SQL union; if they come from multiple databases or files, you can perform the union in SPL (Structured Process Language). Here’s the SPL script:

A

1

=file(“d:\\data1.txt”).import@t()  

2

=file(“d:\\data2.txt”).import@t()

3

result A1

A1: Read in the first text file as a table sequence where the first row is headers.

A2: Read in the second text file as a table sequence where the first row is headers.

A3: Return the union of A1 and A2.

You can connect to esProc from BIRT via JDBC and call the SPL script the same way as calling a stored procedure. For more information, see How to Call an SPL Script in BIRT.
report(22)
 
 •  944 View  •