Really tricky (impossible) query

[mnel_1]

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

 

 

[ernestoo]

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

[calculate.machine]

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/ .