esProc Assists Jasper in Calculating Loan Payments

Problem source: http://community.jaspersoft.com/questions/851148/loop-jasper

The calculation of loan payments according to loan amount will involve loop operation and inter-row operation. It is difficult to write code for it using stored procedure or Scriptlets. Yet with the help of esProc, the Jasper calculation will become easier. Here is an example.

Database table loan contains loan data including loan amount, terms by the month and yearly interest rate. You need to create a Jasper grouped report in which, under each sum of loan amount, payment details per term such as payment, interest rate, principal and principal balance are listed. Below is a selection from the loan table:

The following esProc code is used for preparing the data:

A1: Execute SQL to retrieve records from loan.

A2: Add two computed columns – mRate (monthly rate) and mPayment (monthly payment) to A1. Below is the result:

A3: Calculate payment details for each term based on loan information. Loop function A2.() calculates A2’s members sequentially, uses “()” to perform computation step by step(steps are separated by commas), and returns the computed result of the expression in the last step. new function is used to create a two-dimensional table. A3’s result is cascade data, as shown below:

A4: Combine the cascade data to create a two-dimensional table containing payment details for each term of every loan amount.

A5: Return A4’s result to the report. A reporting tool will identify esProc with JDBC interface as a normal database.

Then create a simple grouped table with Jasper in the following template:

Below is a preview of the finished report:

A report calls an esProc script in the same way as it calls the stored procedure. Save the above script as loan.dfx. You can invoke it with call loan() and input parameters into it from Jasper’s SQL designer. 

esProc Assists Jasper in Calculating Opening Balance

Problem source:

http://community.jaspersoft.com/questions/850400/how-create-report-opening-balance-using-jasper-reports

The calculation of opening balances according to deposit and withdraw amount involves inter-row operation. Jasper can do the calculation but the code is complicated. You can use esProc to assist the job and make it easy. The following is a simple example.

data.csv contains deposit and withdraw information of different sum of money in an account. You need to calculate the opening balance based on this file. Below is a selection from the original data:

esProc code:

A1: Import the file separated by commas.

A2: Calculate opening balance and return result to the report. The initial value of the account is 43. Operations using parentheses will return result of the expression after the last comma. A2’s result is as follows:

esProc provides JDBC interface. Reporting tools will identify it as a normal database.

Now you can create a simple list table with Jasper in the following template:

Below is a preview of the finished report:

A report calls an esProc script in the same way as it calls the stored procedure. Save the above script as balance.dfx. You can invoke it with call balance() and input parameters into it from Jasper’s SQL designer.

Calculate Growth Rate in Jasper Crosstabs

Problem source: http://community.jaspersoft.com/questions/847490/how-get-annual-growth-rate-crosstab .

As every column in a crosstab is generated dynamically, you also need to reference them dynamically when performing inter-row calculations. There is some difficulty in handling this dynamic reference using a Jasper script. But the data preparation can be made easier using esProc. Let’s look at an example.

The database table store holds sales amount of multiple products in the year 2014 and 2015. You need to display the sales amount of each product per year using a crosstab and calculate the annual growth rate of every product. Below is a selectin from original data:

esProc code:

A1: Retrieve records from the store table.

A2: Append annual growth rate of every product to A1. group is used to group data by products; run is used to perform the required calculations by loop; and record is used to append records. ~(i) represents the ith record in the current group. Below is A2’s result:

A3: Return A2’s result to the report. Reporting tools will identify esProc equipped with JDBC interface as a normal database.

Then you can create the simplest crosstab with Jasper:

Below is a preview of the finished report:

A report calls an esProc script in the same way as it calls the stored procedure. Save the above script as AnnualRate.dfx. You can invoke it with call AnnualRate () and input parameters into it from Jasper’s SQL designer.

esProc Integration & Application: Integration with JasperReport

esProc provides standard JDBC interface to be integrated easily with the reporting tool. This article will explain respectively the integration of esProc and JasperReport in development environment and the deployment in WEB server-side.

1. Integration in development environment

Access esProc JDBC to call the esProc script using JasperReport iReport Designer through the following steps:

1.1. Create configuration files

Create the two configuration files – config.xml and dfxConfig.xml – necessary for esProc JDBC.

1.1.1. config.xml

<?xml version="1.0" encoding="UTF-8"?>

<Config Version="1">

<!-- Registration code -->

<regCode>HOjw6-9da4x-NXkD6-089IX@w</regCode>

<charSet>ISO-8859-1</charSet>

<!-- Configure dfx file’s searching path, which is an absolute path. There can be multiple paths separated by semicolon(s). The dfx file can also be placed in the application’s classpath which has the priority over a searching path for loading the file -->

<dfxPathList>

<dfxPath>D:/dfx</dfxPath>

</dfxPathList>

<dateFormat>yyyy-MM-dd</dateFormat>

<timeFormat>HH:mm:ss</timeFormat>

<dateTimeFormat>yyyy-MM-dd HH:mm:ss</dateTimeFormat>

<!-- Method one for datasource configuration: configure connection pool in the application server and specify the datasource name -->

<DBList>

<!-- Datasource name must be consistent with that in the dfx file. Here MySQL database is used -->

                    <DB name="mysql">

            <property name="url" value="jdbc:mysql://127.0.0.1:3306/test"/>

            <property name="driver" value="com.mysql.jdbc.Driver"/>

            <property name="type" value="MYSQL"/>

            <property name="user" value="root"/>

            <property name="password" value=""/>

            <property name="batchSize" value="1000"/>

            <property name="autoConnect" value="false"/>

            <property name="useSchema" value="false"/>

            <property name="addTilde" value="false"/>

        </DB></DBList>

<mainPath/>

<tempPath>temp</tempPath>

<bufSize>65536</bufSize>

