Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

XSLT sum and xpath Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2014 9:31 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 12:03 AM
Points: 16, Visits: 135
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>
Post #1557794
Posted Thursday, April 3, 2014 6:09 AM This worked for the OP Answer marked as solution
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 3,504, Visits: 9,351
One small change needed

&lt;td&gt;&lt;xsl:value-of select="sum(../../*/*/costs)"  /&gt;&lt;/td&gt;

Post #1557932
Posted Thursday, April 3, 2014 5:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 12:03 AM
Points: 16, Visits: 135
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.
Post #1558315
Posted Thursday, April 3, 2014 10:44 PM This worked for the OP Answer marked as solution
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:29 PM
Points: 3,504, Visits: 9,351
Ran your example through on
http://xslttest.appspot.com/

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.

Post #1558362
Posted Friday, April 4, 2014 12:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 22, 2015 12:03 AM
Points: 16, Visits: 135
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
Post #1558373
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse