DataFormWebPart Sum of Currency Columns

Question

I have a data form web part with the "sharepoint list with currency columns" data source. I have added a footer to sum the rows. It seems like because of the number columns as currency format are formatted as "$2,000,000.00", the total shows up on the footer is "NaN".

Looking at the XSLT, I don't see any issues with the XSLT code - <xsl:value-of select="sum($nodeset/@Year_x0020_1_x0020_P_x0026_L_x002)" />

Is there any reason why currency columns are not summable in the XSLT? Has anyone came across this kind of issue?

Answer 1

It seems like data view web part thinks @Year_x0020_1_x0020_P_x0026_L_x002 is above example is not number column. I don't see all the grouping option under lightning thunderbolt sign.. Is there any reason why data view web part treats sharepoint number columns as text? I understand its XML but do I have to specifically add the columns as numbers?

Answer 2

I have fixed this issue. I have changed current columns to number columns. The reason for this is number column saves the data as comma and DVWP can't convert comma separated number string to number.

Here is the brilliant article to discuss this issue..

http://www.endusersharepoint.com/2009/08/06/calculated-column-formula-tips-aggregating-calculations-over-1000/

Use this to fix the problem..

• Create the Calculated Columns Based on Number columns in SharePoint List
• Make sure you use FIXED function to remove all the commas. Otherwise SharePoint saves number as comma and DVWP can’t convert them into number.
• Now, in DVWP, use the XSLT format-number function to format the number back to comma/currency formatted number. E.g.
  - Item - Number format - format-number(@Year_x0020_1_x0020_Revenue_x0020, '#,##0.00;-#,##0.00')
  - Item – Currency format - format-number(@Year_x0020_1_x0020_Revenue_x0020, '$#,##0.00;-$#,##0.00')
  - Sum – Currency format - format-number(sum($Rows/@Year_x0020_1_x0020_Revenue_x0020), '$#,##0.00;-$#,##0.00')
• That should fix the issue...

Answer 3

Hello,

I've got a similar problem in the DataFormWebPart.

The field type is number. If the number is more than 1000, the sum of the grouping is NaN. 

The code <xsl:value-of select="format-number(sum($nodeset/@MyField), '#.00;-#.00')" /> doesn't work - instead of NaN there's no value at all.

Answer 4

The code <xsl:value-of select="format-number(sum($nodeset/@MyField), '#.00;-#.00')" /> doesn't work - instead of NaN there's no value at all. Put a "." on the end of the value. example: @MyField. So your code would be: <xsl:value-of select="format-number(sum($nodeset/@MyField.), '#.00;-#.00')" />

Answer 5

Wow! You nailed it!!! Adding the dot worked perfectly. Thanks!

Answer 6

I know this is old, but I have been search everywhere for this right here. PERFECT!

---

Sean Page SBS 2003 SP2

Answer 7

New to modifying XSLT in SharePoint.

Which part of the XLST code are you modifying?

The normal 'SUM' code returns Sum=float;#0. I'd like to overcome this using format-number but I can't figure out where to apply it. Any help is appreciated.

            </xsl:when>
            		<xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
               		<xsl:value-of select="'Sum'"/>

            </xsl:when>
            		<xsl:when test="$XmlDefinition/Aggregations/FieldRef[@Name=$fieldName]/@Type='SUM'">
                        <xsl:value-of select="concat(@Name, '.SUM', $aggregateLevel)"/>