北京
乾学院 575 号会员
685 浏览  • 
     ...

交叉表列间计算

 
报表(16) 交叉表(3)

【问题】

Hi I am new in this and  i would like to ask how to produce annual Growth rate using crosstab?

to make it simple ,in my database it contains the Year, Item,quantity like this:

YEAR ITEM QUANTITY
2014 Book  35
2014 Pencil 56
2015 Book 67
2015 Pencil 50

now in my report query i filtered it by “YEAR”. By creating crosstab i produced something like this:

$F{Year} Total Quantity Measure
$F{Item} $V{Quantity_measure} $V{Quantity_measure}

which shows something like this

2014 2015 Total
Book 35 67 102
Pencil 56 50 106

I want to calculate the annual growth rate but cant manipulate the datas by Year as it only produces the total of both years, is there any way i can use the formula for annual growth rate in crosstab?

Can anyone give me a sample template or any info on how to do it? Thank you very much. I want to produce a report output like this:

2014 2015 Growth Rate
Book 35 67 91.42%
Pencil 56 50 -10.71%

【回答】

这个问题需要对交叉表进行列间计算，但用 Jasper 脚本实现起来有一定难度，建议用 SPL 先计算出增长率：

A
1 =mydb.query(“select * from store order by item,year”)
2 =A1.group(ITEM).run(A1.record([“Growth Rate”,ITEM, ~(2).QUANTITY/ ~(1).QUANTITY-1]))

A1：查询数据并以 item 和 year 字段排序

A2：以 item 字段进行分组，计算出增长率加入序表中

Jasper 可以通过 JDBC 连接集算器，调用脚本方法和调用存储过程一样，详情参考【JasperReport 调用 SPL 脚本】。

报表(16) 交叉表(3)