Should Divide Alphablox Relational Reporting By Zero !

If you are doing relational reporting using IBM DB2 Alphablox, you might face this situation.

In Alphablox, you can define calculated column using bloxreport:calculate custom action. However, the functionality supported by the expression is pretty limited, even to say "Quite Useless". According to the offcial little documentation, only arithmetic +, -, *, /, sum(), (), runningTotal, rank, runningCount, and percentOfTotal are supported. Missing from it is the ability to conditionally process the value in the calculated column.

Now, I created a calculated column that contains the expression "NEWCOL = COL1 / COL2". A typical problematic scenario is when the value of COL2 is zero, and hence the expression is evaluated to "Divided by Zero". Alphablox Relational display a demonic "?" for such a result. No customer want to see their reports flooded with alot of "?".

After hours of finding and hacking around the classes and constants provided by Alphablox Relational. A feasible workaround is to replace the "Divided by Zero" value using a combination of Alphablox Relational tags and Javascript.

Here is one example of such solution:



<script>
function fix(s) {
if (s=="?") document.write ("0");
else { document.write (s) }
}
</script>

<bloxreport:report id="sample1" errors="true" bloxname="sample5">
<bloxreport:canneddata></bloxreport:canneddata>

<bloxreport:calculate expression="col1 = 0.0"></bloxreport:calculate>
<bloxreport:calculate expression="col2 = 0.0"></bloxreport:calculate>
<bloxreport:calculate expression="col3=col1/col2"></bloxreport:calculate>

<bloxreport:text>
<bloxreport:data columnname="col3" text="<script>fix('<value />');</script>" /> </bloxreport:text>

</bloxreport:report>


The idea is to invoke custom logic to manipulate the cell value in the client browser, instead of the server. This is solely because I can't find any documented API that allows me to do so in the server side.


Though it works, more testings are required for the above solution, especially when exporting the relational report to pdf and excel. These report delivery environments might not understood Javascript, hence wouldn't display any value for the formatted cells.

Let cross our finger and hope for similar enhancement to the Alphablox Relational report formatting capability in its' future releases. I have to mention here that even JasperReport is superior than Alphablox Relational in this area.