I want a result organized like following table:
**Costc 1000**
Product code Quantity Total amount
SALESCOST 1 120.04
LEASINGRENT 1 59.90
**Costc 2000**
Product code Quantity Total amount
SALESCOST 1 118.94
LEASINGCOST 1 513.34
**Costc 3000**
Product code Quantity Total amount
LEASINGCOST 1 658.24
LEASINGRENT 2 100.80
And the input-file I have look like this.
<?xml version="1.0" encoding="iso-8859-1"?>
<Details>
<Detail>
<LineNo>1</LineNo>
<Products>
<ProductCode>SALESCOST</ProductCode>
<ProductDescr>Sales amount asset:997000000000</ProductDescr>
<Quantity>1.00</Quantity>
</Products>
<Costc>
<Value>2000</Value>
</Costc>
<TotAmount>118.94</TotAmount>
</Detail>
<Detail>
<LineNo>2</LineNo>
<Products>
<ProductCode>LEASINGCOST</ProductCode>
<ProductDescr>Leasing cost asset:997000000003</ProductDescr>
</Products>
<Costc>
<Value>2000</Value>
</Costc>
<Quantity>1.00</Quantity>
<TotAmount>513.34</TotAmount>
</Detail>
<Detail>
<LineNo>3</LineNo>
<Products>
<ProductCode>SALESCOST</ProductCode>
<ProductDescr>Sales amount asset:997000000001</ProductDescr>
</Products>
<Costc>
<Value>1000</Value>
</Costc>
<Quantity>1.00</Quantity>
<TotAmount>120.04</TotAmount>
</Detail>
<Detail>
<LineNo>4</LineNo>
<Products>
<ProductCode>LEASINGCOST</ProductCode>
<ProductDescr>Leasing cost asset:997000000002</ProductDescr>
</Products>
<Costc>
<Value>3000</Value>
</Costc>
<Quantity>1.00</Quantity>
<TotAmount>658.24</TotAmount>
</Detail>
<Detail>
<LineNo>5</LineNo>
<Products>
<ProductCode>LEASINGRENT</ProductCode>
<ProductDescr>Leasing interest asset:997000000001</ProductDescr>
</Products>
<Costc>
<Value>3000</Value>
</Costc>
<Quantity>1.00</Quantity>
<TotAmount>48.90</TotAmount>
</Detail>
<Detail>
<LineNo>6</LineNo>
<Products>
<ProductCode>LEASINGRENT</ProductCode>
<ProductDescr>Leasing interest asset:997000000002</ProductDescr>
</Products>
<Costc>
<Value>3000</Value>
</Costc>
<Quantity>1.00</Quantity>
<TotAmount>51.90</TotAmount>
</Detail>
<Detail>
<LineNo>7</LineNo>
<Products>
<ProductCode>LEASINGRENT</ProductCode>
<ProductDescr>Leasing interest asset:997000000002</ProductDescr>
</Products>
<Costc>
<Value>1000</Value>
</Costc>
<Quantity>1.00</Quantity>
<TotAmount>59.90</TotAmount>
</Detail>
How can this be done? Its multi-level group by and also sums and count function in this and it’s too complex for me. Many thanks
In XSLT 1.0 you would need to use a technique called Meunchian Grouping. In this case you are grouping firstly by Costc elements, and then by Products elements.
This means you need to define two keys. Firstly to group details by Costc
<xsl:key name="costc" match="Detail" use="Costc/Value"/>
And then to group details by Costc and Product
<xsl:key name="product" match="Detail" use="concat(Costc/Value, '|', Products/ProductCode)"/>
(Do note the use of the pipe character in the concatenation. It is important this character does not appear in the value or the product code).
Then, to group by the Costc elements, you can do the following:
<xsl:apply-templates
select="Detail[generate-id() = generate-id(key('costc', Costc/Value)[1])]">
This would get you the first Detail element for each unique Costc element. Then, for each such group, you would then group by the products within that group.
<xsl:apply-templates
select="../Detail
[Costc/Value = current()/Costc/Value]
[generate-id()
= generate-id(key('product', concat(Costc/Value, '|', Products/ProductCode))[1])]" />
So, given the following XSLT
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="html" indent="yes"/>
<xsl:key name="costc" match="Detail" use="Costc/Value"/>
<xsl:key name="product" match="Detail" use="concat(Costc/Value, '|', Products/ProductCode)"/>
<xsl:template match="/Details">
<xsl:apply-templates select="Detail[generate-id() = generate-id(key('costc', Costc/Value)[1])]" mode="Cost">
<xsl:sort select="Costc/Value" />
</xsl:apply-templates>
</xsl:template>
<xsl:template match="Detail" mode="Cost">
<h1><xsl:value-of select="Costc/Value" /></h1>
<table>
<tr>
<td>Product Code</td>
<td>Quantity</td>
<td>Total amount</td>
</tr>
<xsl:apply-templates select="../Detail[Costc/Value = current()/Costc/Value][generate-id() = generate-id(key('product', concat(Costc/Value, '|', Products/ProductCode))[1])]" mode="Product" />
</table>
</xsl:template>
<xsl:template match="Detail" mode="Product">
<tr>
<td><xsl:value-of select="Products/ProductCode" /></td>
<td><xsl:value-of select="sum(key('product', concat(Costc/Value, '|', Products/ProductCode))//Quantity)" /></td>
<td><xsl:value-of select="sum(key('product', concat(Costc/Value, '|', Products/ProductCode))/TotAmount)" /></td>
</tr>
</xsl:template>
</xsl:stylesheet>
When applied to your sample XML, the following is output:
<h1>1000</h1>
<table>
<tr>
<td>Product Code</td>
<td>Quantity</td>
<td>Total amount</td>
</tr>
<tr>
<td>SALESCOST</td>
<td>1</td>
<td>120.04</td>
</tr>
<tr>
<td>LEASINGRENT</td>
<td>1</td>
<td>59.9</td>
</tr>
</table>
<h1>2000</h1>
<table>
<tr>
<td>Product Code</td>
<td>Quantity</td>
<td>Total amount</td>
</tr>
<tr>
<td>SALESCOST</td>
<td>1</td>
<td>118.94</td>
</tr>
<tr>
<td>LEASINGCOST</td>
<td>1</td>
<td>513.34</td>
</tr>
</table>
<h1>3000</h1>
<table>
<tr>
<td>Product Code</td>
<td>Quantity</td>
<td>Total amount</td>
</tr>
<tr>
<td>LEASINGCOST</td>
<td>1</td>
<td>658.24</td>
</tr>
<tr>
<td>LEASINGRENT</td>
<td>2</td>
<td>100.8</td>
</tr>
</table>