<localHost/>

<localPort>0</localPort>

<logLevel>DEBUG</logLevel>

<callxParallel>1</callxParallel>

<nodeParallel>1</nodeParallel>

</Config>

1.1.2. dfxConfig.xml

<?xml version="1.0" encoding="GBK"?>

<dfxConfig>

         <maxConcurrents>10</maxConcurrents>

         <maxWaits>10</maxWaits>

         <log></log>

</dfxConfig>

1.1.3.JAR Load driver jars

esProc JDBC driver includes five jars - dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar and dom4j-1.6.1.jar. They can be found in esProc IDE’s [installation directory]\esProc\lib directory. If esProc also uses other databases as the datasources, then the driver jars of these databases need to be added too. For instance, mysql-connector-java-5.1.5-bin.jar has been added here. Place all these jars in the classpath’s root path.

Except for these jars, the directory (such as D:\lib\config) of the two configuration files – config.xml and dfxConfig.xml – created in the first step needs to be placed in the classpath’s root path too. Or compress the configuration files into the root directory of any of the jars above, with their names remaining strictly the same.

Configure the classpath on the Tool Option bar, as shown in the following figure: 

1.2 Create a datasource

Create a new datasource – esproc – on the datasource configuration window. Select the JDBC driver com.esproc.jdbc.InternalDriver and enter the JDBC URL as jdbc:esproc:local://. Leave the username and password blank. 

esProc JDBC is a fully embedded computing engine. All computations are performed in its embedded package, thus the URL is local. On the other hand, since it isn’t a complete database, it has neither the username nor the password.

After filling all necessary information, click on “Test” to see whether the connection to the datasource has been successfully established.

1.3 Deploy esProc script

Copy the esProc script (dfx) to dfxPath configured in config.xml (such as D:/dfx configured in the above). Here we’ll take the query of the closing stock price in a certain month as an example. The esProc script is as follows: 

1.4 Call esProc script in IDE

Create a report and set the query

Create a new report – esProcReport, write the query statement and call the esProc script using call dfxName(), in which dfx name is stocks. 

Select all the fields and execute the code by clicking on Next Step to the end. 

Because the esProc script needs to receive the parameter representing the year and the month, it is necessary to set the parameter for use in the query for the report after it is created. In the following figure, add the parameter representing the year and the month and edit the query for the report. 

Then drag the parameter to the specified position of the query statement, thus the query becomes as follows: 

Edit the report

Drag all fields under Fields to the report’s detail band and complete the edition, as shown below: 

Preview the report

Click on Preview and enter the parameter to see the query result: 

This is the integration of esProc script and JasperReport in the development environment. Next we’ll move on to explain how to deploy them in the web server-side.

2. Deploy web server-side

Select a suitable application server. For this purpose, Tomcat6.0 is used here for illustration.

2.1 create a web application

Create a J2EE application, which is named, say, JasperReport, and copy it to Tomcat’s webapps directory.

2.2 Create configuration files

Two configuration files – config.xml and dfxConfig.xml – are needed. The configuration information is the same as that for integration in development environment.

2.3 Deploy necessary jars

JasperReport’s driver jar

Deploy jars necessary for JasperReport into the application’s WEB-INF\lib directory. The JasperReport jar can be found in ireport\modules\ext directory of iReport Designer’s installation directory.

esProc JDBC’s driver jar

Copy the jars - dm.jar, poi-3.7-20101029.jar, log4j_128.jar, icu4j_3_4_5.jar and dom4j-1.6.1.jar - used in the above integration in the development environment and MYSQL’s driver jar - mysql-connector-java-5.1.5-bin.jar – to the application’s WEB-INF\lib directory. Then copy config.xml and dfxConfig.xml to the application’s WEB-INF\classes directory.

2.4 Copy the report

Copy the completed report (whose file extension is .jasper) to the corresponding directory of the application. For example, copy esProcReport.jasper to the application’s reportFiles directory.

2.5 Define and Publish the JSP report

The following code is to define and publish the JSP report, such as showReport.jsp:

<%@ page language="java" contentType="text/html; charset=UTF-8"

    pageEncoding="UTF-8"%>

<%@ page

    import="java.io.*,

            net.sf.jasperreports.engine.*,

            net.sf.jasperreports.engine.util.*,

            java.util.*,java.sql.*,

            net.sf.jasperreports.engine.export.*"%>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>

<head>

<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">

<title>Show JasperReport</title>

</head>

<body>

    <%

       String report = request.getParameter("report");

       File reportFile = new File(application.getRealPath("reportFiles/"

              + report));

       //Load report object

       JasperReport jasperReport = (JasperReport) JRLoader

              .loadObject(reportFile.getPath());

       //Pass report parameter through Map

       Map parameters = new HashMap();

       //Assign value to parameter p1 of the report

       parameters.put("p1", 200901);

       //Connect to esProc

       Class.forName("com.esproc.jdbc.InternalDriver");

       Connection conn = DriverManager.getConnection(

              "jdbc:esproc:local://", "", "");

       //Export the report to the page

       JasperPrint jasperPrint = JasperFillManager.fillReport(

              jasperReport, parameters, conn);

       JRHtmlExporter exporter = new JRHtmlExporter();

       exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);

       exporter.setParameter(JRExporterParameter.OUTPUT_WRITER, out);

       exporter.setParameter(

              JRHtmlExporterParameter.IS_USING_IMAGES_TO_ALIGN,

              Boolean.FALSE);

       exporter.exportReport();

       out.flush();

       conn.close();

    %>

</body>

</html>

2.6 Preview the report

Launch Tomcat and access the website http://127.0.0.1:8080/JasperReport/reportJsp/showReport.jsp?report=esProcReport.jasper to preview the report as shown below, which means the deployment is completed.