esProc Assists BIRT in Splitting Fields and Converting Them to Records

Uncategorized 941 0

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_report_birt_splitfiled_1

esProc code:

        

A

1

=myDB1.query(“select ID,ANOMALIES from data”)

2

=A1.conj(ANOMALIES.array(” “).new(A1.ID:ID,~:ANOMALIES))

2

result A2

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:

esProc_report_birt_splitfiled_3

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:

esProc_report_birt_splitfiled_4

A preview would be like this:

esProc_report_birt_splitfiled_5

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. 

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website