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_report_birt_splitfiled_1

esProc code:

esProc_report_birt_splitfiled_2

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.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Application, Reporting tool and tagged , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s