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_report_dynamic_multiplesource_1

esProc code for performing the operation:

esProc_report_dynamic_multiplesource_2

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:

esProc_report_dynamic_multiplesource_3

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:

esProc_report_dynamic_multiplesource_4

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:

esProc_report_dynamic_multiplesource_5

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:

2015-06-16_145746

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

esProc_report_dynamic_multiplesource_7

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_report_dynamic_multiplesource_8

The reporting tool calls the esProc script in the same way as that in which it calls the stored procedure. Save the esProc script as, say dynamicDatasource.dfx, to be called by call dynamicDatasource (?) in BIRT Stored Procedure Report Designer.

Advertisements

About datathinker

a technical consultant on Database performance optimization, Database storage expansion, Off-database computation. personal blog at: datakeywrod, website: raqsoft
This entry was posted in Application, Reporting tool and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s