XSL format-number() issues
Posted on 2004-10-29
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