How to get Annual Growth rate in Crosstab?

[perez08_erich]

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%

 

[calculate.machine]

The problem involves inter-column calculation which Jasper has some difficulty in dealing with.

Suggest using esProc to first prepare the data source and calculate and the growth rate:

A1=mydb.query("select * from store order by item,year")

A2=A1.group(item).run(A1.record(["Growth Rate",item,~(2).quantity/~(1).quantity-1]))

Code explanation is available from http://blog.raqsoft.com/?p=3517.