CALCULATE SUM OF TOTALS

pallone · November 19th, 2009, 09:49 AM

Hi,

I am using XSLT 1.0 and MSXML

I am trying to create a table that not only shows calculated values like Order Totals

(number of items * unit price) but also a sum of all the totals for that order.

Basically, I need to insert a row to each table after the last row has been calculate showing the sum of the order totals and number of items.

Joe Baz

Order Date Delivery Date Salesperson Order Total Number of Items
10/08/2000 10/08/2000 Doe, Jon 61.64 2
10/26/2000 10/27/2000 Doe, Jon 2.31 1
--------------------------------------------------------------------------------------------------------------
Totals 63.95 3

Foo Baz

Order Date Delivery Date Salesperson Order Total Number of Items
10/08/2000 10/10/2000 Doe, Jon 46.06 1

----------------------------------------------------------------------------------------------------
Totals 46.06 1

In my Order template I am trying to find out if my position is the last one and then call a template to insert the last row with the summation.

1 - However, I cannot figure out how to get the position() function to tell me if I am in the last Order row every Row that is the last child of its parent. I have tried this but to no avail



<xsl:if test ="parent::Customer/Order[position() = last()]">

2 - Also, I am not sure if I have to pass the whole Customer node to the called template to be able to calculate the summation??

<xsl:call-template name="InsertRowCalculateOrderTotals">
<xsl:with-param name="CustomerNode" select="parent::Customer"/>
</xsl:call-template>

I would appreciate if someone could help to solve this problem.

Cheers

C

XSLT
---------

<xsl:template match="Order">
<tr>
<td>
value here...
</td>
<td>
value here...
</td>
<td>
value here..
</td>
<td align="right">
value here..
</td>
<td align="right">
<xsl:value-of select="count(OrderDetails/Item)"/>
</td>
</tr>



<xsl:if test ="parent::Customer/Order[position() = last()]">
<xsl:call-template name="InsertRowCalculateOrderTotals">
<xsl:with-param name="CustomerNode" select="parent::Customer"/>
</xsl:call-template>

</xsl:if>

</xsl:template>

XML
-------

<?xml version="1.0"?>
<Root>
<Customer CustomerID="1">
<LastName>Baz</LastName>
<FirstName>Joe</FirstName>
<Order OrderID="1">
<Salesperson>Doe, Jon</Salesperson>
<OrderDate>2000/10/08</OrderDate>
<DeliveryDate>2000/10/08</DeliveryDate>
<OrderDetails>
<Item ItemID="1">
<ItemDescription>Large Coke</ItemDescription>
<SalePrice>0.8663</SalePrice>
<Quantity>3</Quantity>
</Item>
<Item ItemID="2">
<ItemDescription>Large Pepperoni Pizza</ItemDescription>
<SalePrice>14.7609</SalePrice>
<Quantity>4</Quantity>
</Item>
</OrderDetails>
</Order>
<Order OrderID="2">
<Salesperson>Doe, Jon</Salesperson>
<OrderDate>2000/10/26</OrderDate>
<DeliveryDate>2000/10/27</DeliveryDate>
<OrderDetails>
<Item ItemID="1">
<ItemDescription>Large Coke</ItemDescription>
<SalePrice>0.5775</SalePrice>
<Quantity>4</Quantity>
</Item>
</OrderDetails>
</Order>
</Customer>
<Customer CustomerID="2">
<LastName>Baz</LastName>
<FirstName>Foo</FirstName>
<Order OrderID="5">
<Salesperson>Doe, Jon</Salesperson>
<OrderDate>2000/10/08</OrderDate>
<DeliveryDate>2000/10/10</DeliveryDate>
<OrderDetails>
<Item ItemID="1">
<ItemDescription>Large Cheese Pizza</ItemDescription>
<SalePrice>11.5153</SalePrice>
<Quantity>4</Quantity>
</Item>
</OrderDetails>
</Order>
</Customer>
</Root>

Martin Honnen · November 19th, 2009, 10:08 AM

It sounds as if you want to create an HTML document with your stylesheet. Can you please post the HTML you want to create for the XML input sample you posted?

pallone · November 19th, 2009, 10:25 AM

Hi Martin,

Thanks for your reply. I am struggling to get the desired output because I have to compute the Order Totals for each order (number of items * unit price) but also a summation of all the totals for all the order that belong to that customer in the last row of the table.

This is what the HTML should look like:

<html>
<head>
<title>Orders by Date</title>
</head>
<body>
<h1>Orders by Date</h1>
<h2>Joe Baz</h2>
<table border="2">
<tr>
<th>Order Date</th>
<th>Delivery Date</th>
<th>Salesperson</th>
<th>Order Total</th>
<th>Number of Items</th>
</tr>
<tr>
<td>10/08/2000</td>
<td>10/08/2000</td>
<td>Doe, Jon</td>
<td align="right">61.64</td>
<td align="right">2</td>
</tr>
<tr>
<td>10/26/2000</td>
<td>10/27/2000</td>
<td>Doe, Jon</td>
<td align="right">2.31</td>
<td align="right">1</td>
</tr>
<tr>
<th>Totals</th>
<td> </td>
<td> </td>
<td align="right">63.95</td>
<td align="right">3</td>
</tr>
</table>
<h2>Foo Baz</h2>
<table border="2">
<tr>
<th>Order Date</th>
<th>Delivery Date</th>
<th>Salesperson</th>
<th>Order Total</th>
<th>Number of Items</th>
</tr>
<tr>
<td>10/08/2000</td>
<td>10/10/2000</td>
<td>Doe, Jon</td>
<td align="right">46.06</td>
<td align="right">1</td>
</tr>
<tr>
<th>Totals</th>
<td> </td>
<td> </td>
<td align="right">46.06</td>
<td align="right">1</td>
</tr>
</table>
</body>
</html>

