In a dashboard project we had to display certain valuta fields that came from a SQL-SERVER table.Those fields had following formatting:
139367709,8800
0,00000,00
51345998,3700
The goal was to display them as European style valuta:
139.367.709,88
0,00
51.345.998,37
To format those numbers to numbers with commas in SharePoint Dataview I used the xslt format-number() function
<xsl:value-of select="format-number(@YourField, "$#,##0.00;-$#,##0.00")"/>
Result was empty fields and a sad look on my face.
So I asked the help of a developer (since I am not one, but I like to mess around in code).
Apparently, the commas in the numbers were giving the format-number() a hard time to recognize it.
After some playing around with the string() and number() functions, I was no step closer.
What I thought would help me, was to replace the commas with a point. I tried the w3schools xslt replace() function, but that gave me an error in SPD, saying the function was not recognized, although it appears to be a valid xslt function.
The trick here is to use the translate() functionality, which is essentialy the same as replace(), but it converts the string instead of replacing it.
<xsl:value-of select='format-number(translate(@YourField,",","."), "#.##0,00")'/>
Now we had values instead of blanks, but the formatting still wasn’t right.
Switching the point and comma in the format-number gave errors as well. Then our eyes fell on the optional decimalformat parameter.After a quick google for a working example we learned that we had to add a separate xsl command to describe how we wanted our field to look like:
<xsl:decimal-format name="european" decimal-separator=',' grouping-separator='.' />
Note: the description of the decimal-format has to be placed outside the xsl:template your field is in because it is not a valid line that you can add in a xsl:template.. More about the decimal-format here.
The final xsl line looked like this and gave us the correct result:
<xsl:value-of select='format-number(translate(@YourField,",","."), "#.##0,00", "european")'/>
Long story short
If you want to translate numbers, use the default SPD “Format item as – number” functionality or build-in Xpath Expressionbuilder which will get you something like following code:
<xsl:value-of select="format-number(@YourField, "$#,##0.00;-$#,##0.00")"/>
If you want to display it in a different way, use the decimal-format functionality which will get you something like this:Outside the xsl:template block:
<xsl:decimal-format name="european" decimal-separator=',' grouping-separator='.' />
To display the field:
<xsl:value-of select='format-number(translate(@YourField,",","."), "#.##0,00", "european")'/>
My friend, after 4 hours of pain I found your article and solved my problem. Thanks a lot.