esProc Assists BIRT to Dynamically Insert Subtable Fields into Primary Table

Database table dColThread is the primary table with the primary key tID. dColQuestion is the subtable with the foreign key tID, as shown in the following figures:

dColThread

esProc_report_birt_subtable_1

dColQuestion

esProc_report_birt_subtable_2

The desired report will display data in the grid in which the primary table is presented according to ApplicationName. Each record in the primary table may correspond multiple, yet not greater than five, status values. These values need to be inserted between the primary table’s Phone field and Decline field in five columns that are named QuestionNo1, QuestionNo2…QuestionNo5 respectively. If one of these columns is empty, then hide it. The appearance and layout of the report is as follows:

esProc_report_birt_subtable_3

Prepare the necessary data in esProc using the following code:

2015-06-15_140017

A1:Execute the SQL statement to retrieve data from the two associated tables – the primary table and the subtable. arg1 is a report parameter. Suppose arg1=“mfc”, then A1’s result is as follows:

esProc_report_birt_subtable_4

A2:Group A1’s table by tID. Each group includes a record of primary table and its corresponding records from the subtable, as shown in the figure below:

esProc_report_birt_subtable_5

A3:Create an empty two-dimensional table according to the data structure of the report table.

A4:Loop through A2’s groups and insert values into a record of A3 with each loop. In the loop body, A4 is used to reference the loop variable and #A4 is used to reference the loop number.

B4:Get status values of the current group and append to at least five columns.

B5:Append new records to A3. When the loop is over, A3’s table is as follows:

esProc_report_birt_subtable_6

A6:Return A3’s result to the report. esProc provides JDBC interface and it will be identified by reporting tools as a database.

Then design the grid report in BIRT. The template is as follows:

esProc_report_birt_subtable_7

We need to hide a QuestionNo column if it is empty. There are many approaches to dynamically hide it. Here is one of them. To hide column QuestionNo5, we can use the following script (also applicable to other columns) in dataSet’s onFetch method:

if(reportContext.getGlobalVariable(“t5”)==null){

reportContext.setGlobalVariable(“t5”,row.QuestionNo5)

}else{

reportContext.setGlobalVariable(“t5”,reportContext.getGlobalVariable(“t5”)+row.QuestionNo5)

Then use the expression BirtStr.trim(reportContext.getGlobalVariable(“t5″))==”” on column QuestionNo5’s Visibility property.

A preview of the final report is as follows:

esProc_report_birt_subtable_8

The way a report calls the esProc script is the same as that it calls the stored procedure. Save the above script as dColMiddle.dfx, which can be called by call dColMiddle.dfx(?) in BIRT’s 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