X

An Oracle blog about BI Publisher

Addition Not Applicable

Apologies for the tenuous title, I went looking for a synonym for today's subject and came up with this, anyway, moving on ...



Have you ever created a summary column, run your template against some data only to find it either falls over or you get that friendly 'NaN' entry in your output frustrating isnt it? Just to clear up one thing, 'NaN' means surprisingly enough, 'Not a Number', if you want to know more check out the definition here.



The most common cause of the issue is you have a null value in your XML ie <AMOUNT></AMOUNT> and sadly the XSLT engine does not know how to handle it. 
So how to handle nulls? We now have a decent section in the user guide on handling nulls buts here's a couple.

Lets assume we have the following XML:

<AMOUNTS>
<AMOUNT>100</AMOUNT>
<AMOUNT></AMOUNT>
<AMOUNT>200</AMOUNT>
<AMOUNT>300</AMOUNT>
<AMOUNT></AMOUNT>
</AMOUNTS> 


If we just do a simple summary to add up the amounts sum(.//AMOUNT) we would get the NaN output. We have two options to get around this:



1. Check for the Null first

We can use an XPATH expression to test the value before adding it:

<?sum(.//AMOUNT[.!=''])?>
yes, its effectively testing for an empty string but this will give us the correct answer i.e. 600



2. Use the BIP Sum function



A little less effort, we can remember to use the BIP built in function xdoxslt:sum. This will check for null values for you.



<?xdoxslt:sum(AMOUNT)?>




Both options work of course, of the two for simple summing either will work fine, if you have a more complex calculation then the second option is going to be better. Thats not to sat you could not use the XPATH but things start to get tedious checking all your values for nulls when the BIP sum function will do it for you. Before you ask, yes the Template Builder should give you the option and insert it for you ... I have logged the ER. Samples here.

Join the discussion

Comments ( 3 )
  • PhillDoc Monday, February 8, 2010
    Great story you got here. I'd like to read a bit more concerning this theme. Thanx for sharing that data.
  • indian escort Thursday, May 27, 2010
    Really good blog to track it to my mind. I have a question, why haven't you you add this article to social media? That should bring rather big traffic to this page.
  • LeeAnne Tuesday, December 13, 2011

    Perfect and straight to the point. I appreciated the examples. Thanks!


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.