SSDN - Sum Groups into a subtotal and Grand Total

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Conferences

	Stylus Studio Feature Requests (1192)
	Stylus Studio Technical Forum (14621)
	Website Feedback (249)
	XSLT Help and Discussion (7625)
	Sum Groups into a subtotal an... (4)
	Sum Groups into a subtota...
	Sum Groups into a subtota...
	Sum Groups into a subtota...
	Finding an alternative to disa... (3)
	Counting unique values of an a... (3)
	XSLT not acceptable (2)
	Use of XSL file (8)
	Mapping Question. Please Help (2)
	XSL - FO Page count (3)
	xml tree to flat xml (3)
	xsl:element - qname (3)
	XSLT and multiple children (2)
	To find totalamount (5)
	Comparing Strings - Using cont... (1)
	AUtomatic conversion XML to XM... (2)
	recursion of wurfl document - ... (4)
	Traversing XML Tree - Want to ... (4)
	Instead of repeating code, can... (3)
	XSL - Display Variable From UR... (4)
	Show/Hide SPAN tag (2)
	Help a newbie (5)
	convert attribute to element (2)
	Learning XML/XSLT (2)
	Previous [1261-1280] [1281-1300] [1301-1320] Next
	XQuery Help and Discussion (2017)
	Stylus Studio FAQs (159)
	Stylus Studio Code Samples & Utilities (364)
	Stylus Studio Announcements (113)

Topic

Rafael Astorga

Subject: Sum Groups into a subtotal and Grand Total
Author: Rafael Astorga
Date: 15 Nov 2006 10:41 PM

Hello,

I am a newbie on the implementation of XSL-FO into a XML file.

What I am trying to do with this XML, is to generate a PDF where I can enlist a series of Invoices (Please view Example.pdf) <-- this is what it should look like.

I am struggling to find a way to sum into a subtotal the amount of each Invoice Recipient. So far I can display each invoice with its total. (View InvoiceList.pdf)

I was wondering, if you have and ideas on How can I add up each Invoice and only display the subtotal on the last entry of each Invoice Recipient.

I've attached the XSL and XML files.

I am using Stylus Studio 2007 XML Enterprise Suite.

Thank you!!

ZZMapTestTxResult.xml
XML Source

MetroGroupInvoiceRev5.xsl
XSL-FO

Example.pdf
Goal

InvoiceList.pdf
What I’ve accomplished

James Durning

Subject: Sum Groups into a subtotal and Grand Total
Author: James Durning
Date: 16 Nov 2006 11:45 AM

If you can use XSLT 2.0, you could use for-each-group.
<xsl:for-each-group select="/InvoiceStart/MetroGroupInvoicSummary" group-by="Header/InvoiceRecipient">

If you can only use XSLT 1.0, I recommend Muenchian grouping, http://www.jenitennison.com/xslt/grouping/muenchian.html.
1. Create a key at top:
<xsl:key name="InvoiceByRecipient" match="MetroGroupInvoicSummary" use="Header/InvoiceRecipient"/>

2. Get each group: In this case, we get the first element of each group.
<xsl:for-each select="/InvoiceStart/MetroGroupInvoicSummary[count(. | key('InvoiceByRecipient', Header/InvoiceRecipient)[1]) = 1]">

3. Sort by group, in this case use the Invoice Recipient.
<xsl:sort select="Header/InvoiceRecipient"/>

4. Sort by date? You might not have to if it is already sorted this way in your xml file.
<xsl:sort select="substring-after(substring-after(Header/Date, '.'), '.')"/>
<xsl:sort select="substring-after(Header/Date, '.')"/>
<xsl:sort select="Header/Date"/>

5. Output data for each.
<xsl:for-each select="key('InvoiceByRecipient', Header/InvoiceRecipient)"/>
...

