How do you calculate the sum of calculated values? 1

PPettit · Oct 23, 2013

As the title says: How do you calculate the sum of calculated values? Using the XML and XSLT below, I get a table like this:

Code:

Item | Quantity | Unit Cost | Extended Price
1    | 1        | $5.00     | $5.00 
2    | 2        | $10.00    | $20.00 
3    | 3        | $15.00    | $45.00 
Total:                         ?????

How would I total the "Extended Price" column to get a total of $70.00?

XML:

<?xml version="1.0" encoding="utf-8"?>
<?xml-stylesheet type="text/xsl" href="test.xslt"?>

<invoice>
  <item>
    <itemnumber>1</itemnumber>
    <quantity>1</quantity>
    <unitcost>5.00</unitcost>
  </item>
  <item>
    <itemnumber>2</itemnumber>
    <quantity>2</quantity>
    <unitcost>10.00</unitcost>
  </item>
  <item>
    <itemnumber>3</itemnumber>
    <quantity>3</quantity>
    <unitcost>15.00</unitcost>
  </item>
</invoice>

Code:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
  <xsl:template match="/">
    <style>
      table
      {
      border-collapse:collapse;
      }
      table,th,td
      {
      border:1px solid black;
      }
    </style>
    <html>
      <body>
        <table>
          <tr>
            <th>Item</th>
            <th>Quantity</th>
            <th>Unit Cost</th>
            <th>Extended Price</th>
          </tr>
          <xsl:for-each select="invoice/item">
          <tr>
            <td>
              <xsl:value-of select="itemnumber"/>
            </td>
            <td>
              <xsl:value-of select="quantity"/>
            </td>
              <td>
                <xsl:value-of select="format-number(unitcost,'$#,##0.00')"/>
              </td>
              <td>
                <xsl:value-of select="format-number(unitcost*quantity,'$#,##0.00')"/>
              </td>
          </tr>
          </xsl:for-each>
          <tr>
            <td colspan="3">Total:</td>
            <td>?????</td>
          </tr>
        </table>            
     </body>
    </html>
  </xsl:template>
</xsl:stylesheet>

k5tm · Oct 28, 2013

XSLT does not have a replacement operator, so one uses recursion rather than xsl:for-each to achieve the desired result. In your case, you wanted to have a grand total of the extended amount. Here is your stylesheet reconfigured to use recursion.

Code:

<xsl:stylesheet version="1.0" xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform">[/URL]
  <xsl:template match="/">
    <style>
      table
      {
      border-collapse:collapse;
      }
      table,th,td
      {
      border:1px solid black;
      }
    </style>
    <html>
      <body>
        <table>
          <tr>
            <th>Item</th>
            <th>Quantity</th>
            <th>Unit Cost</th>
            <th>Extended Price</th>
          </tr>
          <xsl:apply-templates select="invoice/item[1]"/>
        </table>            
     </body>
    </html>
  </xsl:template>

  <xsl:template match="item">
  	<xsl:param name="runningTotal" select="number('0')"/>
          <tr>
            <td>
              <xsl:value-of select="itemnumber"/>
            </td>
            <td>
              <xsl:value-of select="quantity"/>
            </td>
              <td>
                <xsl:value-of select="format-number(unitcost,'$#,##0.00')"/>
              </td>
              <td>
                <xsl:value-of select="format-number(unitcost*quantity,'$#,##0.00')"/>
              </td>
          </tr>
		  <xsl:choose>
		  <xsl:when test="following-sibling::item[1]">
		  		<xsl:apply-templates select="following-sibling::item[1]">
					<xsl:with-param name="runningTotal" select="$runningTotal + unitcost * quantity"/>
				</xsl:apply-templates>
		  </xsl:when>
		  <xsl:otherwise>
          <tr>
            <td colspan="3">Total:</td>
            <td><xsl:value-of select="format-number($runningTotal + unitcost * quantity,'$#,##0.00')"/></td>
          </tr>
		  </xsl:otherwise>
 		  </xsl:choose>
	</xsl:template>

</xsl:stylesheet>

If you are not familiar with recursive techniques there are several tutorials available on the web including this one on the IBM site, and please feel free to ask questions.

Tom Morrison
Hill Country Software

PPettit · Oct 29, 2013

Thanks for the reply. I definitely need to learn more about recursion and XPath.

It's kind of funny that I managed to find a solution a few hours before you posted yours, and it's pretty similar. (I found it at

http://stackoverflow.com/questions/436998/multiply-2-numbers-and-then-sum-with-xslt

in case anyone is interested.)

