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: Friday, October 24, 2014 2:01 AM
Points: 16, Visits: 114
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 2,412, Visits: 6,684
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: Friday, October 24, 2014 2:01 AM
Points: 16, Visits: 114
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
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 1:54 PM
Points: 2,412, Visits: 6,684
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: Friday, October 24, 2014 2:01 AM
Points: 16, Visits: 114
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