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: