• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 636
  • Last Modified:

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
1 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).

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>

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 :(

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now