Shulou(Shulou.com)06/03 Report--

When making business reports, we often encounter some problems that are difficult to solve by relying on report tools alone. Aiming at the practical problems raised by several users of Jasper reporting tools in foreign forums, this paper introduces how to use the aggregator SPL language to solve the problem. The solution here is not only applicable to Jasper, but also to other reporting tools.

1. Calculate the balance of each period of the account

The data file data.csv stores the deposit and withdrawal of funds for each period of an account. If it is known that the initial balance of the account is 43, the balance of each period needs to be calculated according to this file. Some of the source data are as follows:

Inputs,Outputs

0,10

15,0

22,0

0,33

0,15

0,14

36,0

0,69

2,0

18,0

The desired report results are as follows:

Cross-line calculation is needed when calculating the balance of each period according to the deposit and deposit amount, which can be realized by Jasper expression, but the implementation steps are complex and difficult, and it can be easily realized by aggregator with the help of Jasper. The specific aggregator SPL code is as follows:

A1=file ("data.csv"). Import@tc () 2return (tweak 43 Magi A1.derive (t=t+Inputs-Outputs:Balance))

A1 reads into the file, and the option @ tc indicates that the file has a title line and is delimited by a comma.

A2 calculates the balance of each period and returns the results to the report. Among them, set the initial value of the account t to 43, and then add a calculation column to the A1 order table, whose value is the balance of the previous period + inflow of the current period-outflow of the current period. Obviously the result of the calculation here is what you want the report to present.

Save the above SPL code as a file balance.dfx.

In order to present the calculation results in the report, you can use the aggregator to provide an external JDBC interface. In the report tool, the aggregator script is introduced by establishing the JDBC data source, and the method defined by the report calling the aggregator is the same as calling the stored procedure, and you can use call balance () to call and pass parameters in the SQL designer of Jasper. For detailed steps, see "JasperReport calling SPL script".

Then, design the simplest list table in Jasper, with the following template:

After the preview, you can see the results of the report, as we requested:

two。 Calculate loan installments

The database table loan stores loan information, including the total amount of loans, monthly instalments, and annual interest rates. A grouping table is required, and the details of each period are listed under each loan information, including: current repayment amount, current interest, current principal and remaining principal.

Some of the data in table loan are as follows:

LoanIDLoanAmtTermRateL0110000054.75L022000025L03500000124.5

The desired report results are as follows:

Calculating loan installments according to the loan amount requires circular calculation and cross-line calculation, which is difficult to implement with stored procedures or Scriptlets, but it can be easily achieved by using the same aggregator to assist Jasper. The SPL code for the aggregator is as follows:

A1=db.query ("select * from loan") 2=A1.derive (Rate/100/12:mRate,LoanAmt*mRate*power ((1+mRate), Term) / (power ((1+mRate), Term)-1): mPayment) 3=A2. ((tasking LoanAmt A2.LoanID:LoanID. New (A2.LoanID:LoanID, A2.LoanAmt:LoanAmt, A2.mPayment:payment, A2.TermRateRate, t*A2.mRate:interest, payment-interest:principal, t=t-principal:principlebalance)) 4=A3.conj () 5return A4

A1 executes SQL to take out the records in loan.

A2 adds the monthly interest rate mRate and the repayment amount mPayment to the sequence table A1. The results are as follows:

A3 calculates the details of each period according to the loan information. A2. () is a circular function that calculates the members of A2 in turn. The code in "()" will be calculated step by step, and finally return the result of the last comma calculation. The function new is used to generate a new two-dimensional table. The calculation result of A3 is hierarchical data, as follows:

A4 merges hierarchical data to form a two-dimensional table of staging details.

A5 returns the results to the report.

Similarly, a simple grouping table can be designed in Jasper with the following template:

After the preview, you can see the desired report results.

3. Calculate the growth rate of crosstab

The database table store stores the sales volume of a variety of products in 2014 and 2015. It is necessary to use a cross table to show the annual sales volume of each product and calculate the annual growth rate of each product. Some of the source data are as follows:

Year item quantity

2014 Book 35

2015 Pencil 50

2014 Pencil 56

2015 Book 67

You want to get the following report results:

Because each column of the crosstab is generated dynamically, and dynamic reference is needed in the calculation between columns, it is difficult to realize this requirement with Jasper script, but it is relatively simple to use the aggregator to realize the corresponding calculation in the data preparation phase. The aggregator SPL code is as follows:

A1=mydb.query ("select * from store order by item,year") 2=A1.group (item) .run (A1.record (["Growth Rate", item,~ (2) .minutes / ~ (1) .quantity-1]) 3return A1

A1 takes out the record of the store table.

A2 add the annual growth rate of each product. Among them, group is grouped by product, run calculates each group of data in turn, record appends records, and ~ (I) represents the ith record in the current group.

A3 returns the calculation results to the report.

Design the simplest crosstab in Jasper with the following template:

After previewing, you can see the report results:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.