esProc Assists Report Development – Dynamically Relate Multiple Data Sources to a Master Report

Unconventional statistical tasks are not uncommon during report creation, but they are difficult if handled solely by a reporting tool like Jasper or BIRT, or SQL. For example it is troublesome to display result of dynamically relating a master report to its corresponding subreports existing in multiple databases. Yet esProc, with its powerful computing engine for processing structured data, can assist the handling of the case. It is also conveniently to be integrated by the reporting tool. We’ll discuss it through an example.

Master report org resides in data source Master. The subreports to which its records correspond reside in multiple data sources. For instance when org.org_id=“ORG_S”, the record’s corresponding subreport is table User of data source S_odaURL; when org.org_id=“ORG_T”, the record’s corresponding subreport is table User of data source T_odaURL. There are more than two subreports but all their names are User. The final report requires that these subreports be related to the master report dynamically. The following figure shows the logical relation between them: 

esProc code for performing the operation:

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

Execute the SQL statement to retrieve data from table org of data source Master. arg1 is a parameter passed from the report, which is for data filtering. Suppose arg1=“ORG”, then A1’s result is as follows:

A2: for A1

Loop through A1’s records one by one, dynamically relate a subreport each time and then write it to B2. Note that esProc uses the indentation to represent a loop statement’s working range, like B2-B7 in this example. In the loop body, A2 is used to reference the loop variable and #A2 can be used to reference the loop number.

B2=right(A2.org_id,1)+"_odaURL"

Compute data source name of the corresponding subreport according to the current record’s org_id field. For the first loop, B2’s result is “S_odaURL”.

B3=connect(B2)

Connect to a data source by its name. Note that data source Master in A1 has been configured to be automatically connected and thus can be used directly. In B3, the data source needs to be connected manually using connect function.  

B4=B3.query("select * from user where org=?",A2.org_id)

Retrieve data from table User in B3’s data source according to the specified condition.

B5=B4.derive(A2.org_id:org_id,A2.org_manager:manager,A2.org_title:title)

Append three new fields that come from the master report to B4’s subreport and rename them org_id, manager and title respectively. For the first loop, B5’s result is as follows:

B6=B1=B1|B5

Append B5’s result to B1 (operator “|” is equal to union function). After loops are executed, B1 will get all data needed by the report:

B7=B3.close()

Close the data source connection.

A8: result B1

Return B1’s table to the reporting tool. esProc provides JDBC to integrate with the reporting tool, which will identify it as a database. See related documents for the integration solution.

A seasoned programmer may replace for statement with esProc’s long statement to make the code more concise:

Create a simple grouped report with, for instance, BIRT. The template is as follows:

Define parameter pVar in the report to correspond to its counterpart in the esProc script. The following is the preview of the final report:

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：