Selecting Nodes Using Subtotal of Child Nodes

Discussion in 'XML' started by renfrochris@hotmail.com, Aug 23, 2006.

  1. Guest

    I'm having difficulty finding the correct syntax that will allow me to
    select a group of invoices based on the total of an amount column
    located in its line items. Below are simplified examples of my XML and
    XSLT files:

    XML FILE

    <?xml version="1.0" standalone="yes"?>
    <?xml-stylesheet type="text/xsl"
    href="OutstandingInvoiceBalances.xslt"?>
    <ProgramData>
    <Invoices>
    <InvoiceID>1</InvoiceID>
    <InvoiceNumber>100</InvoiceNumber>
    <Amount>1000.00</Amount>
    </Invoices>
    <Invoices>
    <InvoiceID>2</InvoiceID>
    <InvoiceNumber>101</InvoiceNumber>
    <Amount>2000.00</Amount>
    </Invoices>
    <Invoices>
    <InvoiceID>3</InvoiceID>
    <InvoiceNumber>102</InvoiceNumber>
    <Amount>3000.00</Amount>
    </Invoices>
    <InvoiceLineItems>
    <InvoiceLineItemID>1</InvoiceLineItemID>
    <InvoiceID>1</InvoiceID>
    <AmountToPay>0</AmountToPay>
    </InvoiceLineItems>
    <InvoiceLineItems>
    <InvoiceLineItemID>2</InvoiceLineItemID>
    <InvoiceID>2</InvoiceID>
    <AmountToPay>100</AmountToPay>
    </InvoiceLineItems>
    <InvoiceLineItems>
    <InvoiceLineItemID>3</InvoiceLineItemID>
    <InvoiceID>2</InvoiceID>
    <AmountToPay>200</AmountToPay>
    </InvoiceLineItems>
    <InvoiceLineItems>
    <InvoiceLineItemID>4</InvoiceLineItemID>
    <InvoiceID>3</InvoiceID>
    <AmountToPay>100</AmountToPay>
    </InvoiceLineItems>
    <InvoiceLineItems>
    <InvoiceLineItemID>5</InvoiceLineItemID>
    <InvoiceID>3</InvoiceID>
    <AmountToPay>200</AmountToPay>
    </InvoiceLineItems>
    <InvoiceLineItems>
    <InvoiceLineItemID>6</InvoiceLineItemID>
    <InvoiceID>3</InvoiceID>
    <AmountToPay>300</AmountToPay>
    </InvoiceLineItems>
    </ProgramData>


    XSLT FILE

    <?xml version="1.0"?>
    <xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns="http://www.w3.org/TR/REC-html40"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:local="#local-functions">
    <xsl:template match="/">
    <HTML>
    <BODY>
    <TABLE CELLSPACING="1" CELLPADDING="1" BORDER="0">
    <TR>
    <TH>Invoice Number</TH>
    <TH width="10"></TH>
    <TH align="right">Amount</TH>
    <TH width="10"></TH>
    <TH align="right">Amount Paid</TH>
    </TR>
    <TR>
    <TD colspan="17" height="1" bgcolor="black"></TD>
    </TR>

    <xsl:variable name="SelectData" select="ProgramData/Invoices"
    />
    <xsl:for-each select="$SelectData">

    <xsl:variable name="AmountToPay"
    select="sum(//InvoiceLineItems[InvoiceID=current()/InvoiceID]/AmountToPay)"
    />
    <TR>
    <TD><xsl:value-of select="InvoiceNumber" /></TD>
    <TD></TD>
    <TD align="right"><xsl:value-of
    select="format-number(Amount, '#,##0.00')" /></TD>
    <TD></TD>
    <TD align="right"><xsl:value-of
    select="format-number($AmountToPay, '#,##0.00')" /></TD>
    </TR>

    </xsl:for-each>

    <TR>
    <TD colspan="17" height="1" bgcolor="black"></TD>
    </TR>


    <xsl:variable name="GrandAmount"
    select="sum($SelectData/Amount)" />
    <xsl:variable name="GrandAmountToPay"
    select="sum(ProgramData/InvoiceLineItems[InvoiceID=$SelectData/InvoiceID]/AmountToPay)"
    />
    <TR>
    <TD>Grand Total</TD>
    <TD></TD>
    <TD align="right"><xsl:value-of
    select="format-number($GrandAmount, '#,##0.00')" /></TD>
    <TD></TD>
    <TD align="right"><xsl:value-of
    select="format-number($GrandAmountToPay, '#,##0.00')" /></TD>
    </TR>

    </TABLE>
    </BODY>
    </HTML>
    </xsl:template>
    </xsl:stylesheet>


    When I run the translation I get the following result:

    Invoice Number Amount Amount Paid
    100 1,000.00 0.00
    101 2,000.00 300.00
    102 3,000.00 600.00
    Grand Total 6,000.00 900.00

    As you can see from the script each invoice's Amount Paid value is
    calculated as the sum of its line item Amount Paid values. What I
    would like to do is only display those invoices where the Amount Paid
    is greater than 0. I realize I could wrap the code contained within
    the for-each loop with a test to ensure the the sum is greater than 0
    before writing the value but that doesn't help the Grand Total row. It
    would still include all invoices in its summation. Using the example
    above, the Grand Total of the Amount column would still be 6,000.00
    despite the fact that invoice 100 would not be displayed.

    What I've tried to do is exclude those invoices from the $SelectData
    variable. If I can do that the entire sheet would calculate correctly:

    Invoice Number Amount Amount Paid
    101 2,000.00 300.00
    102 3,000.00 600.00
    Grand Total 5,000.00 900.00

    I've tried everything I can think of to accomplish this to no avail.
    If anyone can point me in the right direction I would appreciate it.

    Thanks
     
    , Aug 23, 2006
    #1
    1. Advertising

  2. Soren Kuula Guest

    wrote:

    > I'm having difficulty finding the correct syntax that will allow me to
    > select a group of invoices based on the total of an amount column
    > located in its line items. Below are simplified examples of my XML and
    > XSLT files:

    .....

    > When I run the translation I get the following result:
    >
    > Invoice Number Amount Amount Paid
    > 100 1,000.00 0.00
    > 101 2,000.00 300.00
    > 102 3,000.00 600.00
    > Grand Total 6,000.00 900.00
    >
    > As you can see from the script each invoice's Amount Paid value is
    > calculated as the sum of its line item Amount Paid values. What I
    > would like to do is only display those invoices where the Amount Paid
    > is greater than 0.


    Wait -- I am confused about the things that are named AmountToPay, that
    you call amount paid??? As far as I know, that something is to pay means
    is not yet paid?

    > I realize I could wrap the code contained within
    > the for-each loop with a test to ensure the the sum is greater than 0
    > before writing the value but that doesn't help the Grand Total row. It
    > would still include all invoices in its summation. Using the example
    > above, the Grand Total of the Amount column would still be 6,000.00
    > despite the fact that invoice 100 would not be displayed.
    >
    > What I've tried to do is exclude those invoices from the $SelectData
    > variable. If I can do that the entire sheet would calculate correctly:
    >
    > Invoice Number Amount Amount Paid
    > 101 2,000.00 300.00
    > 102 3,000.00 600.00
    > Grand Total 5,000.00 900.00
    >
    > I've tried everything I can think of to accomplish this to no avail.
    > If anyone can point me in the right direction I would appreciate it.


    Try a key: The 'gedefims' one I included rounds up a map from each
    InvoiceID to lists of InvoiceLineItems with the ID. That is then used
    later in a predicate, where the text value of the InvoiceID children are
    used for looking up the lists. The list nodes' AmountToPay children are
    summed, and if the result > 0, the Amount child of the parent of
    InvoiceID's parent is included in the grand total...........

    <?xml version="1.0"?>
    <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns="http://www.w3.org/TR/REC-html40"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt"
    xmlns:local="#local-functions" version="1.0">
    <xsl:key name="gedefims" match="InvoiceLineItems" use="InvoiceID"/>
    <xsl:template match="/">
    <HTML>
    <BODY>
    <TABLE CELLSPACING="1" CELLPADDING="1" BORDER="0">
    <TR>
    <TH>Invoice Number</TH>
    <TH width="10"/>
    <TH align="right">Amount</TH>
    <TH width="10"/>
    <TH align="right">Amount Paid</TH>
    </TR>
    <TR>
    <TD colspan="17" height="1" bgcolor="black"/>
    </TR>
    <xsl:variable name="SelectData" select="ProgramData/Invoices"/>
    <xsl:for-each select="$SelectData">
    <xsl:variable name="AmountToPay"
    select="sum(//InvoiceLineItems[InvoiceID=current()/InvoiceID]/AmountToPay)"/>
    <xsl:if test="$AmountToPay &gt; 0">
    <TR>
    <TD>
    <xsl:value-of select="InvoiceNumber"/>
    </TD>
    <TD/>
    <TD align="right">
    <xsl:value-of select="format-number(Amount,
    '#,##0.00')"/>
    </TD>
    <TD/>
    <TD align="right">
    <xsl:value-of select="format-number($AmountToPay,
    '#,##0.00')"/>
    </TD>
    </TR>
    </xsl:if>
    </xsl:for-each>
    <TR>
    <TD colspan="17" height="1" bgcolor="black"/>
    </TR>
    <xsl:variable name="GrandAmount"
    select="sum(/ProgramData/Invoices [sum(key('gedefims',
    InvoiceID)/AmountToPay) &gt; 0]/Amount)"/>
    <xsl:variable name="GrandAmountToPay"
    select="sum(ProgramData/InvoiceLineItems[InvoiceID=$SelectData/InvoiceID]/AmountToPay)"/>
    <TR>
    <TD>Grand Total</TD>
    <TD/>
    <TD align="right">
    <xsl:value-of select="format-number($GrandAmount,
    '#,##0.00')"/>
    </TD>
    <TD/>
    <TD align="right">
    <xsl:value-of select="format-number($GrandAmountToPay,
    '#,##0.00')"/>
    </TD>
    </TR>
    </TABLE>
    </BODY>
    </HTML>
    </xsl:template>
    </xsl:stylesheet>

    Hope that confused --- uh, helped,

    Søren
     
    Soren Kuula, Aug 24, 2006
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Timo Nentwig

    selecting nodes between other nodes

    Timo Nentwig, Jun 16, 2004, in forum: XML
    Replies:
    1
    Views:
    436
    Patrick TJ McPhee
    Jun 17, 2004
  2. J. T.
    Replies:
    0
    Views:
    388
    J. T.
    Aug 11, 2004
  3. Tjerk Wolterink
    Replies:
    9
    Views:
    1,359
    Joris Gillis
    Feb 7, 2005
  4. Replies:
    1
    Views:
    1,237
    Joe Kesselman
    Nov 1, 2006
  5. Luis Caceres
    Replies:
    1
    Views:
    148
    Jim Menard
    Oct 7, 2003
Loading...

Share This Page