RaqForum 28 No.
531 View  • 
     ...

Inter-column Calculations in a Crosstab

 
report(22)

【Question】

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

36

50

106

I want to calculate the annual growth rate but can’t manipulate the data 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

36

50

-10.71%

 

【Answer】

It’s quite difficult to perform inter-column calculations in a crosstab in JasperReport. So I think you can calculate the growth rates in SPL (Structured Process Language). Below is the SPL script:

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: Retrieve data ordered by item and year;

A2: Group data by item and calculate growth rates and write it into the table sequence.

You can connect to esProc from JasperReport via JDBC. And the method of calling an SPL script is the same as that of calling a stored procedure, see How to Call an SPL Script in JasperReport to learn more. 


report(22)
 
 •  531 View  •