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
