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

dColQuestion 

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:

Prepare the necessary data in esProc using the following code： 

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:

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: 

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: 

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: 

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: 

The way a report calls the esProc script is the same as that it calls the stored procedure. Save the above script asdColMiddle.dfx, which can be called by call dColMiddle.dfx(?) in BIRT’s stored procedure designer.