esProc Assists Report Development – Dynamic Datasources

Reporting tools, such as Jasper Report 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 andPend.

esProc code for doing this:

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:  

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 Jasper Report as an example to design a report. Its appearance and layout is as follows: 

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: 

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

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 asdsource.dfx, then it can be called by dsource $P{PsCode},$P{Pbegin},$P{Pend} in Jasper Report’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: 

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