. Get Rowdata from an XML file in BIRT Report
You are trying to reproduce a report, with BIRT, of this kind:
Scenario:
- A list of items (product categories).
- Each item (or category) have a fixed number of tables (the table of the product_id vs sold_sold quantity, and a table with some information about the category)
- Each table inside a category has a variable number of rows.
Data structure:
You read data from an XML, it is something like this:
<list>
<item>
<table1>
<row>
<column1>item 1 table 1 row 1 col 1</column1>
<column2>item 1 table 1 row 1 col 2</column2>
</row>
<row>
<column1>item 1 table 1 row 2 col 1</column1>
<column2>item 1 table 1 row 2 col 2</column2>
</row>
</table1>
<table2>
<row>
<columnX>item 1 table 2 row 1 col 1</columnX>
<columnY>item 1 table 2 row 1 col 2</columnY>
<columnZ>item 1 table 2 row 1 col </columnZ>
</row>
<row>
<columnX>item 1 table 2 row 2 col 1</columnX>
<columnY>item 1 table 2 row 2 col 2</columnY>
<columnZ>item 1 table 2 row 2 col 3</columnZ>
</row>
</table2>
</item>
<item>
.
.
</item>
Desidered result:
+---------------------------+---------------------------+
| column 1 | column 2 |
+---------------------------+---------------------------+
|item 1 table 1 row 1 col 1 |item 1 table 1 row 1 col 2 |
|item 1 table 1 row 2 col 1 |item 1 table 1 row 2 col 2 |
.
|item M table 1 row N col 1 |item M table 1 row N col 2 |
+---------------------------+---------------------------+
+---------------------------+---------------------------+---------------------------+
| column X | column Y | column Z |
+---------------------------+---------------------------+---------------------------+
|item 1 table 2 row 1 col 1 |item 1 table 2 row 1 col 2 |item 1 table 2 row 1 col 3 |
|item 1 table 2 row 2 col 1 |item 1 table 2 row 2 col 2 |item 1 table 2 row 2 col 3 |
.
|item M table 2 row N col 1 |item M table 2 row N col 2 |item M table 2 row N col 3 |
+---------------------------+---------------------------+---------------------------+
Using esProc as Datasource:
Here is the esProc SPL script
A | |
1 | =file("./nestedTable.xml").read() |
2 | =xml(A1,"list/item/table1") |
3 | =A2.new(#:ItemID,row) |
4 | =A3.news(row;ItemID,row.column1:column1,row.column2:column2) |
5 | =xml(A1,"list/item/table2") |
6 | =A5.new(#:ItemID,row) |
7 | =A6.news(row;ItemID,row.columnX:columnX,row.columnY:columnY,row.columnZ:columnZ) |
A2:
A3:
A4:
A6:
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 more examples, procedure XML files refer to the following XML data parsing and calculation.
The sample data can be downloaded, nestedTable.xml.zip. 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