Cheers

C

Martin Honnen · November 19th, 2009, 10:58 AM

You could use a result tree fragment variable, convert it to a node-set with the extension function that MSXML supports and then it is easy to compute the sum:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:ms="urn:schemas-microsoft-com:xslt"
  exclude-result-prefixes="ms"
  version="1.0">
  
  <xsl:output method="html" indent="yes"/>
  
  <xsl:template match="/">
    <html>
      <head>
        <title>Orders by Date</title>
      </head>
      <body>
        <h1>Orders by Date</h1> 
        <xsl:apply-templates select="Root/Customer"/>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="Customer">
    <h2><xsl:value-of select="concat(FirstName, ' ', LastName)"/></h2>
    <table border="2">
      <thead>
        <tr>
          <th>Order Date</th>
          <th>Delivery Date</th>
          <th>Salesperson</th>
          <th>Order Total</th>
          <th>Number of Items</th>
        </tr>
      </thead>
      <xsl:variable name="orders-rtf">
        <xsl:for-each select="Order">
          <tr>
            <td><xsl:value-of select="OrderDate"/></td>
            <td><xsl:value-of select="DeliveryDate"/></td>
            <td><xsl:value-of select="Salesperson"/></td>
            <td align="right">
              <xsl:call-template name="compute-total">
                <xsl:with-param name="items" select="OrderDetails/Item"/>
              </xsl:call-template>
            </td>
            <td align="right"><xsl:value-of select="count(OrderDetails/Item)"/></td>
          </tr>
        </xsl:for-each>
      </xsl:variable>
      <tbody>
        <xsl:copy-of select="$orders-rtf"/>
        <xsl:variable name="orders" select="ms:node-set($orders-rtf)/tr"/>
        <tr>
         <th>Totals</th>
         <td> </td>
         <td> </td>
         <td align="right"><xsl:value-of select="sum($orders/td[4])"/></td>
         <td align="right"><xsl:value-of select="sum($orders/td[5])"/></td> 
        </tr>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template name="compute-total">
    <xsl:param name="items"/>
    <xsl:param name="total" select="0"/>
    <xsl:choose>
      <xsl:when test="$items">
        <xsl:call-template name="compute-total">
          <xsl:with-param name="items" select="$items[position() &gt; 1]"/>
          <xsl:with-param name="total" select="$total + $items[1]/SalePrice * $items[1]/Quantity"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise><xsl:value-of select="$total"/></xsl:otherwise>
    </xsl:choose>
  </xsl:template>

</xsl:stylesheet>

pallone · November 19th, 2009, 12:01 PM

Martin,

Many thanks to your reply.

I have never used extensions before. I think the reason for this is that I am transforming the XML using XSLT client side. Therefore, the Microsoft extension will only work for IE but I also need it to work in other browsers like Firefox etc...

1 - Is there another way we can achieve that?

To compute the Order Totals for each order (number of items * unit price) I was using a named template and recurssion like the code below.

2 - Could we do something similar to that to calculate the last row totals

<td align="right">
<xsl:call-template name="CalcualteOrderTotal">
<xsl:with-param name="OrderDetailItems" select="OrderDetails/Item"/>
</xsl:call-template>
</td>

<xsl:template name="CalcualteOrderTotal">
<xsl:param name="OrderDetailItems"/>
<xsl:choose>
<xsl:when test="$OrderDetailItems">
<xsl:variable name="First" select="$OrderDetailItems[1]"/>
<xsl:variable name="valueOfRest">
<xsl:call-template name="CalcualteOrderTotal">
<xsl:with-param name="OrderDetailItems"
select="$OrderDetailItems[position()!=1]"/>
</xsl:call-template>
</xsl:variable>
<xsl:value-of
select="format-number($First/SalePrice *
$First/Quantity + $valueOfRest,'#.00')"/>
</xsl:when>
<xsl:otherwise>
0
</xsl:otherwise>
</xsl:choose>

</xsl:template>

3 - Could you please explain what this code is doing? Why do you use copy-of? It will select the whole xml rtf and add to result, wont it?

<tbody>
<xsl:copy-of select="$orders-rtf"/>
<xsl:variable name="orders" select="ms:node-set($orders-rtf)/tr"/>

Martin Honnen · November 19th, 2009, 12:25 PM

Firefox (since version 3.0 I think) and other browsers like Safari and Opera do support exsl:node-set so the stylesheet can easily be adapted:

Code:

