Jasperserver Ad Hoc Report for a specific year and the year before

[PamSchniesel]

Hello,

I am totally new to JasperReports and I have a problem to create a ad-hoc report for a year.

The problem:
The report should have just one parameter -> year.
It should display tables and diagrams.
The tables should include data from the given year, data from the year before and should calculate the changed in percentage between those years.

Example

	2014	2013	2014/2013
Visits	500	400	+25%
bookings	300	250	+20%
successful bookings	150	100	+50%

 

 

 

 

 

The data are in one postgresql database in one table:

| date | year | visits | bookings | successful bookings |

How can I get the data from the year before and how can I calculate the differences between those years?
Is it necessary to calculate those data in database layer or is jaspersoft server able to calculate them from plain data?
Is there a best practice for it?

BR

 

[calculate.machine]

To create a report according to your requirements is really not easy, because the arrangement of the source data is not the one the crosstab expects for reporting. But by transposing the source data, the creation of a crosstab report will become much easier. The following figure shows the transposed data: 

The transposition can be done by esProc. The code is as follows: 

Then Jasper can connect to esProc through JDBC and call the esProc script in the same way as it calls the stored procedure. For more details, see http://blog.raqsoft.com/?p=2855 .