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
2 Answers:
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:
A3=[560,575,585,595,605,615,625,635,645,655,665]
B3=[39,66,91,116,137,155]
A4= account_detail.select(empirica_score>=A3(1) && mfin_score>=B3(1)).group(
A3.pselect(empirica_score<~[1]):row,
B3.pselect(mfin_score<~[1]):col;
(accounts=~, paysoft_result.count(accounts.(account_no).pselect(~==custno)) /
NAEDO.count(accounts.(account_no).pselect(~==customer_code))):rate)
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/ .
