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
you did consider the crosstab element, right? It can do all this for you!
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= account_detail.select(empirica_score>=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 https://dataccelerator.wordpress.com/2015/02/05/esproc-assists-report-development-cross-table-in-which-header-row-column-group-are-intervals/ .