SSDN - Grouping and summing ...

XML Editor

Sign Up

Search

Options

Chat

Help

News

Log in

Not Logged in

Topic

Topic Page 1 2 3 4 5 6 7 8 9

Alan Searle

Subject: Grouping and summing ...
Author: Alan Searle
Date: 01 Sep 2006 04:25 AM

Hi everyone,
I have a XML file that I need to filter and group. Apparently XSL 2.0 offers some good grouping features but unfortunately (for reasons of compatibility) I think I need to stay with XSL 1.0.

I have found that by using a 'key' I can get my XSL to group the data in my XML file (see example below) but am not sure how to apply a variable to filter to this grouping. I have experimented with a hard-coded filter (e.g. for a particular month: mnth='200602') but find that I don't get back what I need. This may be because the method I am using seems to focus in on the first record ( [1] ) but I'm not sure.

Anyway, below is my example so I hope that someone can give me some pointers so that I can apply a filter to retrieve grouped data for a particular team over a particular period (i.e. a number of months).

Or if anyone has recommendations of better methods of grouping, then that would be a great help.

Many thanks,
Alan Searle

XML: here I need to filter by mnth and display grouped/summed data of each team (i.e. Total Sales) ...

<?xml-stylesheet href="team.xsl" type="text/xsl"?>
<?xml version="1.0" encoding="UTF-8"?>
<root>
<xmlout><mnth>200601</mnth><team>A</team><sales>23</sales>
</xmlout><xmlout>
<mnth>200601</mnth><team>B</team><sales>33</sales>
</xmlout><xmlout>
<mnth>200601</mnth><team>C</team><sales>98</sales>
</xmlout><xmlout>
<mnth>200602</mnth><team>A</team><sales>14</sales>
</xmlout><xmlout>
<mnth>200602</mnth><team>B</team><sales>65</sales>
</xmlout><xmlout>
<mnth>200602</mnth><team>C</team><sales>76</sales>
</xmlout><xmlout>
<mnth>200603</mnth><team>A</team><sales>34</sales>
</xmlout><xmlout>
<mnth>200603</mnth><team>B</team><sales></sales>
</xmlout><xmlout>
<mnth>200603</mnth><team>C</team><sales>56</sales>
</xmlout>
</root>

XSL: note that I have tried to incorporate the filter into the for-each statement. Is this correct? To remove the filter delete ** mnth='200601' and **

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:key name="kteam" match="xmlout" use="team"/>

<xsl:template match="root">
<table border="1">
<tr><th colspan="4"> Team Overview <xsl:value-of select="team"/></th></tr>

<tr><th>Team</th><th>Count</th><th>sales</th><th>id</th></tr>
<xsl:for-each select="xmlout[mnth='200601' and (generate-id()=generate-id(key('kteam',team)[1]))]">
<tr>
<td><xsl:value-of select="team"/></td>
<td><xsl:value-of select="count(key('kteam',team)/team)"/></td>
<td><xsl:value-of select="sum(key('kteam',team)/sales)"/></td>
<td><xsl:value-of select="generate-id(.)"/></td>
</tr>
</xsl:for-each>
</table>
<br />
</xsl:template>
</xsl:stylesheet>

Tony Lavinio

Subject: Grouping and summing ...
Author: Tony Lavinio
Date: 11 Sep 2006 04:39 PM

See http://www.jenitennison.com/xslt/index.html
under "Complex Tasks".
(Oddly enough, this is the same answer I just gave
to someone else, but for a different question.)

Alan Searle

Subject: Grouping and summing ...
Author: Alan Searle
Date: 14 Sep 2006 07:24 AM

Hi Tony,

Yes, I used this URL to find the solution. I found out that I needed to adapt my syntax a little and it worked with the following (see below).

Maybe this snippet of syntax will be useful to someone else too?

Regards,
Alan Searle

<?xml version="1.0" encoding="utf-8" ?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:key name="kteam" match="xmlout" use="team"/>

<xsl:template match="root">
<table border="1">
<tr><th colspan="4"> Team Overview <xsl:value-of select="team"/></th></tr>

<tr><th>Team</th><th>Count</th><th>sales</th></tr>
<xsl:for-each select="xmlout[(generate-id()=generate-id(key('kteam',team)[1]))]">
<tr>
<td><xsl:value-of select="team"/></td>
<td><xsl:value-of select="count(key('kteam',team)[(mnth>='200602' and mnth<='200603')]/team)"/></td>
<td><xsl:value-of select="sum(key('kteam',team)[(mnth>='200602' and mnth<='200603')]/sales)"/></td>
</tr>
</xsl:for-each>
</table>
<br />
</xsl:template>

</xsl:stylesheet>

Topic Page 1 2 3 4 5 6 7 8 9

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.