Code:

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns="[URL unfurl="true"]http://www.w3.org/1999/xhtml"[/URL] xmlns:xsl="[URL unfurl="true"]http://www.w3.org/1999/XSL/Transform"[/URL] xmlns:msxsl="urn:schemas-microsoft-com:xslt">
  <xsl:template match="/">
    <style>
      table {
        border-collapse:collapse;
        }
      table,th,td {
        border:1px solid black;
        }     
    </style>
        
    <html>
      <body>
        <table>
          <tr>
            <th>Item</th>
            <th>Quantity</th>
            <th>Unit Cost</th>
            <th>Extended Price</th>
          </tr>
          <xsl:for-each select="invoice/item">
          <tr>
            <td>
              <xsl:value-of select="itemnumber"/>
            </td>
            <td>
              <xsl:value-of select="quantity"/>
            </td>
            <td>
              <xsl:value-of select="format-number(unitcost,'$#,##0.00')"/>
            </td>
            <td>
              <xsl:value-of select="format-number(unitcost*quantity,'$#,##0.00')"/>
            </td>
          </tr>
          </xsl:for-each>
          <tr>
            <td colspan="3">Total:</td>
            <td>
              <xsl:call-template name="sumProducts">
                <xsl:with-param name="pList" select="*/*"/>
              </xsl:call-template>
            </td>
          </tr>
        </table>            
     </body>
    </html>
  </xsl:template>

  <xsl:template name="sumProducts">
    <xsl:param name="pList"/>
    <xsl:param name="pAccum" select="0"/>
    <xsl:choose>
      <xsl:when test="$pList">
        <xsl:variable name="vHead" select="$pList[1]"/>
        <xsl:call-template name="sumProducts">
          <xsl:with-param name="pList" select="$pList[position() > 1]"/>
          <xsl:with-param name="pAccum" select="$pAccum + $vHead//unitcost * $vHead//quantity"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="format-number($pAccum,'$#,##0.00')"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
  
</xsl:stylesheet>

k5tm · Oct 29, 2013

You can be almost absolutely certain that any solution offered by Dimitre Novatchev is correct. His work in FXSL is quite good and very useful.

Many solutions require the use of call-template. However, I tend to use template matching where I can. In the called template you show, the context node that is being processed is obscured. When you can use a matched template, the context node ('item' in my example) is explicit. So, when I look at your example and see the '//' XPath operator (which can skip multiple generations of nodes), I am given cause to wonder just what the context node might be.

I guess this may be personal preference. I have done a lot of XSL to process Open Office word processing documents (example here) and I find myself using XPath axes to navigate through the document in odd ways. So, doing tail recursion on the following-sibling axis has become somewhat normal to me...

[For some reason your 'name' seems familiar. Do you happen to be in Texas?]

Tom Morrison
Hill Country Software

PPettit · Oct 29, 2013

My bad.. I missed The "//" when I translated my code from my actual PIDX work to something that worked with the example that I gave originally.

I'm in the Midland/Odessa area. I see that HCSS is in Texas as well. Do you think we might have met at some point?

k5tm · Oct 29, 2013

I often find myself using recursive call-templates to compute a value for an xsl:variable, which I then use later in the template. Recursive call-template are very useful for processing strings as well as doing arithmetic.

And, speaking of arithmetic, unless you are quite sure about the validity of the numbers you are getting, you might want to guard your arithmetic operations with <xsl:if> (or multi-leg <xsl:choose>). The number() function will return the string "NaN" for data that cannot be converted to a number. In the example, I would probably compute the extended price of the item using variables to isolate my output from bad data, something like this (fair warning: typed - not tested):

Code:

<xsl:template match="item">
  	<xsl:param name="runningTotal" select="number('0')"/>
  	<xsl:variable name="myCost"><xsl:choose><xsl:when test="number(unitcost) = 'NaN'">0</xsl:when>
		<xsl:otherwise><xsl:value-of select="unitcost"/></xsl:otherwise></xsl:choose>
  	</xsl:variable>
  	<xsl:variable name="myQuantity"><xsl:choose><xsl:when test="number(quantity) = 'NaN'">0</xsl:when>
		<xsl:otherwise><xsl:value-of select="quantity"/></xsl:otherwise></xsl:choose>
  	</xsl:variable>

... then later ...
	<xsl:apply-templates select="following-sibling::item[1]">
		<xsl:with-param name="runningTotal" select="$runningTotal + $myCost * $myQuantity"/>
	</xsl:apply-templates>

This keeps one bad datum from causing your HTML from displaying "NaN" to your unsuspecting user.

Yep, in Texas, but work remotely from the HCSS office. I live near Austin. I have spent most of my career creating/selling program development software to other software developers. I had a look at the forums that in which you have posted (available in your profile) and there is not a whole lot of intersection with mine. So, probably not. (Odd how the mind works...my mind, at least!)

Tom Morrison
Hill Country Software

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How do you calculate the sum of calculated values? 1

PPettit

IS-IT--Management

k5tm

Programmer

PPettit

IS-IT--Management

k5tm

Programmer

PPettit

IS-IT--Management

k5tm

Programmer

Similar threads

Part and Inventory Search

Sponsor