esProc Assists Report Development – Dynamic Datasources

Reporting tools, such as JasperReport and BIRT, don’t have enough support for multi-datasources, leading to complicated Java program for realizing dynamic datasources. But by using esProc to assist the reporting tool, this won’t be a problem anymore. The following example will teach you how esProc works to realize the dynamic datasources.

myDB1 and myDB2 are different databases, but both have a table sales holding different data. We want to control the source of data of the report through the parameter PsCode and the range of data retrieval through the other two parameters – Pbegin and Pend.

esProc code for doing this:

esProc_report_dynamic_1

As you can see, esProc just uses a line of code to achieve using datasources dynamically.

sCode, begin and end are all esProc parameters. They can be set in the following window:

esProc_report_dynamic_2

begin and end are used to define the range of retrieving data from SQL and sCode is used to switch between datasources . The macro ${sCode} is to parse the parameters to get an esProc expression. To assign myDB2 to sCode, for instance, the above esProc script will be parsed into result myDB2.query(“select * from sales where OrderDate>=? and OrderDate <=?”,begin,end).

query function is used to execute an SQL statement, and receives parameters passed to it.

result statement will return the result to the reporting tool.

esProc provides JDBC interface to be integrated with the reporting tool, which will identify esProc as a database. For detailed integration solution, please refer to related documents.

Next let’s take JasperReport as an example to design a report. Its appearance and layout is as follows:

esProc_report_dynamic_3

Define three parameters – PsCode, Pbegin and Pend – in the report to correspond to the three esProc parameters. Click on Preview to see the completed report:

esProc_report_dynamic_4

In the above report, PsCode’s value is myDB1. We can change the value to myDB2 to see the effect of having dynamic datasources:

esProc_report_dynamic_5

Notice that the way a report calls the esProc script is the same as that it calls the stored procedure. If we save the script as dsource.dfx, then it can be called by dsource $P{PsCode},$P{Pbegin},$P{Pend} in JasperReport’s SQL designer.

That the datasource name is directly used as the parameter, as in the first esProc script, may cause security problem. Sometimes users prefer to use numbers to represent the datasources. For instance, if PsCode’s value is 1, then retrieve data from myDB1; if the parameter has anotehr value, retrieve data from myDB2. This can be realized with the following code:

esProc_report_dynamic_6

 

This esProc script uses connect and close function to connect to or close the database explicitly, which features a higher flexibility.

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