The Making of Dynamic Association report of multi-data source (Birt as an example)

when using reporting tools such as Jasper or Birt, you often encounter unconventional statistics that are difficult to handle with either the report tool itself or SQL, such as a primary table-related sub-table that is distributed across multiple databases, and reports show the results of dynamic Association of these data sources. With a structured and robust computing engine, the integration is simple and can assist reporting tools to easily implement such requirements. The following is an example of a multi-data source dynamic association implementation process.

The primary table org in the data source master, in the Org each record corresponding sub-table in different data sources, such as org.org_id= "org_s", the record corresponding to the child table is the data source S_odaurl user table, org.org_id= "org_t", The child table that corresponds to this record is the user table in the data source T_odaurl. There are more than two sub-tables, the name is user, and the main table needs to be dynamically associated and rendered in the report. The logical relationship is as follows:

The Collector code is as follows:

A1=master.query ("SELECT * from org where org_id like '" +arg1+ "% '")

Executes SQL, fetching data from the Org table of the data source master. Arg1 is a parameter from the report, used to filter the data, if arg1= "ORG" then the A1 calculation results are as follows:

a2:for A1
Loop through the records in A1, each dynamically associating a child table, and merging the associated results in B2. It is worth noting that the natural indentation of the collector represents the scope of the loop statement, that is, the b2-b7, the loop body can be used A2 to refer to the loop variable, can be used #a2 to refer to the loop count.

b2=right (a2.org_id,1) + "_odaurl"
calculates the data source name for the corresponding child table based on the org_id field of the current record. For the first cycle, the B2 evaluates to "S_odaurl".

B3=connect (B2)
Connect to the data source by name. Note that the data source master in A1 is configured for automatic connection and can be used directly, and the data sources in the B3 need to be connected manually using function connect.

b4=b3.query ("select * from user where org=?", a2.org_id)
Query The data source B3, conditionally remove the data from the user table.

b5=b4.derive (a2.org_id:org_id,a2.org_manager:manager,a2.org_title:title)
added three columns in child table B4, data from the main table, renamed to ORG_ID, manager, title. For the first cycle, the B5 results are as follows:

b6=b1=b1| B5
merges the computed results of the B5 into the B1, operator ' | ' Equivalent to the function union. At the end of the loop, B1 stores the complete data required for the report, as follows:

B7=b3.close ()
Close the data source connection.

A8:result B1
returns B1 to the report tool. The collector provides a JDBC interface, and the report tool recognizes the collector as a normal database, and the integration scheme is referenced in the relevant documentation.

for skilled programmers, you can also use the long statement of the collector to avoid looping statements and make the code more concise:

Next, take Birt as an example to design a simple grouping table with the following template:

You need to define the report parameter Pvar, which corresponds to the parameters in the collector. 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 DYNAMICDATASOURCE.DFX, and you can use the call Dynamicdatasource (?) in the Birt stored procedure designer. To invoke.

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

The Making of Dynamic Association report of multi-data source (Birt as an example)