Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

XSL format-number() issues

Posted on 2004-10-29
5
Medium Priority
?
633 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
Comment
Question by:enforge
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 15

Expert Comment

by:Thogek
ID: 12448688
> 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
ID: 12456531
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
ID: 12458488
Yeah, like that.  :-)
0
 
LVL 3

Accepted Solution

by:
DitmarBehn earned 1000 total points
ID: 12465577
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
ID: 12596462
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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Client Need Led Us to RSS I recently had an investment company ask me how they might notify their constituents about their newsworthy publications.  Probably you would think "Facebook" or "Twitter" but this is an interesting client.  Their cons…
Many times as a report developer I've been asked to display normalized data such as three rows with values Jack, Joe, and Bob as a single comma-separated string such as 'Jack, Joe, Bob', and vice versa.  Here's how to do it. 
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

618 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