DataForm Webpart, XSLT, and NaN

3 Jun

The other night I was playing around with the dataform webpart, trying out different XSLT code to get a better grasp on it.  Where I work, we are only allowed to use SharePoint Designer so I have had to spend a lot of time learning about the DataView webpart, and the more I learn, the more impressed I am.  Now I am less inclined to jump straight into Visual Studio when developing SharePoint solutions.  Anyway, I was playing around with a inventory cost list based on a spreadsheet I found online and I was trying using the format-number function in XSLT.

The function does exactly what it says, format numbers.  You can read more about the function at the following link.  I used the following code to format currency:

<xsl:value-of select="format-number(@Total,'$#.##')"/>

Everything looked fine, until I noticed that cells containing values over a 1,000 were empty.  I tried entering the function using the Formula button in the SharePoint Designer ribbon and noticed that values over 1,000 were turning up as NaN (or Not a Number).

Values greater than 1,000 are not displayed when using format-number function in XSLT.

Since I imported the spreadsheet into SharePoint (for which I encountered a whole other set of issues I will blog about later) I thought that maybe the type of the column was set to Single line of text and maybe that was causing the issue.  Looking at the List Information  screen, the column was set to Number.  Since the values under a thousand were displaying ok, I wasn’t 100% convinced that was the issue.  I did notice though that the column automatically digit-grouped the numbers, adding a comma to values greater than 1,000.  It then dawned on me that the comma must be causing the function to spit out NaN.  I adjusted my formula to the following:

<xsl:value-of select=”format-number(translate(@Total,’,’,”),’$#,###.00′)” />

Sure enough, values over 1,00o displayed correctly.  Basically the translate function takes the first parameter, searches it for instances of the second parameter, and replaces it with the third parameter.  In my case, it took the Total value and replaced the comma with nothing before processing.  The format-pattern in the format-number function adds back the comma in the proper place.   The other thing you will notice is that I replaced the place-holders after the period from # to 0. When it was set to #, I notice that values that ended in zero were truncated.  For example, 60.00 would be turned to $60, or 49.90 would become $49.9, but 59.09 would display as $59.09.   The # place-holder displays 0 as absent when it starts or ends the number and that is why some numbers were correct while others were truncated.

If you have encountered this issue, share how you solved it below.


One Response to “DataForm Webpart, XSLT, and NaN”

  1. EW July 24, 2012 at 9:18 pm #

    I have tried this and it works very well. However I have tried applying this to the sum of the columns and it doesn’t work. Do have any ideas why?

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: