esProc Assists Report Development – Create a Tabular Report by Transposition

Unconventional statistical tasks are difficult to be handled solely by the reporting tool like Jasper and BIRT, or the SQL. For example when the original data are not arranged as required by the tabular report, they need to be transposed for display. However, with the assistance of esProc that has a powerful computing engine for processing structured data and is integration-friendly, we can deal with this case more easily. The following example will teach you how data are transposed.

The database table KPIDetail stores detail data of performance assessment. The KPI Subtotals for different time ranges can be computed using simple SQL statements (group by or union), as shown below:

As the following figure shows, the tabular report will be created based on those source data:

It can be seen that the source data cannot be used directly for tabular report display and thus need to be transposed.

Taking the computation of KPISubtoal as an example, the following esProc code provides a universal method of transposing source data arranged in any format:

A1=myDB1.query(“select * from KPISubtoal”)

Query the database for the source data. The result is as follows:

A2=A1.fname().to(2,)

This line of code produces a set of A1’s field names except the first one. fname function creates a set of field names, for instance A1.fname()=[“range”,”registrations”,”deposits”,”games”]. to function returns a consecutive subsequence between two given integers of the original sequence, for instance .to(2,4) will return a subsequence consisting of the 2nd, 3^rd and 4th members; if the second parameter is omitted, the subsequence will consist of members starting from the first parameter to the end of the original sequence. A2’s result is as follows:

A3=create(KPI).record(A2)

This line of code creates a two-dimensional table for storing the transposed data. It only has one field – KPI – for the time being whose values come from A2, as shown below:

A4: for A1

This loop statement traverses A1’s records, transposes the row data to column data and stores the transposed data in A3. The loop body consists of cells from B4 to B6, the indentation part; in it A4 is used to reference the loop variable.

B4=columnName=A4.#1

This line of code gets the value of the first field of the current record and assigns it to the variable columnName. We can also use A4.range to get the same result, but the sequence number of the field – #1 – is used here for the sake of universality. During the first loop, the value of columnName is “today”.

B5= A4.array().to(2,)

This line of code gets field values of the current record beginning from its second field. A4.array() gets the field values of the loop variable (i.e. the current record) to form a set. For the first loop, B5’s result is as follows:

B6=A3=eval(“A3.derive(B5(#):”+columnName+”)”)

This line of code adds a new column to A3’s two-dimensional table. The column name is the value of columnName and its values are B5’s result. “#” represents the sequence numbers of A3’s members. eval function parses a string into an expression. For the first loop, B6’s expression is A3=A3.derive(B5(#):today). The result is as follows:

After A4’s loop is executed, A3 will get all the transposed data as shown below: 

result A3

This line of code returns A3’s two-dimensional table to the reporting tool. esProc provides JDBC interface for integrating with the reporting tool that will identify esProc as a database. See related documents for the integration solution.

Then create a simple tabular report with, for instance, JasperReport. The template is as follows:

A preview of the final report is as follows: 

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 esProcKPI.dfx, to be called by esProcKPI in JasperReport’s SQL designer. If query parameters have been entered to the esProc script, then use esProcKPI $P{parameter} to call the script. 

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:

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.