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

XSL format-number() issues

Hello,

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
0
enforge
Asked:
enforge
1 Solution
 
ThogekCommented:
> 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.
0
 
MogalManicCommented:
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:choose>
  <xsl:when test='$value &lt; 0'>
      (<xsl:value-of select='format-number(0-$value,$decimal-format)'/>)
  </xsl:when
  <xsl:when>  <xsl:when test='$value &lt; 0.005'>
      0.00
  <xsl:when>
  <xsl:otherwise>
      <xsl:value-of select='format-number($value,$decimal-format)'/
  </xsl:otherwise>
</xsl:choose>
0
 
ThogekCommented:
Yeah, like that.  :-)
0
 
DitmarBehnCommented:
What about writing your own number parser to handle these *small* or even large  numbers. Not really elegant but effective :-)

Regards, Ditmar

XML:
<?xml version="1.0" encoding="iso-8859-1"?>
<root>
<number>1</number>
<number>3</number>
<number>-3.5802584473078064E-7</number>
<number> -1.3467713688987721E-3</number>
<number>2</number>
</root>

XSL:
<?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="/">
    <root>
      <xsl:apply-templates />
    </root>
  </xsl:template>

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

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

  <xsl:template name="myNumber">
    <xsl:param name="myValue" />
    <xsl:choose>
      <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:variable>
        <xsl:choose>
          <xsl:when test="contains($op2, '-')" >
            <xsl:value-of select="number($op1) div number($op3)" />
          </xsl:when>
          <xsl:otherwise>
            <xsl:value-of select="number($op1) * number($op3)" />
          </xsl:otherwise>
        </xsl:choose>
      </xsl:when>
      <xsl:otherwise><xsl:value-of select="$myValue" /></xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

Result:
<root>
1.0000000000
3.0000000000
-0.0000003580
-0.0013467714
2.0000000000
</root>
0
 
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 :(
0

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