Often the data used to build a report comes from different sources. With the JasperReport, the common practice is either to build a report with one or more sub reports or to use JavaBean as the data source. The former makes report development complex and the latter requires a JavaBean class. Moreover, iReport needs a static method to return the result set defining JavaBean to display data in the report.

  In this article we introduce another solution – esProc. esProc handles different sources in a convenient and efficient way, which also makes iReport run faster. Here we use JasperReport5.6.0 to make the illustration.

 Multi-data-source handling

  In some cases the source data for a report project comes from more than one database. One case is that the database fed by an application is overloaded and data has to be split into several parts to store, such as two databases or a database and a file storing present sales data and historical sales data respectively.

  For database sources, sometimes they are of the same type, such as MySQL or Oracle; other times they are not, such as one is the database, the other is a text file, CSV file or an Excel file.

  In the following example, the source data comes from a MySQL database and a text file.

  The employee table in MySQL database contains records where EID values are from 1 to 100000:

  

  data.txt stores data where EID values are from 100001 to 101000:

  

  The task is to build a report with iReport to display data of concatenating employee and emp.txt. Let’s look at how esProc handles it using its SPL language (Structured Process Language).

	A
1	=connect("mysql")
2	=A1.query@x("select * from employee")
3	=file("F:\\files\\emp.txt").import@t()
4	=[A2,A3].conj()
5	return A4

  A1: Connect to MySQL database.

  A2: Get data from employee table in the database and return the query result.

  

  A3: Import the content of emp.txt.

  

  A4: Concatenate data of A2 and A3.

  

  A5: Return the concatenation result to the reporting tool.

 

  Now we save the SPL script as employee.dfx and call it via esProc JDBC to display data in the report.

  As calling a stored procedure in a reporting tool, we configure a JDBC data source to input the SPL script to the iReport. Then we can call the script in Jasper’s SQL designer using call employee(). See How to Call an SPL Script in JasperReport to learn more.

  Now all is ready. We can build a simple report according to the following template in iReport:

  

  Click Preview to button to view the finished report:

  

 

  As you see, the integration of SPL script into Jasper is simple, and the whole process is clear, concise and easy to understand.

 

 Performance optimization

  That’s the basic process flow in SPL. Now let’s look at if we can make it faster in more complicated scenarios. Table joins is not uncommon in preparing report data source. Sometimes a table contains a large volume of data. For example, we need to join an employee table and an orders table by employee IDs to view the sales people’s information in certain orders. But the number of orders increases daily and at last overload the system seriously.

  In the following example, the source data comes from the employee table and the sales table in MySQL database.

  employee table stores employee records where EID values are from 1 to 3000000:

  

  sales table stores 760000 rows of data that is increasing daily. Below is a part of it”

  

  Below is the template of report mysql_join.jrxml:

  

  To find the sales records where EID value is less than 1000001 before the date 2015-04-01, the popular method is the following SQL:

  select sales.OID,sales.Date,sales.EID,sales.Amount,employee.Name from sales join employee on sales.EID=employee.EID where sales.Date<'2015-04-01'and employee.EID<1000001

 

  Click Preview button and we wait 101 seconds to get the result:

  

 

  Now let’s try using esProc JDBC to get what we want. We save the above report as esproc_join.jrxml and then write the following SPL script:

	A
1	=connect("mysql")
2	=A1.cursor("select * from sales where  Date<'2015-04-01'")
3	=A1.query("select EID,Name from employee  where EID<1000001").keys(EID)
4	=A2.join@i(EID,A3:EID,Name)
5	return A4.fetch()

  A1: Connect to MySQL database.

  A2: Find records from sales where Date is earlier than 2015-04-01 and return them as cursor.

  A3: Get EID column and Name column from employee where EID value is less than 1000001.

  

  A4: Join A2’s cursor with A3’s table sequence through the foreign key.

  A5: Return A4’s cursor data to the reporting tool.

 

  Save the SPL script as esproc_join.dfx and define a SQL-style data source:

    call esproc_join()

  Click Preview button and after just 14 seconds we get the same result (In the same test environment).

  That’s how esProc makes report development with iReport efficient. This is only one of the basic esProc uses. More advanced uses include parallel processing, controllable buffer, controlled SQL execution path, reducing hidden cells, inserting data computation layer, etc.