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



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.

  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?



PamSchniesel's picture
Joined: Aug 12 2014 - 3:35am
Last seen: 7 years 1 day ago

1 Answer:


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 .

calculate.machine's picture
Joined: Jan 13 2015 - 11:40pm
Last seen: 5 years 10 months ago