Really tricky (impossible) query


Please help. I have a report to do and Im hopless.

I have this table setup.

I need a grid or table with empirica_score as the rows   (DISTINCT)
and mfin_score as the columns.   (DISTINCT)    e.g.

Thats the grid done.

But then I need to  divide the total paysoft_result records per customer
with the total NAEDO records per customer where start_date is DISTINCT

times * 100 to get percentage

This calculated value needs to be placed on the grid in the correct emprica_score mfin_score location on the grid.

Man I dont even know how or where to start



mnel_1's picture
Joined: Sep 17 2014 - 12:59am
Last seen: 5 years 10 months ago

2 Answers:


you did consider the crosstab element, right? It can do all this for you!

ernestoo's picture
Joined: Nov 29 2010 - 11:59am
Last seen: 2 years 10 months ago

Both the table header row group and table header column group of your cross table consist of intervals. The measurement comes from another table. It’s rather difficult to design such a cross table only using SQL+Jasper. Yet esProc can be used in this case to get data prepared for helping Jasper design the report. The esProc solution is as follows:



A4=>=A3(1) && mfin_score>=B3(1)).group(



       (accounts=~, paysoft_result.count(accounts.(account_no).pselect(~==custno)) /


Jasper can connect to esProc through JDBC, and calls the esProc script in the same way as that it calls the stored procedure. For details, see .

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