Oracle BI Publisher Blog

Another bad bad play on words that only my British and perhaps Indian brethren will see the link for. We used to, when I was a kid, make 'trunk' calls on our phones - today it would a be a 'long distance' call - someone has explained better here there is some interesting history behind it. Use it at your next dinner party to impress your friends ... maybe not!

To the more useful part of the post ... another question from our mailing list and another great answer from Kan who works in Oracle Consulting.

Hi, wanted to check if we can use the same TRUNC function avaliable in oracle db as shown below in BI Publisher (v10.1.3.3.2)

 select trunc(1234.123,0) from dual --> 123 
 select trunc(1234.123,2) from dual --> 123.12

please let me know if it's possible or if there is an alternative. 

You immediately maybe think, thats easy just use the ROUND function but its different ...

TRUNC(1234.125,2) --> 1234.12
ROUND(1234.125,2) --> 1234.13

As Kan says in his reply, to get this to work in XSL speak ...

You can use a combination of substr/instr to do this.

1. select trunc(1234.123,0) from dual --> 123       
                <?xdofx:substr(?123456.123?, 1, (instr('123456.123', '.', 1)+0))?> 

2. select trunc(1234.123,2) from dual --> 123.12    
                <?xdofx:substr(?123456.123?, 1, (instr('123456.123', '.', 1)+2))?> 

Not very friendly I know but doable - we need to build a new extension for this - its coming soon!