Tuesday, June 26, 2012

Custom Aggregation in Apex IR footer

As I was not to much involved in proffi Apex programming since 2008, when I build most valuable Croatian WEB site, Croatian Depository & Clearing Company, where more then 50 bil. € stocks are saved, I didn't have to much challenges in this area until recently.

The problem

Then I got an interesting ask: "In Apex Interactive report, calculate index column as average on two other visible columns and show "Total average". First part was easy-SQL column as average calculation of other two. But problem came when I wanted to show "Total average" value, because I couldn't use implemented IR feature "Aggregation" on average column-average computation on average values is not correct average!!! So I let this value to blank to see what can be done later.
Let me show a picture of problem with report with activated "Control Brake" on first two columns, what results as virtual two reports:

The solution

Analyzing page source with Web Developer show me next data: Hopefully there was created placement for average calculation, but it was blank.
Thanks to Davor Zelić who has enough nerves and will to handle this problem, solution was made in a classic way-custom java script.
The result is this source javascript file which setAggrIndexValue method is the one that do the work, while getElementsByAttribute method is used to find proper values for calculation.
This java script file, as usual, has to be uploaded in workspace/application image repository (or place on Apache images location if using Apache as WEB server), and put it's call directly on custom page header or in page template (find more appropriate solution).
After that on page, where Interactive report is placed, fill call of java script function in page property in "Footer" part of "Header and Footer" section as:
If you start that report and look now in WEB Developer debuger inspector, you'll see that values are filled properly-with real average values.
Result is exactly what I wanted, even thought if there are more then one average calculation on page ("Control Brake" case).

The End

As you see, this solution is pretty generic and real text function call can be viewed as pure text in mentioned java script source, last three lines as comment. The only condition is that calculation values are visible in Interactive report, what is more then common requirement and reasonable condition.
Beside all thisi approach allow very easy implementation of other custom aggregation when you have need for that.
Hope this helps someone.

Cheers!

15 comments :

  1. Why not use an Oracle Analytical functions to handle doing the average? Using a rollup on your columns with the proper grouping would provide the weighted average you want, right??

    ReplyDelete
    Replies
    1. The result is not the problem but how to show it in Apex page in footer with correct numbers.

      Delete
  2. Hi,

    now i am facing the problem with to display that row as first row.

    i need to display the total aggregate result as first row of the report

    can you please help me on this?

    regards
    shekar

    ReplyDelete
    Replies
    1. For that you can use analytic functions directly in oracle.

      Delete
  3. Thanks Damir,

    I need to display only grand totals not sub totals, for my requirement i should use APEX aggregation functionality.
    is there any way to display as header or first row?

    regards
    shekar

    ReplyDelete
    Replies
    1. Look in mine js code.
      With WEB developer (in FF) find heading name element and change it according that value you'll find in header. Script is pretty simple for debugging.
      Rg
      Damir

      Delete
  4. Hi Damir,

    Thanks for your reply, really i am not that much intelligent.
    could you please explain how to write and where i should write that one?

    best regards
    sheakr

    ReplyDelete
    Replies
    1. Send me an Excel with exact data (number of columns and proper names) what you need and will try when I get time.
      Or even better make an demo of your app on Oracle public Apex site, send me login data.

      Delete
    2. "http://apex.oracle.com" is wher you can show your demo

      Delete
  5. Hi,

    Here i am showing my app demo.

    http://apex.oracle.com/pls/apex/f?p=22641:2
    Username and Password : test

    I just need to display that colored row as first row of report..

    regards
    Shekar

    ReplyDelete
    Replies
    1. OK.

      If you display first row as normal row then total at the end will not work. If this is OK, then let me know I'll try to make your wish alive-seems to me possible

      Delete
    2. give me login data for workspace t be able to test code

      Delete
  6. Thanks Damir,

    Workspace : chandu_jnet

    ReplyDelete
  7. Hi Damir,

    Thanks for this great information. I have implemented the solution successfully, but when viewing it in IE9 - the calculated value doesn't appear. I have tested it in Chrome/Firefox and on my iPad(Safari).

    Can you please advise?

    Regards
    Mynhardt

    ReplyDelete

Zagreb u srcu!

Copyright © 2009-2014 Damir Vadas

All rights reserved.


Sign by Danasoft - Get Your Sign