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% |
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.
