XSL format-number() issues


We've come across a few bugs in the Microsoft XSL parser and wondered if anyone could assist. All of them have to do with the format-number() function.

First, we have a problem with:

<xsl:value-of select="format-number(someElement, '###,##0.0')" />

It works fine for everything, but gives us NaN (error) when then XML value is -3.5802584473078064E-7. Strangely enough, it seems to work for other numbers like -1.3467713688987721E-3 for example. This stylesheet is being applied to a financial xml dataset and we don't have access to modify the sql on the backend, so I have to figure out a way to handle these numbers.

Second, we wanted financial numbers to show up as $1,234,567.00 and ($1,234,567.00) for positive and negative numbers, but using the formatting pattern thingy of <xsl:value-of select="format-number(someElement, '###,##0.00;($###,##0.00')" /> had issues for us. It seemed to work great until it started working with numbers above $1,000,000. At that point, it started formatting numbers as $1234,5678.00. I tried formatting patterns like $###,###,###.00 but that didnt work either. I read in a post somewhere that someone else said this is a bug where when using parens the xml parser gets confused and cannot handle it.

Right now as a solution we avoided using the positive/negative separator and so we're going with $1,234,567 and -$1,234,567. Unfortunately this is not what the end user wants, so I was hoping for a solution. I tried a choose/otherwise hoping to prepend and append the parens myself, but then it comes out wierd for negative numbers: (-$1,234,567).

Thanks in advance
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

> I tried a choose/otherwise hoping to prepend and append the parens myself,
> but then it comes out wierd for negative numbers: (-$1,234,567).

You can probably remove the negative sign in this case by placing a negative sign in front of the number variable, or subtracting it from zero, when you render it.
If there is bugs in the format-number function you could work around them like this:
<xsl:variable name='decimal-format'>$###,###,##0.00</xsl:variable>
  <xsl:when test='$value &lt; 0'>
      (<xsl:value-of select='format-number(0-$value,$decimal-format)'/>)
  <xsl:when>  <xsl:when test='$value &lt; 0.005'>
      <xsl:value-of select='format-number($value,$decimal-format)'/
Yeah, like that.  :-)
What about writing your own number parser to handle these *small* or even large  numbers. Not really elegant but effective :-)

Regards, Ditmar

<?xml version="1.0" encoding="iso-8859-1"?>
<number> -1.3467713688987721E-3</number>

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
*** Author:    Ditmar Behn
*** Date:      2004-11-01
*** Origin:    www.experts-exchange.com

  <!-- Start the pattern matching mechanism in the root element -->
  <xsl:template match="/">
      <xsl:apply-templates />

  <xsl:template match="number">
    <xsl:variable name="myValue">
      <xsl:call-template name="myNumber">
        <xsl:with-param name="myValue" select="text()" />

    <xsl:value-of select="format-number(number($myValue), '###,##0.0000000000')" />

  <xsl:template name="myNumber">
    <xsl:param name="myValue" />
      <xsl:when test="contains($myValue, 'E')" >
        <xsl:variable name="op1"><xsl:value-of select="substring-before($myValue, 'E')" /></xsl:variable>
        <xsl:variable name="op2"><xsl:value-of select="substring-after($myValue, 'E')" /></xsl:variable>
        <xsl:variable name="op3">
          1<xsl:value-of select="substring('000000000000000000000000000000000000', 1, number(translate($op2, '-', '')))" />
          <xsl:when test="contains($op2, '-')" >
            <xsl:value-of select="number($op1) div number($op3)" />
            <xsl:value-of select="number($op1) * number($op3)" />
      <xsl:otherwise><xsl:value-of select="$myValue" /></xsl:otherwise>


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
enforgeAuthor Commented:
Thanks all for the answers.

We had come up with the idea of doing an if/else for our numbers and handling the parens and stuff ourselves, but were looking for a less gaudy solution (since there are hundreds of references that would have needed the custom if/else code logic).

In the end, we just decided to forego parens for negative numbers and tell the users it's a technology limitation :(
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.