Cart
|
[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message] Re: XSL: How to Calculate Running Total using Variabl
 Hi Bill,
Perhaps this link and the example code provided may help you sort out your
problem. You may need to use keys?
http://www.codeproject.com/soap/groupxml.asp?print=true
HtH,
/johnny :)
On 5/12/03 1:17 PM, "Ficke, Bill" <Bill.Ficke@xxxxxxxxxxxxxxxx> wrote:
> I suspect I'm going about this in the wrong way. Please help me. If some
> kind soul were to take special interest, I will gladly email an xml file to
> accompany the included xsl.
>
> I'm performing an outer for-loop for a report header and an inner for-loop
> to display report details. Within the inner loop, I'm calculating 4
> variables (HOURLY, LESSER, SPECIALTY, SAVINGS) which successively evaluate
> each other to get thier results, and eventually calcuate a GRANDTOTAL in
> each inner for-loop. (words in CAPS represent appropriate template names).
>
> Then, I need to calculate the BATCHTOTAL based on the GRANDTOTAL variables
> displayed in each inner for-loop.
>
> Essentially, it goes: <loop> - report header - <inner loop> - report details
> - </inner loop> - series of subtotals based on report details (HOURLY,
> LESSER, SPECIALTY, SAVINGS) - report total (GRANDTOTAL) </loop> - sum(report
> totals) to accomodate multiple reports (BATCHTOTAL).
>
> PROBLEM: I can't figure out how to calculate the BATCHTOTAL. I think it's
> because with each loop, the GRANDTOTAL variable goes out of scope.
>
> Thanks to anyone who will help.
>
> Bill
>
> Here is some xsl code to illustrate my efforts. The templates of interest
> include: HOURLY, LESSER, SPECIALTY, SAVINGS, BATCHTOTAL.
>
> <?xml version="1.0" encoding="UTF-8"?>
> <xsl:stylesheet version="1.0"
> xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
> xmlns:fo="http://www.w3.org/1999/XSL/Format"
> xmlns:s="uuid:BDC6E3F0-6DA3-11d1-A2A3-00AA00C14882"
> xmlns:dt="uuid:C2F41010-65B3-11d1-A29F-00AA00C14882"
> xmlns:rs="urn:schemas-microsoft-com:rowset" xmlns:z="#RowsetSchema">
>
> <!-- StyleSheet: CI200PreBillReport.xsl
> Bill Ficke 09/02/2003 -->
>
> <!-- Output is XHTML -->
> <xsl:output method="xml" omit-xml-declaration="yes" indent="no"
> encoding="ISO-8859-1"/>
> <!-- the Report Header of the output table -->
> <xsl:param name="report_header">Batch Detail Report</xsl:param>
> <!-- the ID of the output table -->
> <xsl:param name="table_id">ms_table</xsl:param>
> <!-- parm to change stylesheet reference -->
> <xsl:param
> name="style_sheet_ref">../../../StyleFiles/D1MasterCSS.css</xsl:param>
> <!-- style class assigned to the table -->
> <xsl:param name="tbl_class"/>
> <!-- style class assigned to the table header cells -->
> <xsl:param name="hdr_cell_class"/>
> <!-- style class assigned to the table cells -->
> <xsl:param name="cell_class"/>
> <!-- init the running total var -->
> <xsl:param name="running-total" select="0"/>
>
>
>
> <!-- match root -->
> <xsl:template match="/">
> <html>
> <head>
> <title></title>
> <link rel="stylesheet" type="text/css"
> href="{$style_sheet_ref}"></link>
> </head>
> <body>
> <xsl:for-each
> select="/xml/rs:data/z:row[@RB_ID !=
> 'B478EB94-F6D0-4133-A796-6718BF854B1C']">
> <xsl:call-template
> name="PreBillHeader"/>
> <xsl:call-template
> name="PreBillBody"/>
> </xsl:for-each>
>
> </body>
> </html>
> </xsl:template>
> <xsl:template name="PreBillHeader">
> <div id="cia_header">
> <table border="0" width="90%">
> <tbody>
> <tr>
> <td class="cia_reportname">
> <xsl:value-of
> select="$report_header"/>
> </td>
> </tr>
> <tr>
> <td class="cia_company">
> <xsl:value-of
> select="./@CO_NAME"/>
> </td>
> </tr>
> <tr>
> <td>
> <xsl:value-of
> select="./@CO_ADDRESS"/>
> </td>
> </tr>
> <tr>
> <td/>
> </tr>
> <tr>
> <td>
> <table border="0"
> width="100%">
> <tbody>
> <tr>
>
>
> <td>Batch:</td>
>
> <td class="cia_reportdata">
>
> <xsl:value-of select="./@RB_NAME"/>
>
> </td>
>
> <td>Batch Start Date:</td>
>
> <td class="cia_reportdata">
>
> <xsl:value-of select="translate(./@RB_START_DATE, 'T', ' ')"/>
>
> </td>
>
> </tr>
> <tr>
>
> <td></td>
>
> <td class="cia_reportdata">
>
>
>
> </td>
>
> <td>Batch End Date:</td>
>
> <td class="cia_reportdata">
>
> <xsl:value-of select="translate(./@RB_END_DATE, 'T', ' ')"/>
>
> </td>
>
> </tr>
> </tbody>
> </table>
> </td>
> </tr>
> </tbody>
> </table>
> </div>
> <br/>
> </xsl:template>
>
> <xsl:template name="PreBillBody">
> <div id="cia_header">
> <table border="0" width="90%">
> <tbody>
>
> <xsl:for-each select="./b_children/rr">
> <tr>
> <td>Pre-Billing
> Report:</td>
> <td colspan="2"
> class="cia_reportdata">
>
> <xsl:value-of select="./@RR_NAME"/>
> </td>
> <td>Job
> Address:</td>
> <td colspan="2"
> class="cia_reportdata">
>
> <xsl:value-of select="./@WR_ADDRESS"/>
> </td>
> </tr>
> <tr>
> <td
> class="cia_total">Bid Unit</td>
> <td
> class="cia_total">BU Type</td>
> <td
> class="cia_total"></td>
> <td
> class="cia_total" align="right">Price</td>
> <td
> class="cia_total" align="right">Qty</td>
> <td
> class="cia_total" align="right">Total $</td>
> </tr>
> <xsl:for-each select="./rr_children/rd">
> <tr class="cia_reportdata">
> <td>
> <xsl:value-of select="@RESOURCECODENAME"/>
> </td>
> <td>
> <xsl:value-of select="@BIDUNITNAME"/>
> </td>
> <td>
> <!--<xsl:value-of select="@UNITOFMEASURE"/>-->
> </td>
> <td align="right">
> <xsl:value-of select="format-number(@ACTUALCOST, '$#,##0.00')"/>
> </td>
> <td align="right">
> <xsl:value-of select="@QUANTITY"/>
> </td>
> <td align="right">
> <xsl:value-of select="format-number(@TOTALCOST, '$#,##0.00')"/>
> </td>
> </tr>
> </xsl:for-each>
>
> <xsl:variable name="Hourly">
> <xsl:call-template name="Hourly"></xsl:call-template>
> </xsl:variable>
>
> <xsl:variable name="Lesser">
> <xsl:call-template name="Lesser">
> <xsl:with-param name="Hourly" select="$Hourly"/>
> </xsl:call-template>
> </xsl:variable>
>
> <xsl:variable name="Savings">
> <xsl:call-template name="Savings">
> <xsl:with-param name="Hourly" select="$Hourly"/>
> </xsl:call-template>
> </xsl:variable>
>
> <xsl:variable name="Specialty">
> <xsl:call-template name="Specialty"></xsl:call-template>
> </xsl:variable>
>
> <xsl:variable name="GrandTotal">
> <xsl:call-template name="GrandTotal">
> <xsl:with-param name="Hourly" select="$Hourly"/>
> <xsl:with-param name="Lesser" select="$Lesser"/>
> <xsl:with-param name="Specialty"
> select="$Specialty"/>
> </xsl:call-template>
> </xsl:variable>
>
> <xsl:call-template name="PreBillTotal">
> <xsl:with-param name="Hourly" select="$Hourly"/>
> <xsl:with-param name="Lesser" select="$Lesser"/>
> <xsl:with-param name="Specialty" select="$Specialty"/>
> <xsl:with-param name="Savings" select="$Savings"/>
> <xsl:with-param name="GrandTotal" select="$GrandTotal"/>
> </xsl:call-template>
>
> </xsl:for-each>
>
> <xsl:variable name="BatchTotal">
> <xsl:call-template name="BatchTotal">
> <xsl:with-param name="list"
> select="/xml/rs:data/z:row/b_children"/>
> </xsl:call-template>
> </xsl:variable>
>
> </tbody>
> </table>
> </div>
>
> </xsl:template>
>
> <xsl:template name="Hourly">
> <xsl:value-of
> select="sum(./rr_children/rd[@BIDUNITNAME='Labor']/@TOTALCOST) +
> sum(./rr_children/rd[@BIDUNITNAME='Equipment']/@TOTALCOST)"/>
>
> </xsl:template>
>
> <xsl:template name="Lesser">
>
> <xsl:param name="Hourly"/>
> <xsl:choose>
> <!-- Hourly is 0, so use Unit -->
> <xsl:when test="$Hourly = 0">
> <xsl:value-of
> select="sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST)"/>
> </xsl:when>
>
> <!-- Unit is 0, so use Hourly -->
> <xsl:when
> test="sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST) = 0">
> <xsl:value-of select="$Hourly"/>
> </xsl:when>
>
> <!-- Hourly greater than Unit -->
> <xsl:when test="$Hourly >
> sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST)">
> <xsl:value-of
> select="sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST)"/>
> </xsl:when>
>
> <!-- Unit greater than Hourly -->
> <xsl:when
> test="sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST) > $Hourly">
> <xsl:value-of select="$Hourly"/>
> </xsl:when>
>
> <!-- Otherwise -->
> <xsl:otherwise>
> <xsl:value-of select="0"></xsl:value-of>
> </xsl:otherwise>
> </xsl:choose>
>
> </xsl:template>
>
> <xsl:template name="Savings">
>
> <xsl:param name="Hourly"/>
>
> <xsl:choose>
>
> <!-- Unit greater than Hourly and Payment Type is Hourly not
> to exceed Unit-->
> <xsl:when test="((./@RR_PAYMENT_TYPE = 2) and
> (sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST) > $Hourly))">
> <xsl:value-of
> select="sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST) - $Hourly"/>
> </xsl:when>
>
> <!-- Otherwise -->
> <xsl:otherwise>
> <xsl:value-of select="0"></xsl:value-of>
> </xsl:otherwise>
> </xsl:choose>
>
> </xsl:template>
>
>
> <xsl:template name="Specialty">
>
> <xsl:value-of
> select="sum(./rr_children/rd[@BIDUNITNAME='Specialty']/@TOTALCOST) +
> sum(./rr_children/rd[@BIDUNITNAME='Maintenance']/@TOTALCOST)"></xsl:value-of
>>
>
> </xsl:template>
>
> <xsl:template name="GrandTotal">
>
> <xsl:param name="Hourly"/>
> <xsl:param name="Lesser"/>
> <xsl:param name="Specialty"/>
>
> <xsl:choose>
>
> <xsl:when test="./@RR_PAYMENT_TYPE = 3">
> <xsl:value-of
> select="$Specialty"></xsl:value-of>
> </xsl:when>
>
> <xsl:when test="./@RR_PAYMENT_TYPE = 2">
> <xsl:value-of select="$Lesser +
> $Specialty"></xsl:value-of>
> </xsl:when>
>
> <xsl:when test="./@RR_PAYMENT_TYPE = 1">
> <xsl:value-of
> select="sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST) +
> $Specialty"></xsl:value-of>
> </xsl:when>
>
> <xsl:when test="./@RR_PAYMENT_TYPE = 0">
> <xsl:value-of select="$Hourly +
> $Specialty"></xsl:value-of>
> </xsl:when>
>
> </xsl:choose>
>
> </xsl:template>
>
> <xsl:template name="PreBillTotal">
>
> <xsl:param name="Hourly"/>
> <xsl:param name="Lesser"/>
> <xsl:param name="Specialty"/>
> <xsl:param name="Savings"/>
> <xsl:param name="GrandTotal"/>
>
> <tr class="cia_company">
> <td colspan="6" class="cia_total"></td>
> </tr>
>
> <tr class="cia_reportdata">
> <td colspan="2"></td>
> <td colspan="3">Hourly SubTotal</td>
> <td align="right"><xsl:value-of
> select="format-number($Hourly, '$#,##0.00')"/></td>
> </tr>
>
> <tr class="cia_reportdata">
> <td colspan="2"></td>
> <td colspan="3">Units SubTotal</td>
> <td align="right"><xsl:value-of
> select="format-number(sum(./rr_children/rd[@BIDUNITNAME='Unit']/@TOTALCOST),
> '$#,##0.00')"/></td>
> </tr>
>
> <tr class="cia_reportdata">
> <td colspan="6"> </td>
> </tr>
>
> <tr class="cia_reportdata">
> <td colspan="2"></td>
> <td colspan="3">SubTotal</td>
> <td align="right"><xsl:value-of
> select="format-number($Lesser, '$#,##0.00')"></xsl:value-of></td>
> </tr>
>
> <tr class="cia_reportdata">
> <td colspan="2"></td>
> <td colspan="3">Specialty Total</td>
> <td align="right"><xsl:value-of
> select="format-number($Specialty, '$#,##0.00')"/></td>
> </tr>
>
> <tr class="cia_reportdata">
> <td colspan="2"></td>
> <td colspan="3" class="cia_subtotal">Report Total</td>
> <td align="right" class="cia_subtotal"><xsl:value-of
> select="format-number($GrandTotal, '$#,##0.00')"></xsl:value-of></td>
> </tr>
>
> <tr class="cia_reportdata">
> <td colspan="2"></td>
> <td colspan="3" class="cia_subtotal">Savings</td>
> <td align="right" class="cia_subtotal"><xsl:value-of
> select="format-number($Savings, '$#,##0.00')"></xsl:value-of></td>
> </tr>
>
> </xsl:template>
>
>
> <xsl:template name="BatchTotal">
>
> <xsl:param name="running-total"/>
>
> <xsl:variable name="Hourly">
> <xsl:call-template name="Hourly"></xsl:call-template>
> </xsl:variable>
>
> <xsl:variable name="Lesser">
> <xsl:call-template name="Lesser">
> <xsl:with-param name="Hourly" select="$Hourly"/>
> </xsl:call-template>
> </xsl:variable>
>
> <xsl:variable name="Savings">
> <xsl:call-template name="Savings">
> <xsl:with-param name="Hourly" select="$Hourly"/>
> </xsl:call-template>
> </xsl:variable>
>
> <xsl:variable name="Specialty">
> <xsl:call-template name="Specialty"></xsl:call-template>
> </xsl:variable>
>
> <xsl:variable name="GrandTotal">
> <xsl:call-template name="GrandTotal">
> <xsl:with-param name="Hourly" select="$Hourly"/>
> <xsl:with-param name="Lesser" select="$Lesser"/>
> <xsl:with-param name="Specialty"
> select="$Specialty"/>
> </xsl:call-template>
> </xsl:variable>
>
>
>
>
> <tr class="cia_company">
> <td colspan="6" class="cia_total">Batch Total</td>
> </tr>
> <tr class="cia_reportdata">
> <td colspan="2"></td>
> <td colspan="3" class="cia_subtotal">Batch
> Total</td>
> <td class="cia_subtotal">
> <xsl:value-of
> select="format-number($running-total, '$#,##0.00')"/>
> </td>
> </tr>
>
> </xsl:template>
>
> </xsl:stylesheet>
>
> XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
>
>
>
--
If you place a small value on your self, the rest of the world will not
raise the price.
XSL-List info and archive: http://www.mulberrytech.com/xsl/xsl-list
|
PURCHASE STYLUS STUDIO ONLINE TODAY!Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced! 
Download The World's Best XML IDE!Accelerate XML development with our award-winning XML IDE - Download a free trial today! Subscribe in XML format
|
