johnaryan
asked on
XSLT Grouping and Aggregation
This should be simple but I'm not very strong in XSLT.
I am trying to perform an XSLT on XML returned from a dataset that will give me the same
output as a "Group By" SQL statement combined with the "Sum" statement.
Using the Muenchian method i have been able to group by country but not Sum the Quantities
My XML Structure:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table>
<ReportingDataId>2</Report ingDataId>
<ReportingBusUnitDesc>Unit ed Kingdom</ReportingBusUnitD esc>
<SystemQuantity>2</SystemQ uantity>
<TotalRevenue>10000</Total Revenue>
</Table>
<Table>
<ReportingDataId>2</Report ingDataId>
<ReportingBusUnitDesc>Unit ed Kingdom</ReportingBusUnitD esc>
<SystemQuantity>2</SystemQ uantity>
<TotalRevenue>10000</Total Revenue>
</Table>
<Table>
<ReportingDataId>2</Report ingDataId>
<ReportingBusUnitDesc>Germ any</Repor tingBusUni tDesc>
<SystemQuantity>4</SystemQ uantity>
<TotalRevenue>20000</Total Revenue>
</Table>
<Table>
<ReportingDataId>2</Report ingDataId>
<ReportingBusUnitDesc>Germ any</Repor tingBusUni tDesc>
<SystemQuantity>4</SystemQ uantity>
<TotalRevenue>20000</Total Revenue>
</Table>
</DataSet>
My XSL:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform ">
<xsl:key name="countryKey" match="Table" use="ReportingBusUnitDesc" />
<xsl:template match="/">
<table border="0" cellpadding="0" cellspacing="0" width="1000">
<tr>
<td class="header">
Country
</td>
<td class="header">
units
</td>
<td class="header">
revenue
</td>
</tr>
<xsl:call-template name="dataTableGroup" />
<!-- Sum the Totals of quantities and revenue-->
<xsl:call-template name="dataTotals" />
</table>
</xsl:template>
<xsl:template name="dataTableGroup">
<xsl:for-each select="NewDataSet/Table[g enerate-id ()=generat e-id(key(' countryKey ',Reportin gBusUnitDe sc))]">
<xsl:sort select="ReportingBusUnitDe sc"/>
<tr>
<td class="columnTextRight">
<xsl:value-of select="ReportingBusUnitDe sc"/>
</td>
<td class="columnData">
<xsl:value-of select="sum(SystemQuantity [../Report ingBusUnit Desc=curre nt()])"/>
</td>
<td class="columnData">
$
</td>
</tr>
</xsl:for-each>
</xsl:template>
<xsl:template name="dataTotals">
<tr>
<td class="footer">
Total
</td>
<td class="footer" >
<xsl:value-of select="sum(/NewDataSet/Ta ble/System Quantity)" />
</td>
<td class="footer">
$<xsl:value-of select="sum(/NewDataSet/Ta ble/TotalR evenue)" />
</td>
</tr>
</xsl:template>
</xsl:stylesheet>
My desired output is:
Country Quantity Revenue
Germany 8 40000
United Kingdom 4 20000
Total 12 60000
If anyone could set me on the right path I would be very greatful.
I am trying to perform an XSLT on XML returned from a dataset that will give me the same
output as a "Group By" SQL statement combined with the "Sum" statement.
Using the Muenchian method i have been able to group by country but not Sum the Quantities
My XML Structure:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
<Table>
<ReportingDataId>2</Report
<ReportingBusUnitDesc>Unit
<SystemQuantity>2</SystemQ
<TotalRevenue>10000</Total
</Table>
<Table>
<ReportingDataId>2</Report
<ReportingBusUnitDesc>Unit
<SystemQuantity>2</SystemQ
<TotalRevenue>10000</Total
</Table>
<Table>
<ReportingDataId>2</Report
<ReportingBusUnitDesc>Germ
<SystemQuantity>4</SystemQ
<TotalRevenue>20000</Total
</Table>
<Table>
<ReportingDataId>2</Report
<ReportingBusUnitDesc>Germ
<SystemQuantity>4</SystemQ
<TotalRevenue>20000</Total
</Table>
</DataSet>
My XSL:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform ">
<xsl:key name="countryKey" match="Table" use="ReportingBusUnitDesc"
<xsl:template match="/">
<table border="0" cellpadding="0" cellspacing="0" width="1000">
<tr>
<td class="header">
Country
</td>
<td class="header">
units
</td>
<td class="header">
revenue
</td>
</tr>
<xsl:call-template name="dataTableGroup" />
<!-- Sum the Totals of quantities and revenue-->
<xsl:call-template name="dataTotals" />
</table>
</xsl:template>
<xsl:template name="dataTableGroup">
<xsl:for-each select="NewDataSet/Table[g
<xsl:sort select="ReportingBusUnitDe
<tr>
<td class="columnTextRight">
<xsl:value-of select="ReportingBusUnitDe
</td>
<td class="columnData">
<xsl:value-of select="sum(SystemQuantity
</td>
<td class="columnData">
$
</td>
</tr>
</xsl:for-each>
</xsl:template>
<xsl:template name="dataTotals">
<tr>
<td class="footer">
Total
</td>
<td class="footer" >
<xsl:value-of select="sum(/NewDataSet/Ta
</td>
<td class="footer">
$<xsl:value-of select="sum(/NewDataSet/Ta
</td>
</tr>
</xsl:template>
</xsl:stylesheet>
My desired output is:
Country Quantity Revenue
Germany 8 40000
United Kingdom 4 20000
Total 12 60000
If anyone could set me on the right path I would be very greatful.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Geert, I do appreciate the input.
I don't need too much optimizations as it seems the dataset will only be in the order of 10000 rows. And at the moment my sample set of 4000 performs adequately.
I don't need too much optimizations as it seems the dataset will only be in the order of 10000 rows. And at the moment my sample set of 4000 performs adequately.
cheers
ASKER
I'm still looking the optimal performance out of this., so rather than give myself the points I'm leaving the post open.
My solution......
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:key name="countryKey" match="Table" use="ReportingBusUnitDesc"
<xsl:template match="/">
<table border="0" cellpadding="0" cellspacing="0" width="600">
<tr>
<td class="header">
Country
</td>
<td class="header">
units
</td>
<td class="header">
revenue
</td>
</tr>
<xsl:call-template name="dataTableGroup" />
<!-- Sum the Totals of quantities and revenue-->
<xsl:call-template name="dataTotals" />
</table>
</xsl:template>
<xsl:template name="dataTableGroup">
<xsl:for-each select="NewDataSet/Table[g
<xsl:sort select="ReportingBusUnitDe
<tr>
<td class="columnTextRight">
<xsl:value-of select="ReportingBusUnitDe
</td>
<td class="columnData">
<xsl:value-of select="sum(key('countryKe
</td>
<td class="columnData">
$<xsl:value-of select="sum(key('countryKe
</td>
</tr>
</xsl:for-each>
</xsl:template>
<xsl:template name="dataTotals">
<tr>
<td class="footer">
Total
</td>
<td class="footer" >
<xsl:value-of select="sum(/NewDataSet/Ta
</td>
<td class="footer">
$<xsl:value-of select="sum(/NewDataSet/Ta
</td>
</tr>
</xsl:template>
</xsl:stylesheet>