[XSL-LIST Mailing List Archive Home] [By Thread] [By Date]

[xsl] Grouping and Subtotals


Subject: [xsl] Grouping and Subtotals
From: "David Petersen" <david@xxxxxxxxxxxxxx>
Date: Tue, 1 Apr 2003 11:10:57 -0600

I currently use the following XSL to display the data indicated in the
below XML document to display like this:

<xsl:for-each select="RECORD[count(. |
key('listofbrochures',BrochureName)[1]) = 1]">
	<tr><td colspan="6" class="dataValue"><font
size="4"><xsl:value-of select="BrochureName" /></font></td></tr>
	<xsl:for-each select="key('listofbrochures', BrochureName)">
		<tr>
			<td nowrap="true" class="dataValue">
				<xsl:value-of
select="SalesRepFullName"/>
			</td>
			<td align="right" class="dataValue">
				<xsl:value-of
select="format-number(ProjectedUnits,'#,##0')"/>
			</td>
			<td align="right" class="dataValue">
				<xsl:value-of
select="format-number(ActualUnits,'#,##0')"/>
			</td>
			<td align="right" class="dataValue">
				<xsl:value-of
select="format-number(RemainingProjected,'#,##0')"/>
			</td>
		</tr>
	</xsl:for-each>
</xsl:for-each>

Brochure/Salesrep Name         Projected      Actual        Remaining
BROCHURE 1
  Salesman 1                         500           0              500
  Salesman 2                         500           0              500
BROCHURE 2
  Salesman 1                         500           0              500
  Salesman 2                         500           0              500



What I would like to do is only display the Brochure names with
subtotals and then total the whole document.  I know to use the syntax
sum(//ProjectedUnits) to give me the grand total and that works fine.
How do I compute the Subtotals so that I get the following display:

BrochureName                       Projected         Actual
Remaining
BROCHURE 1                              1000              0
1000
BROCHURE 2                              1000              0
1000
Totals                                  2000              0
2000


I have tried the following but it didn't work.
<xsl:value-of
select="format-number(sum(key('listofbrochures',.)/../ProjectedUnits),'#
,##0')"/>



XML DATA Document
------------------------------------------------------------------------
--------------
<?xml version="1.0"?>
<DATA>
	<RECORD>
		<SalesPersonID>FB02</SalesPersonID>
		<SalesRepFullName>Salesman 1</SalesRepFullName>
		<BrochureID>165</BrochureID>
		<BrochureName>BROCHURE 1</BrochureName>
		<GroupName>DistRep</GroupName>
		<SeasonName>Spring 2003</SeasonName>
		<ProjectedUnits>500</ProjectedUnits>
		<SalesRepProjectedUnits>300</SalesRepProjectedUnits>
		<ProjectedForActual>0</ProjectedForActual>
	
<SalesRepProjectedForActual>0</SalesRepProjectedForActual>
		<ActualUnits>0</ActualUnits>
		<RemainingProjected>500</RemainingProjected>
	</RECORD>
	<RECORD>
		<SalesPersonID>FB02</SalesPersonID>
		<SalesRepFullName>Salesman 2</SalesRepFullName>
		<BrochureID>165</BrochureID>
		<BrochureName>BROCHURE 1</BrochureName>
		<GroupName>DistRep</GroupName>
		<SeasonName>Spring 2003</SeasonName>
		<ProjectedUnits>500</ProjectedUnits>
		<SalesRepProjectedUnits>300</SalesRepProjectedUnits>
		<ProjectedForActual>0</ProjectedForActual>
	
<SalesRepProjectedForActual>0</SalesRepProjectedForActual>
		<ActualUnits>0</ActualUnits>
		<RemainingProjected>500</RemainingProjected>
	</RECORD>
	<RECORD>
		<SalesPersonID>FB02</SalesPersonID>
		<SalesRepFullName>Salesman 1</SalesRepFullName>
		<BrochureID>165</BrochureID>
		<BrochureName>BROCHURE 2</BrochureName>
		<GroupName>DistRep</GroupName>
		<SeasonName>Spring 2003</SeasonName>
		<ProjectedUnits>500</ProjectedUnits>
		<SalesRepProjectedUnits>300</SalesRepProjectedUnits>
		<ProjectedForActual>0</ProjectedForActual>
	
<SalesRepProjectedForActual>0</SalesRepProjectedForActual>
		<ActualUnits>0</ActualUnits>
		<RemainingProjected>500</RemainingProjected>
	</RECORD>
	<RECORD>
		<SalesPersonID>FB02</SalesPersonID>
		<SalesRepFullName>Salesman 2</SalesRepFullName>
		<BrochureID>165</BrochureID>
		<BrochureName>BROCHURE 2</BrochureName>
		<GroupName>DistRep</GroupName>
		<SeasonName>Spring 2003</SeasonName>
		<ProjectedUnits>500</ProjectedUnits>
		<SalesRepProjectedUnits>300</SalesRepProjectedUnits>
		<ProjectedForActual>0</ProjectedForActual>
	
<SalesRepProjectedForActual>0</SalesRepProjectedForActual>
		<ActualUnits>0</ActualUnits>
		<RemainingProjected>500</RemainingProjected>
	</RECORD>
</DATA>






 XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list



Current Thread
Keywords