July 30, 2015

esProc Assists BIRT to Dynamically Insert Subtable Fields into Primary Table

Database table dColThread is the primary table with the primary key tIDdColQuestion is the subtable with the foreign key tID, as shown in the following figures:

dColThread

dColQuestion 

The desired report will display data in the grid in which the primary table is presented according to ApplicationName. Each record in the primary table may correspond multiple, yet not greater than five, status values. These values need to be inserted between the primary table’s Phone field and Decline field in five columns that are named QuestionNo1, QuestionNo2…QuestionNo5 respectively. If one of these columns is empty, then hide it. The appearance and layout of the report is as follows:


Prepare the necessary data in esProc using the following code 


A1Execute the SQL statement to retrieve data from the two associated tables – the primary table and the subtable. arg1 is a report parameter. Suppose arg1=“mfc”, then A1’s result is as follows:

A2Group A1’s table by tID. Each group includes a record of primary table and its corresponding records from the subtable, as shown in the figure below: 

A3Create an empty two-dimensional table according to the data structure of the report table.

A4Loop through A2’s groups and insert values into a record of A3 with each loop. In the loop body, A4 is used to reference the loop variable and #A4 is used to reference the loop number.

B4Get status values of the current group and append to at least five columns.

B5Append new records to A3. When the loop is over, A3’s table is as follows: 

A6Return A3’s result to the report. esProc provides JDBC interface and it will be identified by reporting tools as a database.

Then design the grid report in BIRT. The template is as follows: 

We need to hide a QuestionNo column if it is empty. There are many approaches to dynamically hide it. Here is one of them. To hide column QuestionNo5, we can use the following script (also applicable to other columns) in dataSet’s onFetch method:

if(reportContext.getGlobalVariable("t5")==null){
    reportContext.setGlobalVariable("t5",row.QuestionNo5)
}else{
    reportContext.setGlobalVariable("t5",reportContext.getGlobalVariable("t5")+row.QuestionNo5)

Then use the expression BirtStr.trim(reportContext.getGlobalVariable("t5"))=="" on column QuestionNo5’s Visibility property.

A preview of the final report is as follows: 

The way a report calls the esProc script is the same as that it calls the stored procedure. Save the above script asdColMiddle.dfx, which can be called by call dColMiddle.dfx(?) in BIRT’s stored procedure designer. 

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:

July 29, 2015

esProc Assists Jasper in Calculating Opening Balance

Problem source:

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.

July 28, 2015

esProc Assists BIRT in Handling Irregular Month Grouping

Problem source:
 
Irregular month grouping: If the start date is 2014-01-10, group dates from this date to 2014-02-09 together and dates from 2014-02-10 to 2014-03-9 together. If the start date is 2014-01-31, put dates from this date to 2014-02-27 into a group and take dates from 2014-02-28 to 2014-03-30 as a group.

esPro code:

A1: Query database according to the start date and end date. Both startDate and endDate are external parameters.

A2: Count the number of months between the start date and the end date. For example, there are 6 month between 2014-01-31 and 2014-07-31.

B2: Find the day when each of the irregular months begins according to the initial start date and the intervals. Put the start date before the expression for this computation. The sign “|” represents concatenation and “~” represents the current member in the set, i.e. the numbers from 1 to 6. after function is used to get the irregular months.

A3: Group A1’s data by B2’s intervals and calculate sales amount for each of these irregular months; B2 is appended as the last column. pseg function returns the sequence number of the interval in which the data is held. “~” in ~.sum(Amount)represents the current group. “#” represents the sequence number of the current group. Result is as follows:

A4: Retrieve the second and third column from A3 and return them to the reporting tool. esProc provides JDBC interface outward to be identified by a reporting tool as a normal database.

Create a simple list table with BIRT:

esProc script in the same way as it calls the stored procedure. Save the above script as BirtUnregulMonth.dfx. You can invoke it with call BirtUnregulMonth(?,?) from BIRT’s stored procedure designer. Below is a preview of the appearance and layout of the result report:

July 27, 2015

esProc Assists BIRT in Splitting Fields and Converting Them to Records


To split fields apart and recombine them into records, you can use the stored procedure or a report script. But as the code is complex, an alternative choice is using esProc to help the reporting tool with the job. Let’s look at an example.

Database table data has two fields: ID and ANOMALIES. You need to split ANOMALIES field into strings by the space and combine each string with the corresponding original ID value to generate new records. Below is the original data:

esProc code:

A1: Query the database.

A2: Split ANOMALIES field to create a new two-dimensional table. Then use conj function to concatenate the records generated from the processing of each of A1’s members. “~” represents the set member under processing. A2’s result is like this:

A3: Return A2’s result to the reporting tool. esProc provides JDBC interface outward to be identified by a reporting tool as a normal database.

Create a simple list table with BIRT:

A preview would be like this:
The way a report calls an esProc script is the same as that it calls the stored procedure. Save the above script asBIRTsplitrow.dfx. You can invoke it with call BIRTsplitrow() and input parameters into it from BIRT stored procedure designer.