esProc Assists Report Development – Different Datasources for Master Report and Subreport

Functionally, reporting tools like JasperReport or BIRT can handle the situation where the master report and the subreport(or the table) have their data sources in different databases. But data source names cannot be used directly in the subreport, instead they need to be defined in clear-text database usernames and passwords, which, as we can see, reduces security and requires relatively complicated programming.

esProc has a powerful structured data computing engine, supports heterogeneous data sources and is integration-friendly, so it is capable of assisting the reporting tool to make the programming much more easier. Here is such an example for illustrating how it works to realize connecting to different data sources for the master report and subreport.

There is a table emp in MySQL database holding employee information, with EId field being its primary key. There is another table sales in MSSQL database containing order information, with SellerId being its logical foreign key corresponding to emp’s EId field. We need to develop a report that includes a master report, whose data come from emp, and a subreport, which uses sales as its data source, to display each seller’s orders according to the salary range. Some of the source data are as follows:

Table emp

Table sales

esProc code for doing this：

empEsProc.dfx (This script is for the master report)

A1：Query table emp in MySQL dabase according to the salary range.

myDB1 is the data source name corresponding to MySQL. query function executes the SQL statement and accepts two parameters specifying the salary range – low and high – from the reporting tool. If low equals 100 and high equals 3,000, A1’s result is as follows:

A2：Return A1’s result to the reporting tool. esProc provides JDBC interface to be integrated with the reporting tool and the latter will identify it as an ordinary database. See related documents for the integration solution.

salesEsProc.dfx (This script is for the subreport)

A1：Select orders from MSSQL’s table sales by SellerId.

msSQL1 is the data source name corresponding to MySQL. eid, the report parameter, represents the seller’s ID and is used to establish a relationship between the master report and the subreport. If eid equals 1, then A1’s result is as follows:

A2：Return the result of A1 to the reporting tool.

Next we’ll design a report with a master report and one subreport. Template of the master report is as follows:

A pair of report parameters, pLow and pHigh, which correspond to the pair in empEsProc.dfx, need to be defined.

The reporting tool calls the esProc script in a same way as it calls the stored procedure. The first step is to define a JDBC data source, such as esProcConn, as shown below:

Then empEsProc.dfx can be called in JasperReport’s SQL designer. The corresponding expression is empEsProc $P{pLow},$P{pHigh}.

The template of the subreport is as follows:

For the report, both empEsProc.dfx and salesEsProc.dfx have the same data source – esProcConn, so select “Use same connection used to fill the master report” in configuring the data source for the subreport, as shown in the following figure:

Similarly, the esProc script can be called in the subreport. The corresponding expression is salesEsProc $P{pEId}.

Set the relationship between the master report and the subreport according to the rules of Jasper. In our example the master report’s $F{EId} field is set to be referenced by the parameter pEId in the subreport. The appearance and layout of the final report is as follows:

A table can be regarded as a simple subreport. Both the table and the subreport are handled by Jasper within the same processing structure, thus different tables may also have different data sources. This case can also be dealt with through esProc. If we change the subreport in this example to a table, the result will be like this:

The report template：

Report preview：