Processing of multi-data sources for master reports

When the main report and the subreport (or table tables) use different databases. Report tools such as jasperreport/birt can be functionally processed, but the data source name cannot be used directly in subreports, and an explicit database account and password are required. It can be seen that there are some security risks in this way, and the implementation process is more complicated.

With a structured and strong computing engine, supporting a variety of data sources, the integration is simple, can assist the reporting tools to easily implement such requirements, the following is an example to illustrate the master report multi-data source implementation process.

Table emp in MySQL database, store employee information, primary key is Eid. Table sales in MSSQL, store the employee's order information, field Sellerid is a logical foreign key, corresponding to the EMP table's Eid field. Now you need to make a master report that shows the order information for each employee by salary range, the main report data from the table EMP, and the subreport data from the table sales. Some of the source data are as follows:

Table emp

Table Sales

The Collector code:

EMPESPROC.DFX (The script file is used for the main report)

A1: Query the MySQL database's table emp by salary range.

MyDB1 is the data source name, pointing to MySQL. The function query executes the SQL query, can receive parameters, and low and high are the parameters from the report, representing the compensation range. When low=1000,high=3000, the A1 calculation results are as follows:

A2: Returns A1 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.

SALESESPROC.DFX (This script file is used for subreports)

A1: Find the appropriate order from the sales table of MSSQL by employee ID.

MyDB1 is the data source name, pointing to MSSQL. An Eid is a report parameter that represents an employee ID that is used to establish an association relationship for a master report. If eid=1, the A1 evaluates as follows:

A2: Returns A1 to the report tool.

Next take Jasperreport as an example to design a simple master report, the main table template is as follows:

You need to define two report parameters plow, Phigh, respectively, corresponding to the empesproc.dfx in the two parameters.
The report calls the collector the same way you call a stored procedure. The first step is to define a JDBC data source, such as esprocconn, such as:

You can then call empesproc.dfx in the SQL designer of Jasperreport, with the expression: Empesproc $P {plow}, $P {Phigh}.  

The following design sub-report, the template is as follows:

for reports, EMPESPROC.DFX and salesesproc.dfx come from the same data source Esprocconn, so the data source for the subreport is selected "Use same connection used to fill the master Report ", such as:

Similarly, SQL writing that invokes the collector in a subreport: Salesesproc $P {Peid}

The relationship of the master report is set according to Jasper's specification, and this case uses the field $f{eid} in the main table to map the parameters of the subreport Peid. The final sample is as follows:

Table tables are equivalent to simple subreports, and Jasper have the same processing structure for both, so table tables also encounter problems with multiple data sources. This type of problem can also be solved with a collector, for example, to change the subreport of this case to table.

Report Template:

Report preview:

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

Handling of multiple data sources for master reports