How to format numbers with commas in SharePoint Dataview (using decimal-format functionality)

November 11th, 2011 | Posted by Marijn in code | css

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")'/>

You can follow any responses to this entry through the RSS 2.0 You can leave a response, or trackback.

One Response



Leave a Reply

%d bloggers like this: