Where did that nvl() function go when creating BI Publisher Templates?
Lets say you've got this data:
<?xml version="1.0" encoding="UTF-8"?> <rowset> <row> <g1>1</g1> <g2></g2> <g3>3</g3> <g4></g4> </row> </rowset>
And in your template you put:
Sum: <?G1+G2+G3+G4?>
Uh-oh, you get the lovely number NaN or a nice long java error including:
Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number.
What to do? Well, you could rightly put nvl(G1,0) functions around whatever SQL query is generating your XML data, but if not then where's the nvl in BI Publisher lingo? Oh here it is:
Sum: <?xdofx:decode(G1,'',0,G1) + decode(G2,'',0,G2) + decode(G3,'',0,G3) + decode(G4,'',0,G4)?>
Well, not that lovely after all, but if it works ...
6 comments:
Hey Gareth
You also have the more compact XPATH expression.Now 'blogger' thinkks Im writing some scripting attack on the server if I put the complete expression in so hopefully you get the idea
< ? G1 [ . ! = '' ] + ...? >
Saves some space in those precious formfields.
Tim
Hi Tim,
I can't get the xpath syntax working for me ... still getting NaN, my thinking is even with the xpath null check null + 1 is NaN. Drop me an email ggr888 [AT] gmail [D0T] com
It becomes my main resource for Oracle E-Business Suite.
thank you for your INFO
Sem
Gave like
Not working...it tr=hrows error...
plz send mail to vrajesh_apps@yahoo.com if got any clue
ConfFile: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\config\xdoconfig.xml
Font Dir: C:\Program Files\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\fonts
Run XDO Start
Template: E:\Venkatesh\Shared_Venkatesh\Rajesh\17May10_Abstract_Report\Lease Abstract Report.rtf
RTFProcessor setLocale: en-us
FOProcessor setData: E:\Venkatesh\Shared_Venkatesh\Rajesh\17May10_Abstract_Report\Lease Abstract Report Torrance.xml
FOProcessor setLocale: en-us
java.lang.reflect.InvocationTargetException
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
at java.lang.reflect.Method.invoke(Unknown Source)
at oracle.apps.xdo.common.xml.XSLT10gR1.invokeProcessXSL(Unknown Source)
at oracle.apps.xdo.common.xml.XSLT10gR1.transform(Unknown Source)
at oracle.apps.xdo.common.xml.XSLT10gR1.transform(Unknown Source)
at oracle.apps.xdo.common.xml.XSLTWrapper.transform(Unknown Source)
at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(Unknown Source)
at oracle.apps.xdo.template.fo.util.FOUtility.generateFO(Unknown Source)
at oracle.apps.xdo.template.FOProcessor.createFO(Unknown Source)
at oracle.apps.xdo.template.FOProcessor.generate(Unknown Source)
at RTF2PDF.runRTFto(RTF2PDF.java:708)
at RTF2PDF.runXDO(RTF2PDF.java:517)
at RTF2PDF.main(RTF2PDF.java:305)
Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number.
at oracle.xdo.parser.v2.XSLStylesheet.flushErrors(XSLStylesheet.java:1534)
at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:521)
at oracle.xdo.parser.v2.XSLStylesheet.execute(XSLStylesheet.java:489)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:271)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:155)
at oracle.xdo.parser.v2.XSLProcessor.processXSL(XSLProcessor.java:192)
... 15 more
Hi VPR,
You're still trying to convert something to a number that won't work:
Caused by: oracle.xdo.parser.v2.XPathException: Cannot convert to number.
Regards,
Gareth
Hi,
I have similar error. First I thought it is due to a null value so, I corrected it and verified from the XML output that all null values are taken care of. Having done this still the error persists. I am calculating running total using a variable like so:
I have 3 4 similar variables doing almost the same job. When I take out all these variable and run the concurrent program the output report is generated successfully.
Also, using the xml data output the report is able to run locally; the problem occurs when run thru concurrent program.
Post a Comment