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!

NLS problem in Apex flash charts

Recently I had a situation to work with Apex Flash Chart graphs on Apex 4.1.

I was surprised when I saw that all number formats in flash graphs are using American NLS number format-decimal separator "." and group separator ",". Because I live in Croatia, where decimal and group separator signs are contrary, I was searching to find a solution to get a proper display of number values in flash charts.

The try

I was trying several things to work that out:

• Set default language in browser to Croatian.
• Set "Initialization PL/SQL Code" option (from Home|Application Builder|Application xxx|Edit Security Attributes) to:
  alter session set NLS_NUMERIC_CHARACTERS=',.'
• Try formatting values for chart

But they all failed-with no success. Decimal point was all the time "." and group operator was "," as decimal.

The solution

Then I realize that flash chart is independent piece of code running in Apex through it's external definition, in XML part of chart setting. Looking closely what is written there lead me to solution. Goto Home|Application Builder|Application xxx|Page yyy|Flash Chart Attributes, where your flash chart is placed.

Find Chart XML section and change Use Custom XML->Yes.

Then in next (enabled) XML section whith default values as:

<labels enabled="true" position="Outside">
  <font family="Tahoma" size="10" color="0x000000" />
  <format><![CDATA[{%Value}{numDecimals:2,decimalSeparator:\,,thousandsSeparator:.}]]></format>
</labels>

change highlighted row to:

<labels enabled="true" position="Outside">
  <font family="Tahoma" size="10" color="0x000000" />
  <format><![CDATA[{%Value}{numDecimals:2,decimalSeparator:.,thousandsSeparator:\,}]]></format>
</labels>

Result is finally as expected, graph with proper nls format setting

The End

Because Oracle is in total control of session values, I think that this code should be generated dynamically to follow real nls format values. Especially that this solution doesn't cover translated applications where such a setting is hard codded for all languages!

Hope this helps someone.

Cheers!