. Join several flat files for one chart in BIRT
Assuming you have to use 4 Flat Files (.csv) for one chart in your new requirements. All 4 Flat Files have a same data column type(have the same dimension). There are Joint Data Sets in BIRT, but it seems kinda awkward to solve this problem with these Joint Data Sets for 4 Flat Files because the solution would most likely look like this:
Data Set A using Flat File a
Data Set B using Flat File b
Data Set C using Flat File c
Data Set D using Flat File d
Joint Data Set 1. from Data Set A + B
Joint Data Set 2. from Data Set C + D
Joint Data Set from Joint Data Set 1. + 2.
Sounds easy, but each chart in all reports needs the data of all 4 flat files. Besides its unhandy solution, if you need to do this for serval reports in total It will be a huge time investment.
Is there a more efficient solution?
Solution: Use esProc as Data Source preparing data for BIRT.
For example, here are Contract.csv, Payment.csv, and Invoice.csv:
“Contract” table, with the following fields:
id,date,customer,price,…
“Payment” table, with the following fields:
seq,date,source,amount,…
“Invoice” table, with the following fields:
code,date,customer,amount,…
To find each day’s contract amount, payment amount, and invoice amount, you can write the query with SQL like this:
$SELECT T1.date as Date,T1.Total as CTotal,T2.Total as PTotal,T3.Total as ITotal FROM
(SELECT date, SUM(price) as Total FROM ./Contract.csv GROUP BY date)T1,
(SELECT date, SUM(amount) as Total FROM ./Payment.csv GROUP BY date)T2,
(SELECT date, SUM(amount) as Total FROM ./Invoice.csv GROUP BY date)T3
WHERE T1.date = T2.date AND T2.date=T3.date
or
you can write esProc SPL script like this:
A | B | C | |
1 | =file("./Contract.csv").import@t() | =file("./Payment.csv").import@t() | =file("./Invoice.csv").import@t() |
2 | =A1.groups(date;sum(price):Total) | =B1.groups(date;sum(amount):Total) | =C1.groups(date;sum(amount):Total) |
3 | =join@1(A2:T1,date;B2:T2,date;C2:T2,date) | ||
4 | =A3.new(T1.date:Date,T1.Total:CTotal,T2.Total:PTotal,T3.total:ITotal) |
Explanation
A3: Dimension alignment joins syntax. It can be applied to any tables to be associated, without the requirement that the related fields be the primary keys or a part of them.
The report can be designed in the same way as you would if you were retrieving the data from a database. For detail SPL integration with BIRT, see How to Call an SPL Script in BIRT.
For many difficult calculations of Text Computing, you can refer to Structured Text Computing.
If you have any questions or comments, please leave them below.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL