esProc Assists BIRT to Dynamically Insert Subtable Fields into Primary Table

Uncategorized 973 0

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:

A

B

1

=myDB1.query(“select * from dColThread t,dColQuestion q where t.tID=q.tID and t.ApplicationName=?”,arg1 )

2

=A1.group(tID)

3

=create(ApplicationName,User,Phone,QuestionNo1,QuestionNo2,QuestionNo3,QuestionNo4,QuestionNo5,Decline)

4

for A2 =A4.(status)|[“”,””,””,””,””]

5

=A3.record(A4.ApplicationName|A4.User|A4.Phone|B4.to(5)|A4.Decline)

6

result A3

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.

FAVOR (0)
Leave a Reply
Cancel
Icon

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

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