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="Outstandi ngInvoiceBalanc es.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>
<InvoiceLineIte ms>
<InvoiceLineIte mID>1</InvoiceLineItem ID>
<InvoiceID>1</InvoiceID>
<AmountToPay> 0</AmountToPay>
</InvoiceLineItem s>
<InvoiceLineIte ms>
<InvoiceLineIte mID>2</InvoiceLineItem ID>
<InvoiceID>2</InvoiceID>
<AmountToPay>10 0</AmountToPay>
</InvoiceLineItem s>
<InvoiceLineIte ms>
<InvoiceLineIte mID>3</InvoiceLineItem ID>
<InvoiceID>2</InvoiceID>
<AmountToPay>20 0</AmountToPay>
</InvoiceLineItem s>
<InvoiceLineIte ms>
<InvoiceLineIte mID>4</InvoiceLineItem ID>
<InvoiceID>3</InvoiceID>
<AmountToPay>10 0</AmountToPay>
</InvoiceLineItem s>
<InvoiceLineIte ms>
<InvoiceLineIte mID>5</InvoiceLineItem ID>
<InvoiceID>3</InvoiceID>
<AmountToPay>20 0</AmountToPay>
</InvoiceLineItem s>
<InvoiceLineIte ms>
<InvoiceLineIte mID>6</InvoiceLineItem ID>
<InvoiceID>3</InvoiceID>
<AmountToPay>30 0</AmountToPay>
</InvoiceLineItem s>
</ProgramData>
XSLT FILE
<?xml version="1.0"?>
<xsl:styleshe et version="1.0"
xmlns:xsl="http ://www.w3.org/1999/XSL/Transform"
xmlns="http://www.w3.org/TR/REC-html40"
xmlns:msxsl="ur n:schemas-microsoft-com:xslt"
xmlns:local="#l ocal-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">A mount</TH>
<TH width="10"></TH>
<TH align="right">A mount Paid</TH>
</TR>
<TR>
<TD colspan="17" height="1" bgcolor="black" ></TD>
</TR>
<xsl:variable name="SelectDat a" select="Program Data/Invoices"
/>
<xsl:for-each select="$Select Data">
<xsl:variable name="AmountToP ay"
select="sum(//InvoiceLineItem s[InvoiceID=curre nt()/InvoiceID]/AmountToPay)"
/>
<TR>
<TD><xsl:valu e-of select="Invoice Number" /></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($AmountT oPay, '#,##0.00')" /></TD>
</TR>
</xsl:for-each>
<TR>
<TD colspan="17" height="1" bgcolor="black" ></TD>
</TR>
<xsl:variable name="GrandAmou nt"
select="sum($Se lectData/Amount)" />
<xsl:variable name="GrandAmou ntToPay"
select="sum(Pro gramData/InvoiceLineItem s[InvoiceID=$Sele ctData/InvoiceID]/AmountToPay)"
/>
<TR>
<TD>Grand Total</TD>
<TD></TD>
<TD align="right">< xsl:value-of
select="format-number($GrandAm ount, '#,##0.00')" /></TD>
<TD></TD>
<TD align="right">< xsl:value-of
select="format-number($GrandAm ountToPay, '#,##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
