esProc Assists BIRT in Splitting Fields and Converting Them to Records

Problem source: http://developer.actuate.com/community/forum/index.php?/topic/36204-split-data-row/ .

To split fields apart and recombine them into records, you can use the stored procedure or a report script. But as the code is complex, an alternative choice is using esProc to help the reporting tool with the job. Let’s look at an example.

Database table data has two fields: ID and ANOMALIES. You need to split ANOMALIES field into strings by the space and combine each string with the corresponding original ID value to generate new records. Below is the original data:

esProc code:

A1: Query the database.

A2: Split ANOMALIES field to create a new two-dimensional table. Then use conj function to concatenate the records generated from the processing of each of A1’s members. “~” represents the set member under processing. A2’s result is like this:

A3: Return A2’s result to the reporting tool. esProc provides JDBC interface outward to be identified by a reporting tool as a normal database.

Create a simple list table with BIRT:

A preview would be like this:

The way a report calls an esProc script is the same as that it calls the stored procedure. Save the above script as BIRTsplitrow.dfx. You can invoke it with call BIRTsplitrow() and input parameters into it from BIRT stored procedure designer.