XSLT Sum function

John Moss · May 7th, 2014, 04:48 PM

I'm trying to use the XSLT Sum function to total all the items within a specific node. This is a section of the XML file:

Code:

<Results>
<Recordset">
<Record>
<check-date>04/01/2014</check-date>
<title>title of Item</title>
<fund-acct>01.504.0100-05</fund-acct>
<subtotal>$3,564.00</subtotal>
<Pre-assessed-total>$3,693.02</Pre-assessed-total>
</Record>

I need to add all the <Pre-assessed-total> for each occurrance of <fund-acct> that are the same. I tried using this,

Code:

<xsl:sequence select="sum(/Results/Recordset/Record/(Pre-assessed-total ?????))"/>

but not sure how to make it work. I'm not even sure if this can do it?

Thanks for any tips or suggestions.

John

mhkay · May 7th, 2014, 04:56 PM

Your use of xsl:sequence suggests you're using XSLT 2.0, so you can do

Code:

sum(/Results/Recordset/Record/translate(Pre-assessed-total, '$', '')))

John Moss · May 8th, 2014, 12:41 PM

Thanks Michael. I still have a lot to learn about this stuff.

How do you determine what version is in use? By the DTD?

Does XML/XSLT require a server?

mhkay · May 8th, 2014, 01:03 PM

You determine what version of XSLT you are using the same as you would determine what version of Java you are using: you're expected to know what processor you are invoking, and you consult the documentation for that processor.

In general however, if you don't know what processor you are invoking that's probably because it's bundled with something like the Java platform or the .NET platform or the browser, and if the XSLT processor is bundled in that way then it's usually an XSLT 1.0 processor only; if you want XSLT 2.0, you usually have to install it specially. There are exceptions, for example some XML IDEs have Saxon bundled, which supports XSLT 2.0.

John Moss · May 8th, 2014, 01:23 PM

I'm not using XML on a server, so I'm not sure if the XSLT Sum function is going to work.

All I have is a XML and XSLT file in Notepadd++. I put both files in the same folder on a shared network drive. I can display the XML, but the calculation is not working. Here's my XSLT:

Code:

<?xml version="1.0" encoding="UTF-8"?>
 <xsl:stylesheet version="1.0"
 xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

 <xsl:template match="/">
    <xsl:text>Grand Total: </xsl:text>
		<xsl:value-of select="sum(row/col[@name = 'price' and node()/self::text() != 'null'])"/>

  <html>
   <body>
   <h2>Library Budget Report</h2>
   <table border="1">
     <tr bgcolor="#9acd32">
       <th>check-date</th>
       <th>title</th>
	   <th>fund-acct</th>
       <th>subtotal</th>
	   <th>Pre-assessed-total</th>
     </tr>
    <xsl:for-each select="Results/Recordset/Record">
	   <xsl:sort select="fund-acct"/>
	  <tr>
       <td><xsl:value-of select="check-date"/></td>
       <td><xsl:value-of select="title"/></td>
	   <td><xsl:value-of select="fund-acct"/></td>
       <td><xsl:value-of select="subtotal"/></td>
	   <td><xsl:value-of select="Pre-assessed-total"/></td>
     </tr>
    </xsl:for-each>
	</table>
   </body>
   </html>
 </xsl:template>

 </xsl:stylesheet>

What server works for what I need? Apache? IIS?

mhkay · May 8th, 2014, 01:54 PM

Notepad++ implements XSLT 1.0. That's not the reason it's not working for you, since your code only uses XSLT 1.0. Can't help you debug it without seeing your XML source document.

You can run XSLT anywhere: in the browser, on a desktop, on a server. The version of XSLT depends on what software you install on the server (or on your desktop). You can install half a dozen different XSLT processors on the same machine if you want to.

John Moss · May 8th, 2014, 02:39 PM

Thanks for sticking with me on this.

Here's part of the XML file.

Code:

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<?xml-stylesheet type="text/xsl" href="budget.xsl"?>
<Results productTitle="Inmagic DB/TextWorks for SQL" productVersion="14.00" xmlns:inm="http://www.inmagic.com/webpublisher/query">
<Recordset setCount="35">
<Record setEntry="0">
<check-date>04/01/2014</check-date>
<title>Almanac of the federal judiciary online 4/14-3/15</title>
<fund-acct>01.504.0100-05</fund-acct>
<subtotal>$3,564.00</subtotal>
<Pre-assessed-total>$3,693.02</Pre-assessed-total>
</Record>
<Record setEntry="1">
<check-date>04/01/2014</check-date>
<title>Estate planning and taxation coordinator 5/2014- 5/2015</title>
<fund-acct>01.504.0100-5</fund-acct>
<subtotal>$2,020.00</subtotal>
<Pre-assessed-total>$2,245.86</Pre-assessed-total>
</Record>

I need to sum all the <Pre-assessed-total> for each <fund-acct>.

John Moss · May 14th, 2014, 12:04 PM

Thanks for sticking with me on this. Here's a sample of the XML file:

Code:

<?xml version="1.0" encoding="ISO-8859-1" standalone="yes"?>
<?xml-stylesheet type="text/xsl" href="budget.xsl"?>
<Results productTitle="Inmagic DB/TextWorks for SQL" productVersion="14.00" xmlns:inm="http://www.inmagic.com/webpublisher/query">
<Recordset setCount="35">
<Record setEntry="0">
<check-date>04/01/2014</check-date>
<title>Almanac of the federal judiciary online 4/14-3/15</title>
<fund-acct>01.504.0100-05</fund-acct>
<subtotal>$3,564.00</subtotal>
<Pre-assessed-total>$3,693.02</Pre-assessed-total>
</Record>
<Record setEntry="1">
<check-date>04/01/2014</check-date>
<title>Estate planning and taxation coordinator 5/2014- 5/2015</title>
<fund-acct>01.504.0100-5</fund-acct>
<subtotal>$2,020.00</subtotal>
<Pre-assessed-total>$2,245.86</Pre-assessed-total>
</Record>

I need to get a total of all <Pre-assessed-total> for each <fund-acct>. I tried your suggestion of using

Code:

sum(/Results/Recordset/Record/translate(Pre-assessed-total, '$', '')))

but that did not work.

Thanks for any tips or suggestions,
John

John Moss · May 23rd, 2014, 10:48 AM

My last 2 posts were caught in the forums' spam filter, released by the forum admin, and now appear, but with no notification sent out.

I have received MKay's book, XSLT 2.0 and XPath 2.0, but have yet to read through it (that may take a while

), so I still trying to resolve this problem.

Thanks for any assistance.

mhkay · May 23rd, 2014, 11:25 AM

Don't tell us that something didn't work, tell us how it failed. Most of us find it much easier to see what's wrong with a piece of code if we have some diagnostics.

The obvious problem is that the result of translate is a string, it needs to be converted to a number e.g. by using the number() function.