Jump to content

Really tricky (impossible) query


mnel_1

Recommended Posts

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

I have this table setup.

relation.png.daa44aff5dce607613a04d83ab888179.png

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

grid.PNG.ef47c9336223c7edb0b27829bbc1c03a.PNG

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

 

 

Link to comment
Share on other sites

  • Replies 2
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Posted Images

  • 4 months later...

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


Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...