XSLT sum and xpath
-
I am trying to sum the costs values in this sample xml using the xslt below. I expect to see a subtotal of 92, ie the sum of all costs in the userfieldgroupid = 9. Instead I get:
Result -
Item $
1.5 69
0.5 23
Subtotal 69 23
I suspect the issues is with the xpath and that my result is the individual costs summed with themselves. If I use <xsl:value-of select="sum(//costs)"/> I get:
Result -
Item $
1.5 69
0.5 23
Subtotal NaN
I am using an IE control in a database application to render a data view. I have searched and tried so many examples but can't work this out so I would be very grateful for any help you can give me.
Sample xml:
<?xml version="1.0"?><xml><object><objectid>183382</objectid><objectnumber>Test00001237</objectnumber><lccs><lcc><groupname>Costs: Acquisition planning</groupname><userfieldgroupid>9</userfieldgroupid><userfieldname>Concept development hours</userfieldname><userfieldid>42</userfieldid><fieldvalue>1.5</fieldvalue><depreciationlife>25</depreciationlife><costs>69</costs></lcc><lcc><groupname>Costs: Acquisition planning</groupname><userfieldgroupid>9</userfieldgroupid><userfieldname>Concept assessment hours</userfieldname><userfieldid>43</userfieldid><fieldvalue>0.5</fieldvalue><depreciationlife>25</depreciationlife><costs>23</costs></lcc></lccs></object></xml>Sample xslt:
<?xml version="1.0" encoding="UTF-8"?><xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:js="urn:custom-javascriptscript" exclude-result-prefixes="msxsl js"><xsl:output method="html" omit-xml-declaration="yes" doctype-public="-//W3C//DTD HTML 4.01 Transitional//EN" doctype-system="http://www.w3.org/TR/html4/loose.dtd" indent="yes"/><xsl:template match="/xml/object"><html><head></head><body><table><tr style="font-weight:bold" ><td>Item</td><td>$</td></tr><xsl:for-each select="lccs/lcc"><xsl:choose><xsl:when test="normalize-space(userfieldid)='42'"><tr><td> <xsl:value-of select="normalize-space(fieldvalue)"/></td><td> <xsl:value-of select="normalize-space(costs)"/></td></tr></xsl:when><xsl:when test="normalize-space(userfieldid)='43'"><tr><td><xsl:value-of select="normalize-space(fieldvalue)"/></td><td><xsl:value-of select="normalize-space(costs)"/></td></tr></xsl:when></xsl:choose></xsl:for-each></table><table><tr><td>Subtotal</td><xsl:for-each select="lccs/lcc"><xsl:choose><xsl:when test="userfieldgroupid='9'"><xsl:if test ="costs !=''"><td><xsl:value-of select="sum(costs)"/></td></xsl:if></xsl:when></xsl:choose></xsl:for-each></tr></table></body></html></xsl:template></xsl:stylesheet> -
One small change needed
<td><xsl:value-of select="sum(../../*/*/costs)" /></td>
-
Hi thanks so much for replying and helping me however when I make the change you suggest the result I get is :
Result -
Item $
1.5 69
0.5 23
Subtotal NaN NaN
The values that were previously displayed as numbers now read NaN and still in the list rather than sum.
The underlying data is from a SQL data base and is in decimal format.
I tried using <xsl:value-of select='format-number(sum(../../*/*/costs), "###,###.00")' /> and this gives the same result as above, ie with NaN
If I just use <xsl:value-of select='format-number(costs, "###,###.00")' /> directly on costs I get the numbers, but not summed! so back to where I started.
Result-
Item $
1.5 69
0.5 23
Subtotal 69.00 23.00
Grateful for any further suggestions.
-
Ran your example through on
noticed the grouping is incorrect, if it is on lccs/lcc/, you will get multiple lines of subtotal.
NaN could be null or empty string.
-
Hi thank you so much for this, it was a huge help. Part of my problem was the null values which Ii thought I had prevented by using <xsl:if test ="costs !=''">. This is no doubt not the way to prevent a null value. In any case, I cheated a bit and changed the underlying SQL view to replace the null values with 0 and I am just very happy that it is now working
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply