Loading ...
Sorry, an error occurred while loading the content.

XSL SubTotal & GrandTotal Issue

Expand Messages
  • Joe Schmaltz
    I am having trouble using a for-each statement to loop through my xml data in order to get a subtotal and grandtotal. It seems to be looping the subtotal line
    Message 1 of 1 , Aug 4, 2003
    • 0 Attachment
      I am having trouble using a for-each statement to loop through my
      xml data in order to get a subtotal and grandtotal. It seems to be
      looping the subtotal line for each transaction of the ledger. I'm
      also having difficuly looping through each Resident and ledger
      transactions at the same time. Here is my incoming xml:

      <Property>
      <strPropertyName>Test Property</strPropertyName>
      <lngPropertyID>1</lngPropertyID>
      <Resident>
      <strFirstLast>Smith, Bob</strFirstLast>
      <lngPersonalID>299</lngPersonalID>
      <Ledger>
      <lngLedgerId>1</lngLedgerId>

      <datTransaction>03/26/2003</datTransaction>

      <decPaymentAmount>300.00</decPaymentAmount>
      <strMemo>Deposit</strMemo>
      <decBalance>0.00</decBalance>
      </Ledger>
      <Ledger>

      <decChargeAmount>1437.00</decChargeAmount>

      <datTransaction>03/26/2003</datTransaction>
      <strMemo>Test</strMemo>
      <decBalance>1437.00</decBalance>
      </Ledger>
      </Resident>
      </Property>


      HERE IS MY XSL:

      <?xml version='1.0'?>
      <xsl:stylesheet version="1.0"
      xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
      <xsl:key name="kDistinctLedger" match="Ledger" use="lngLedgerID"/>

      <xsl:template match="/">
      <p align="left">
      <link rel="stylesheet" type="text/css" href="CSS/wakul2.css" />

      <table class="ReportPortrait" cellspacing="0" cellpadding="0">
      <tr>
      <td width="20%"><img src="Images/logo.jpg" border="0"></img></td>
      <td class="ReportTitle" align="center">Resident Ledger</td>
      <td width="20%"></td>
      </tr>
      <tr>
      <td class="ReportDescription" align="center" colspan="3">Detailed
      Listing Of Resident Charges And Payments Per Resident.
      </td>
      </tr>
      <tr>
      <td class="ReportSubTitle" colspan="3" align="center">Property:
      <xsl:value-of select="//Property/strPropertyName"/>
      </td>
      </tr>
      </table>

      <table class="ReportPortrait" cellspacing="10" cellpadding="0">
      <tr><td></td></tr>
      <tr><td class="ReportCellGroup" colspan="5"><xsl:value-of
      select="//Resident/strFirstLast"/></td></tr>
      <tr align="center">
      <td class="ReportCol">Transaction Date</td>
      <td class="ReportCol">Memo</td>
      <td class="ReportCol">Charge Amount</td>
      <td class="ReportCol">Payment Amount</td>
      <td class="ReportCol">Balance</td>
      </tr>
      <xsl:for-each select="//Resident/Ledger[generate-id()=generate-id(key
      ('kDistinctLedger',lngLedgerID))]">
      <xsl:for-each select="key('kDistinctLedger',lngLedgerID)">
      <tr>
      <td class="ReportCell"><xsl:value-of select="datTransaction"/></td>
      <td class="ReportCell"><xsl:value-of select="strMemo"/></td>
      <td class="ReportCell" align="right" width="10%"><xsl:value-of
      select="format-number(decChargeAmount,'$#,##0.00')"/></td>
      <td class="ReportCell" align="right" width="10%"><xsl:value-of
      select="format-number(decPaymentAmount,'$#,##0.00')"/></td>
      <td class="ReportCell" align="right" width="10%"><xsl:value-of
      select="format-number(decBalance,'$#,##0.00')"/></td>
      </tr>
      </xsl:for-each>
      <tr>
      <td class="ReportGrandTotal" colspan="2">SubTotal:</td>
      <td align="right" class="ReportGrandTotal"><xsl:value-of
      select="format-number(sum
      (//Resident/Ledger/decChargeAmount),'$0.00')"/></td>
      <td align="right" class="ReportGrandTotal"><xsl:value-of
      select="format-number(sum
      (//Resident/Ledger/decPaymentAmount),'$0.00')"/></td>
      <td align="right" class="ReportGrandTotal"></td>
      </tr>
      </xsl:for-each>
      <tr>
      <td class="ReportGrandTotal" colspan="2">Grand Total:</td>
      <td align="right" class="ReportGrandTotal"><xsl:value-of
      select="format-number(sum
      (//Property/Resident/Ledger/decChargeAmount),'$0.00')"/></td>
      <td align="right" class="ReportGrandTotal"><xsl:value-of
      select="format-number(sum
      (//Property/Resident/Ledger/decPaymentAmount),'$0.00')"/></td>
      <td align="right" class="ReportGrandTotal"></td>
      </tr>
      </table>
      </p>
      </xsl:template>
      </xsl:stylesheet>


      MY FINAL RESULT SHOULD BE THIS:

      Resident, Name
      -----------------------------------------------------------------
      | TransDate | Memo | Charge Amt | Payment Amt | Bal |
      -----------------------------------------------------------------
      | 07/17/2003 | test | 10.00 | 10.00 | 20.00 |
      -----------------------------------------------------------------
      | 07/18/2003 | test | 20.00 | 20.00 | 40.00
      -----------------------------------------------------------------
      | Subtotal | 30.00 | 30.00 |
      -----------------------------------------------------------------

      Resident, Name
      -----------------------------------------------------------------
      | TransDate | Memo | Charge Amt | Payment Amt | Bal |
      -----------------------------------------------------------------
      | 07/22/2003 | test | 30.00 | 20.00 | 50.00 |
      -----------------------------------------------------------------
      | 07/28/2003 | test | 10.00 | 60.00 | 70.00
      -----------------------------------------------------------------
      | Subtotal | 40.00 | 80.00 |
      -----------------------------------------------------------------


      -----------------------------------------------------------------
      | GrandTotal | 70.00 | 110.00 |
      -----------------------------------------------------------------
    Your message has been successfully submitted and would be delivered to recipients shortly.