Insert child table fields in Birt dynamically, and insert fields in birt dynamically

Insert child table fields in Birt dynamically, and insert fields in birt dynamically

The database table dColThread is the primary table and the primary key is tID. DColQuestion is a sub-table and the foreign key is tID, as follows:

DColThread

DColQuestion

The report needs to query the master table based on ApplicationName and display the data in the form of a list. There are multiple status fields corresponding to each record in the master table, but there are no more than five. You need to insert the Phone and Decline fields in the master table horizontally and name them QuestionNo1, QuestionNo2... QuestionNo5. If data in a column is empty, this column is not displayed. Table sample:

Prepare data with the set calculator. The Code is as follows:

A1: Execute SQL to retrieve the associated data of the primary table and sub-table. Arg1 is a report parameter. If arg1 = "mfc", the A1 calculation result is as follows:

A2: group by tID. Each group is a master table record and its corresponding sub-Table record, for example:

A3: Create an empty sequence table based on the structure of the list in the report.

A4: cycle the group in A2 and insert a record into A3 each time. Cycle variables can be referenced in A4 in the loop body, and # A4 is used to reference the cyclic count.

B4: obtains the status field value in the current group and supplements at least five records.

B5: append a new record to A3. After the loop ends, A3 is as follows:

A6: return the result to the report. The assembler provides a JDBC interface. The report tool recognizes the assembler as a common database.

 

Then use BIRT to design the list table. The template is as follows:

If the QuestionNo column is empty, it should be hidden. There are many methods for dynamic hiding, one of which is described here. For QuestionNo5 (similar to other columns), you can use the following script in the onFetch method of dataSet:

if(reportContext.getGlobalVariable("t5")==null){    reportContext.setGlobalVariable("t5",row.QuestionNo5)}else{    reportContext.setGlobalVariable("t5",reportContext.getGlobalVariable("t5")+row.QuestionNo5)

Use the following expression in the Visibility attribute of the QustionNo5 column: BirtStr. trim (reportContext. getGlobalVariable ("t5") = ""

After previewing, you can see the report results:

The method of the report call set calculator is the same as that of the call stored procedure. For example, save this script as dColMiddle. dfx, call dColMiddle can be used in the BIRT Stored Procedure designer. dfx (?) .

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.