How to get Annual Growth rate in Crosstab?

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%

 

perez08_erich's picture
Joined: Feb 3 2015 - 1:42am
Last seen: 7 years 6 months ago

1 Answer:

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.

calculate.machine's picture
Joined: Jan 13 2015 - 11:40pm
Last seen: 6 years 8 months ago
Feedback
randomness