No NaN for me please

Being British, I of course love Indian food, its almost our national dish back in the UK. Roast beef and yorkshire pud is no longer the go to faire. Its a spicy hot chicken Pathia or Dal for me all the way. Thats sometimes hard to find here in Colorado, we have taken to making our own having found only a handful of Indian restaurants in Denver and Springs (suggestions welcome locals :0) Nan bread is now available in local supermarket so we have not built our own tandoori oven.
My tenuous bog post title is not referring to that delicious bread but to an XSL and therefore Publisher issue, well maybe be not an issue; more of something you may need to handle.
there is no native support to handle divide by zero errors or maybe mistakenly dividing a string into a number. Right now the only way around it is to check every value prior to using it in a calculation e.g.

<?if: XXXX >0?> <?XXXX div YYYY?><?end if?>

Not so bad, but if you have multiple calculations to handle in a template it can become a pain and of course is a bunch of code to write or at best copy and paste.
There was a questions on the forum this week asking if BIP had a safeDivide function like Actuate has? After a little digging to find out what the heck a safedivide function was, my initial thought was to create a template(function) for the template to call prior to any divide functions. I am from the school of 'why re-invent the wheel' so Google it took another hit. Lots of cool solutions out there; one of my favorite sites is from Dave Pawson of XSLT book fame - There is a mass of answers on Dave's site from a host of experts, well worth bookmarking.
Dave's site popped up this solution

Define a format name, like so:

<xsl:decimal-format name="MyFormat" NaN=" - " zero-digit="0"/>

And use it with your format statement:


this is much simpler than building a function. The first statement needs to be placed at the top of your template. In this case if a divide by zero occurs i.e. a NaN then a '-' will be returned.

First thought, corrected and a solution found. My second thought, we need to provide something natively to handle this for folks. Some folks have differing requirements in separate parts of an output, maybe 'N/A' in sme cases and '0' for others. So, I logged an enhancement request to handle this. I logged 9355897, Template Meister Hok Min picked it up and implemented the enhancement within the day. Now waiting for a release vehicle but there's service for you, logged, coded and tested within the day. If you are desperate for it, you can get support to log a one off request on your behalf.


Tim, (kind of on topic) Can't help you with good restaurants in Colorado and where I am in Victoria BC we have a large Indo-Canadian population so good Indian food is always available. The naan bread that we have in the stores here is just not the same as what we get in a good restaurant so I've reverted to making my own to accompany our homemade Indian food. There are a number of good recipes available on the web and I just pre-heat a large pizza stone in a 500 degree oven in place of a proper tandoori oven.

Posted by Mike Charchuk on February 18, 2010 at 05:01 AM MST #

hello Tim, thanks for the post. when I try to test the solution I'm getting the following error: : XML-22047: (Error) Invalid instantiation of 'xsl:decimal-format' in '_B' context. seems that I have not clear understanding of the "xls:" tag using. My references to documentation gives nothing. I'm using BI P Enterprise standalone. -- regards, Eldar A.

Posted by Eldar.A on July 26, 2010 at 08:06 PM MDT #

add: BIP Enterprise (build:1145) -- regards, Eldar A.

Posted by Eldar.A on July 26, 2010 at 08:08 PM MDT #

In one of the post on the OTN, I've found the following initialization: <xsl:decimal-format xdofo:ctx="begin" name="MY" NaN="0" zero-digit="0"/> that's work perfect. As for my understanding, the solution, do not cover the total sum calculation (if some fields contain null). This solution only resolves 'NaN' displaying. -- kind regards, Eldar A.

Posted by Eldar.A on July 26, 2010 at 11:05 PM MDT #

Good post, thanks

Posted by Bakersfield Plumbers on September 10, 2010 at 09:21 PM MDT #

Hello - i know it is a 3 years later but i am new - and i have question - where exactly i have to input this pieces:

1: xsl:decimal-format name="MyFormat" NaN=" - " zero-digit="0"

2: format-number(@totalAmt,'#,###','MyFormat')


Posted by guest on June 25, 2013 at 11:31 PM MDT #


facing one issue . i am using bip
BIP Enterprise .when doing sum for some of the columns and doing zero devide system giving "NAN" error .Please suggest

Posted by subrath on December 19, 2014 at 04:39 AM MST #

Post a Comment:
  • HTML Syntax: NOT allowed

Follow bipublisher on Twitter Find Us on Facebook BI Publisher Youtube ChannelDiscussion Forum

Join our BI Publisher community to get the most and keep updated with the latest news, How-to, Solutions! Share your feedback and let us hear your voice @bipublisher on Twitter, on our official Facebook page, and Youtube!


« July 2016