Solved

XSL format-number() issues

Posted on 2004-10-29
627 Views
Last Modified: 2009-07-29
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
Question by:enforge
    5 Comments
     
    LVL 15

    Expert Comment

    by:Thogek
    > 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
     
    LVL 21

    Expert Comment

    by:MogalManic
    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
     
    LVL 15

    Expert Comment

    by:Thogek
    Yeah, like that.  :-)
    0
     
    LVL 3

    Accepted Solution

    by:
    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
     
    LVL 1

    Author Comment

    by:enforge
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Suggested Solutions

    Title # Comments Views Activity
    help with simple xml php 22 90
    Write multiple XML child nodes with VB.Net 5 34
    Update a XML node 6 27
    c# & linq 7 77
    Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
    Browsing the questions asked to the Experts of this forum, you will be amazed to see how many times people are headaching about monster regular expressions (regex) to select that specific part of some HTML or XML file they want to extract. The examp…
    With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…
    Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

    913 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now