Archive | SharePoint RSS feed for this section

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.



2 Jun

Just starting out and trying to figure out how to use WordPress.  I goal with this site is to provide tips and references, etc. for SharePoint as I come across them during the course of my work and to share my ideas and solutions on projects that I am currently working on.  I hope to update this daily, but we’ll see how that goes!

Thanks for stopping by!