6. Output group data:
<xsl:value-of select="sum(key('InvoiceByRecipient', Header/InvoiceRecipient)/InvoiceTotal/TotalNetAmount)
<xsl:value-of select="sum(key('InvoiceByRecipient', Header/InvoiceRecipient)/InvoiceTotal/VATAMT)
<xsl:value-of select="sum(key('InvoiceByRecipient', Header/InvoiceRecipient)/InvoiceTotal/TotalAmount)

I hope you can use some of this.

Rafael Astorga

Subject: Sum Groups into a subtotal and Grand Total
Author: Rafael Astorga
Date: 19 Nov 2006 06:32 PM

Thank you James !!! It worked pretty well.

Although I have another question. Actually a couple questions.

Is there a way by using XSL 1.0, to only print the subTotal in the last Item of each group (Grouped by Invoice Recipient).

I tried by modifying the XML by adding an element, It could also be a attribute, that contains a boolean value that says if the Invoice Recipient is the last one of the group and therefore it should print the subtotal. Otherwise it should be left blank, and move to the next item.

Ex:

<MetroGroupInvoicSummary>
<Header>
<LineNumber>11</LineNumber>
<InvoiceNumber>4900157934</InvoiceNumber>
<InvoiceDate>25.10.2006</InvoiceDate>
<InvoiceRecipient>4306286000007</InvoiceRecipient>
<Last>0</Last>
<BillingPeriod>25.10.2006 - 25.10.2006</BillingPeriod>
</Header>
<InvoiceTotal>
<TotalNetAmount>34.93</TotalNetAmount>
<VATAMT>218.32</VATAMT>
<TotalAmount>253.25</TotalAmount>
</InvoiceTotal>
</MetroGroupInvoicSummary>
<MetroGroupInvoicSummary>
<Header>
<LineNumber>12</LineNumber>
<InvoiceNumber>4900157971</InvoiceNumber>
<InvoiceDate>25.10.2006</InvoiceDate>
<InvoiceRecipient>4306286000007</InvoiceRecipient>
<Last>1</Last>
<BillingPeriod>25.10.2006 - 25.10.2006</BillingPeriod>
</Header>
<InvoiceTotal>
<TotalNetAmount>64.62</TotalNetAmount>
<VATAMT>403.85</VATAMT>
<TotalAmount>468.47</TotalAmount>
</InvoiceTotal>
</MetroGroupInvoicSummary>

I tried to use the instruction <xsl:choose><xsl:when>, but It's not doing much.

This how I implemented:

<fo:block>
<xsl:choose>
<xsl:when test="Last= 1">
<xsl:value-of select="sum(key('InvoiceByRecipient', Header/InvoiceRecipient)/InvoiceTotal/TotalNetAmount)"/>
</xsl:when>
<xsl:otherwise>
<fo:block>
<xsl:text></xsl:text>
</fo:block>
</xsl:otherwise>
</xsl:choose>
</fo:block>

The second question is how to reduce the decimals to only two. Some of the subtotals have more than 2 decimals. I was looking at the round() instruction but I believe that cuts all the decimals.

You may view the source files I've attached for more details.

Thank you for all your help.

MetroGroupInvoiceRev5(1).xsl

ZZMapTestTxResult(1).xml

James Durning

Subject: Sum Groups into a subtotal and Grand Total
Author: James Durning
Date: 20 Nov 2006 01:39 PM

I honestly couldn't figure out an easy solution to the first problem.

Number formatting:
http://www.w3schools.com/xsl/func_formatnumber.asp
<xsl:value-of select="format-number(.,'#.00')"/>
where . is the xpath of the value.

Powered by Stylus Studio, the world's leading XML IDE for XML, XSLT, XQuery, XML Schema, DTD, XPath, WSDL, XHTML, SQL/XML, and XML Mapping!

Go to Conference:

Log In Options Username: Password:

Site Map | Privacy Policy | Terms of Use | Trademarks

Stylus Studio® and DataDirect XQuery ™are from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2016 All Rights Reserved.