Selecting Nodes Using Subtotal of Child Nodes

R

renfrochris

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
 
S

Soren Kuula

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.
Click to expand...

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.
Click to expand...

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
 

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How can I calculate the last payment of the year to be the sum of all previous payments for that year and subtracting it from Research Costs value? 7
How to add dropdown selected data to table using jquery 2
XSL Calculation 0
How Count Cell and Divide in Percentage in Table Using XSLT 1
newbie using group by in XSL on XLM 6
Extract attributes using xslt 5
php-generated email not formatting on a mac 13
Generating html out of xml using xslt 3

Members online

Total: 63 (members: 2, guests: 61)
Robots: 437

Forum statistics

Threads
473,994
Messages
2,570,222
Members
46,810
Latest member
Kassie0918

Latest Threads

Top