<xsl:stylesheet
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
  xmlns:ms="urn:schemas-microsoft-com:xslt"
  xmlns:exsl="http://exslt.org/common"
  exclude-result-prefixes="ms exsl"
  version="1.0">
  
  <xsl:output method="html" indent="yes"/>
  
  <xsl:template match="/">
    <html>
      <head>
        <title>Orders by Date</title>
      </head>
      <body>
        <h1>Orders by Date</h1> 
        <xsl:apply-templates select="Root/Customer"/>
      </body>
    </html>
  </xsl:template>
  
  <xsl:template match="Customer">
    <h2><xsl:value-of select="concat(FirstName, ' ', LastName)"/></h2>
    <table border="2">
      <thead>
        <tr>
          <th>Order Date</th>
          <th>Delivery Date</th>
          <th>Salesperson</th>
          <th>Order Total</th>
          <th>Number of Items</th>
        </tr>
      </thead>
      <xsl:variable name="orders-rtf">
        <xsl:for-each select="Order">
          <tr>
            <td><xsl:value-of select="OrderDate"/></td>
            <td><xsl:value-of select="DeliveryDate"/></td>
            <td><xsl:value-of select="Salesperson"/></td>
            <td align="right">
              <xsl:call-template name="compute-total">
                <xsl:with-param name="items" select="OrderDetails/Item"/>
              </xsl:call-template>
            </td>
            <td align="right"><xsl:value-of select="count(OrderDetails/Item)"/></td>
          </tr>
        </xsl:for-each>
      </xsl:variable>
      <tbody>
        <xsl:copy-of select="$orders-rtf"/>
        <xsl:choose>
          <xsl:when test="function-available('exsl:node-set')">
            <xsl:call-template name="make-total">
              <xsl:with-param name="orders" select="exsl:node-set($orders-rtf)/tr"/>
            </xsl:call-template>
          </xsl:when>
          <xsl:when test="function-available('ms:node-set')">
            <xsl:call-template name="make-total">
              <xsl:with-param name="orders" select="ms:node-set($orders-rtf)/tr"/>
            </xsl:call-template>
          </xsl:when>
        </xsl:choose>
      </tbody>
    </table>
  </xsl:template>
  
  <xsl:template name="compute-total">
    <xsl:param name="items"/>
    <xsl:param name="total" select="0"/>
    <xsl:choose>
      <xsl:when test="$items">
        <xsl:call-template name="compute-total">
          <xsl:with-param name="items" select="$items[position() &gt; 1]"/>
          <xsl:with-param name="total" select="$total + $items[1]/SalePrice * $items[1]/Quantity"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise><xsl:value-of select="$total"/></xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <xsl:template name="make-total">
    <xsl:param name="orders"/>
    <tr>
      <th>Totals</th>
      <td> </td>
      <td> </td>
      <td align="right"><xsl:value-of select="sum($orders/td[4])"/></td>
      <td align="right"><xsl:value-of select="sum($orders/td[5])"/></td> 
    </tr>
  </xsl:template>
  
</xsl:stylesheet>

As for explaining the copy-of select="$orders-rtf", yes, that copies the result tree fragment stored in the variable 'orders-rtf' to the result tree. I did that as your sample HTML you presented on my request contains those 'tr' elements with the order details. If you only want the total then remove that copy-of instruction.

As for doing it without a result tree fragment and a node-set extension function, I might give it a try later.

pallone · November 19th, 2009, 12:46 PM

Hi Martin,

Your code is really fantastic.

I have debugged it in Visual Studio 2008 and now can understand what goes on.

The way you created the RTF dynamically was very clever

The only problem I have is that I transform the XML on the client.

Any suggestions???

pallone · November 19th, 2009, 12:48 PM

Sorry,

I missed your previous code. I will have a look at that.

pallone · November 19th, 2009, 12:55 PM

Martin,

Using an extension makes things a lot easier. thanks a lot for that tip.

I have debugged your second version of the code and I found something really interesting:

1 - Even though I set VS 2008 to use IE as the default broweser, the code is going into the 'exsl:node-set' extension. That is strange, isn't it?

<xsl:when test="function-available('exsl:node-set')">
<xsl:call-template name="make-total">
<xsl:with-param name="orders" select="exsl:node-set($orders-rtf)/tr"/>
</xsl:call-template>
</xsl:when>

2 - So why can't we foreach a RTF?

Cheers

C

Martin Honnen · November 19th, 2009, 01:07 PM

As far as I know if you debug XSLT in Visual Studio then it debugs the XSLT run with .NET's XslCompiledTransform. As that supports the exsl:node-set function the code uses that branch. IE uses MSXML and that does not support exsl:node-set.

As for the restrictions of result tree fragments, the XSLT 1.0 specification unfortunately imposes the restriction that you can only use xsl:value-of and xsl:copy-of on them but can't use them in path expressions.

The distinction between input trees and result tree fragment having a different data model is completely gone in XSLT 2.0 so there you can easily create a temporary tree (fragment) in a variable and process that further with the for-each or apply-templates but as long as you live in the XSLT 1.0 world you need to live with the distinction and use an extension function.