Oracle BI Publisher Blog

Good question from Remc0 on the forum recently.

When the following in a group:
<?sum(current-group()/_saw_8_[.!=鋳])?>

It returns a 0 when all values are null. But 0 means smaller then 0,5 and null means it is not there. Now my question is: how can I sum the values and make a different between 0 and null?

Its a good question and there is a reply to use the updateable variables - I have to admit an aversion to updateable variables - they are expensive and are not a feature of native XSL - there are other ways around using the 'variables'

Remco's basic problem is that the code above that checks and eliminates null values in a sum function returns a zero value when all values are null. For example with the following XML.

<AMOUNTS>
<AMOUNT type="INV"></AMOUNT>
<AMOUNT type="INV"></AMOUNT>
<AMOUNT type="CM"></AMOUNT>
<AMOUNT type="CM">100</AMOUNT>
</AMOUNTS>

The sum of the amounts of the type 'CM' using the XPATH null elimination:

<?sum(AMOUNT[@type='CM' and .!=""])?>

as an aside, notice we're combining XPATH expressions with an 'and' - we want to sum all AMOUNT values where the type attribute is CM and the values are not 'null'. In this case we get '100' returned - thats good. If we sum the INV types:

<?sum(AMOUNT[@type='INV' and .!=""])?>

we get a zero returned - which as Remco notes is not actually correct - well not for his requirement, he wants to see an empty string.

My approach was to use our inline 'if' statement.

<?xdoxslt:ifelse(sum(AMOUNT[@type='CM' and .!=""]) = 0,'',sum(AMOUNT[@type='CM' and .!=""]))?>

The if statement tests the returned value and if its a zero ie the XML values are null then an empty string (two single quotes) is inserted otherwise it returns the calculated amount. This is fine if all the values are null but if the returned sum value happens to be '0' then an empty string will be returned.

In the interest of audience participation, here's a challenge. Can anyone come up with the XPATH necessary to return the real zero values and handle the null case too?