Number with decimals, shows as 'NaN' in DataFormWebPart

Question

Hi,

I have a DataFormWebPart that exposes items from a custom SharePoint list.

One of the columns in the SharePoint list is of data type Number with 2 decimal places.

The problem is that all values with decimals (other than ,00) shows in the DataFormWebPart as 'NaN'.

Here is the xls that is generated by SPD 2007:

<xsl:value-of select="format-number(@Timer, &quot;#.##0,00;-#.##0,00&quot;, &quot;lcid1030&quot;)"/>

How do I fix this problem ?

Answer 1

Hi,

 

I understand you that there has a column which data type is Number with 2 decimal places in a SharePoint list, when creating a Data Form Web Part using the list, the Number column shows as ‘NaN’.

 

I am sorry I cannot reproduce the issue, but I find that your XSL code is different from my code. I think the format of the XSL is incorrect, please try to change it to this: <xsl:value-of select="format-number(@Timer, ‘#,##0.00;-#,##0.00’)"/> .

 

Let me know if I misunderstand you.

Xue-Mei Chang

TechNet Subscriber Support in forum

If you have any feedback on our support, please contact [e-mail]

---

Xue-Mei Chang

Answer 2

Hi,

1) only numbers with decimals other than ',00' is exposed as NaN - ie. 5,25 as 'NaN' (Not OK) and 5,00 as 5,00 (ok)

2) this string: <xsl:value-of select="format-number(@Timer, &quot;#.##0,00;-#.##0,00&quot;, &quot;lcid1030&quot;)"/>
is generated by SPD 2007 - I have NOT altered the string.

'lcid1030' is present because the number format should be Danish (decimal point is comma, thousand delimeters is dot).

Answer 3

Hi,

I found the problem described here: http://vspug.com/wirkus/2008/04/07/kleiner-bug-im-data-view-webpart/  

Changing

- <xsl:value-of select="format-number(@Timer, &quot;#.##0,00;-#.##0,00&quot;, &quot;lcid1030&quot;)"/>

to

- <xsl:value-of select="format-number(translate(@Timer,&quot;,&quot;,&quot;.&quot;), &quot;###0,00;-###0,00&quot;, &quot;lcid1030&quot;)"/>

solved the problem.

But I can't figure out how to modify the XSLT for calculating the sum - SPD generates

- <xsl:value-of select="sum($Rows/@Timer)" />

which results in 'NaN'.

Can you ?

Answer 4

Hi,

 

Glad to know that you have solved the problem.

 

I think this is because the XSL sum function will return “NaN” if the node value is not a number or empty. To fix the problem, you just set the default value of the field to 0 and the function proceeded to calculate the sum.

 

Hope it helps.

Xue-Mei Chang

TechNet Subscriber Support in forum

If you have any feedback on our support, please contact [e-mail]

---

Xue-Mei Chang

Answer 5

Hi,

The field is already defined with default = 0 and every single row actually contains a valid number.

If I change the values so every number has ',00' as decimals the sum-function works - but if values with other decimals (ie. ',25') exists the sum-function shows NaN.

My guess is: SharePoint returns numbers with localized decimal point (this is comma) and this value is input to the sum-function that expects dot as decimal point.

Work around: if I could translate the decimal point character before the number is input to the sum function it may work.

But - I can't figure out how to modify (adding translate-function ?)

- <xsl:value-of select="sum($Rows/@Timer)" />

to do this translation.

Can anyone.

Answer 6

Hi

The only workaround i founded was to write your own sum function template:

<xsl:template name="sumAll">
  <xsl:param name="node"/>
  <xsl:param name="sum" select="0"/>
  <xsl:choose>
    <xsl:when test="$node">
      <xsl:call-template name="sumAll">
        <xsl:with-param name="node" select="$node/following-sibling::Row"/>
        <xsl:with-param name="sum" select="$sum + translate($node/@WorkHours, ',.', '.')"/>//use wanted field
      </xsl:call-template>
    </xsl:when>
    <xsl:otherwise>
      <xsl:value-of select="format-number($sum, &quot;#.##0,00;-#.##0,00&quot;, 'lt')"/>//adopt to your locale
    </xsl:otherwise>
  </xsl:choose>
</xsl:template>

and then you can call it like this:

<td class="style2" nowrap="">
      <xsl:call-template name="sumAll">
            <xsl:with-param name="node" select="//Time_Log/Rows/Row"/> //need to change data source
       </xsl:call-template>
</td>

You need to change the code according to you locale.

Answer 7

Hi Mindaugas, i cannot manage to get your code to work :( It gives me this error:

 

Impossible to define more than once model "sumAll" with the same import priority.

 

Could you give me an advice?

 

My locale is ITALIAN

Answer 8

You realise that the post you are replying to is one year old ?

The person you are asking may still be in the forums but it is rather unlikely that he will see your post.

(Moderator)

---

SP 2010 "FAQ" (mainly useful links): http://wssv4faq.mindsharp.com/default.aspx
WSS3/MOSS FAQ (FAQ and Links) http://wssv3faq.mindsharp.com/default.aspx
Both also have links to extensive book lists and to (free) on-line chapters

Answer 9

<xsl:value-of select="format-number(@Timer, "#.##0,00;-#.##0,00", "lcid1030")"/> adding a "." after field @Timer works for me. <xsl:value-of select="format-number(@Timer., "#.##0,00;-#.##0,00", "lcid1030")"/>