交叉表列间计算

【问题】

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 脚本】。