Insert a child table field dynamically in Birt

The library table Dcolthread is the primary table, and the primary key is the TID. Dcolquestion is a child table and the foreign key is Tid, as follows:

Dcolthread

dcolquestion

The report needs to query the main table based on applicationname and present the data as a list. The main table each record corresponding to the Status field value has multiple, but not more than 5, need to insert the main table horizontally between the phone, decline field, in turn named QuestionNo1, QuestionNo2 ... QuestionNo5. If a column of data is empty, this column is not displayed. The sample form is as follows:

To prepare the data with the collector, the code is as follows:

A1: Executes SQL to remove the Master Table Association data. Arg1 is from a report parameter. If Arg1= "MFC", then the A1 calculation results are as follows:

A2: Grouped by TID, each group is a master table record with its corresponding sub-table record, such as:

A3: Creates a new empty two-dimensional table based on the structure of the list in the report.

A4: Loops The group in the A2, inserting one record at a time to A3. Circular variables can be referenced in the loop body using A4 and #a4 to refer to the loop count.

B4: Takes the field value of status in the current group and complements at least 5 records.

B5: Append new record to A3. After the end of the cycle A3 as follows:

A6: Returns the result to the report. The collector provides the JDBC interface externally, and the report tool recognizes the collector as a normal database.

then use Birt to design the list table with the following template:

If the Questionno column is empty, it should be hidden. There are many ways to hide dynamically, one of which is described here. For QuestionNo5 (the other columns are similar), you can first use the following script in the Onfetch method of the dataset:

if (reportcontext.getglobalvariable ("T5") ==null) {    reportcontext.setglobalvariable ("T5", row. QUESTIONNO5)}else{    reportcontext.setglobalvariable ("T5", Reportcontext.getglobalvariable ("T5") +row. QUESTIONNO5)

The following expression is used in the visibility property of the QustionNo5 column: Birtstr.trim (reportcontext.getglobalvariable ("T5")) = =""

You can see the results of the report after previewing:

The report calls the collector the same way you call a stored procedure, such as saving this script as DCOLMIDDLE.DFX, and you can use the call dcolmiddle.dfx (?) in the Birt stored procedure designer. To invoke.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

Insert a child table field dynamically in Birt