none
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 ?

    Monday, December 07, 2009 11:55 AM

Answers

  • 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.
    • Marked as answer by TRoennow Saturday, December 12, 2009 10:13 AM
    Friday, December 11, 2009 1:23 PM

All replies

  • 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 tngfb@microsoft.com


    Xue-Mei Chang
    Tuesday, December 08, 2009 2:50 AM
    Moderator
  • 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).

    Tuesday, December 08, 2009 11:27 AM
  • 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 ?

    Wednesday, December 09, 2009 10:06 AM
  • 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 tngfb@microsoft.com


    Xue-Mei Chang
    Thursday, December 10, 2009 3:07 AM
    Moderator
  • 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.
    Thursday, December 10, 2009 12:38 PM
  • 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.
    • Marked as answer by TRoennow Saturday, December 12, 2009 10:13 AM
    Friday, December 11, 2009 1:23 PM
  • 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

    Thursday, December 09, 2010 5:29 PM
  • 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
    Thursday, December 09, 2010 5:42 PM
  • <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")"/>
    Wednesday, November 16, 2011 5:42 PM