Search code examples
xsltsumgrouping

XSLT grouping and calculating the total

I need to group the description, calculate its weights and sum up the totals

  • Steps
    • Group description for ItemLevel =3
    • Weight = (ItemLevel='3'/Dimension[@type='unitNetWeight']/@quantity only for first occurence) * sum(Items[ItemLevel='3']/DispatchQuantity/@quantity for matching ItemType)
    • Total sum

Now i am not able to group the description, only weights are coming correctly only for first ItemType. Total is not showing up correctly.

Current

Descp: Weight:
Pencil 400
Box 800
Carton 200
Total 700

Expected:

Descp: Weight:
Pencil 100
Clock 300
Box 400 Pen 200
Carton 200
Total 1200

I have tried many methods and is not working. Your kind help is appreciated!

<?xml version="1.0" encoding="UTF-8"?>

<cXML payloadID="16314uu19eii6-765h52u1pp0.162.97.178" timestamp="2021-10-06T02:54:24-07:00" version="1.2.051" xml:lang="en-PL">

<Request>
<Header>
<Level>
<Item>
<Items>
    <Dimension type="unitNetWeight" quantity="100.0">
      <UnitOfMeasure>LB</UnitOfMeasure>
    </Dimension>
    <ItemLevel>1</ItemLevel>
    <ItemType>Pencil</ItemType>
  <DispatchQuantity quantity="1.0">
    <UnitOfMeasure>EA</UnitOfMeasure>
  </DispatchQuantity>
</Items>

<Items>
    <Dimension type="unitNetWeight" quantity="100.0">
      <UnitOfMeasure>LB</UnitOfMeasure>
    </Dimension>
  <ItemLevel>3</ItemLevel>
  <ItemType>Pencil</ItemType>
  <DispatchQuantity quantity="1.0">
    <UnitOfMeasure>EA</UnitOfMeasure>
  </DispatchQuantity>


</Items>

<Items>
    <Dimension type="unitNetWeight" quantity="100.0">
      <UnitOfMeasure>LB</UnitOfMeasure>
    </Dimension>
  <ItemLevel>3</ItemLevel>
  <ItemType>Clock</ItemType>
  <DispatchQuantity quantity="3.0">
    <UnitOfMeasure>EA</UnitOfMeasure>
  </DispatchQuantity>
  </Items>
</Item>
</Level>

<Level>
<Item>

<Items>
    <Dimension type="unitNetWeight" quantity="200.0">
      <UnitOfMeasure>LB</UnitOfMeasure>
    </Dimension>
  <ItemLevel>3</ItemLevel>
  <ItemType>Box</ItemType>
  <DispatchQuantity quantity="2.0">
    <UnitOfMeasure>EA</UnitOfMeasure>
  </DispatchQuantity>
</Items>

<Items>
    <Dimension type="unitNetWeight" quantity="100.0">
      <UnitOfMeasure>LB</UnitOfMeasure>
    </Dimension>
  <ItemLevel>3</ItemLevel>
  <ItemType>Pen</ItemType>
  <DispatchQuantity quantity="2.0">
    <UnitOfMeasure>EA</UnitOfMeasure>
  </DispatchQuantity>
  </Items>
</Item>

<Item>

<Items>
    <Dimension type="unitNetWeight" quantity="200.0">
      <UnitOfMeasure>LB</UnitOfMeasure>
    </Dimension>
  <ItemLevel>2</ItemLevel>
  <ItemType>Frame</ItemType>
  <DispatchQuantity quantity="2.0">
    <UnitOfMeasure>EA</UnitOfMeasure>
  </DispatchQuantity>
</Items>

<Items>
    <Dimension type="unitNetWeight" quantity="100.0">
      <UnitOfMeasure>LB</UnitOfMeasure>
    </Dimension>
  <ItemLevel>3</ItemLevel>
  <ItemType>Carton</ItemType>
  <DispatchQuantity quantity="2.0">
    <UnitOfMeasure>EA</UnitOfMeasure>
  </DispatchQuantity>
  </Items>
</Item>
</Level>
</Header>
</Request>

</cXML>

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0" 
    xmlns:xs="http://www.w3.org/2001/XMLSchema" 
    
    xmlns:fo="http://www.w3.org/1999/XSL/Format"
    
    >
   
    <xsl:decimal-format name="generalFormat" grouping-separator="," decimal-separator="." /> 
    <xsl:template match="/" name="Barcode">
        
     
        <fo:root>
            <fo:layout-master-set>
                <fo:simple-page-master master-name="ManufacturLabelSize-first" page-height="100mm" page-width="100mm"  >
                    <fo:region-body margin-top="15mm" />
                    <fo:region-before />
                    <fo:region-after />
                </fo:simple-page-master>
                <fo:simple-page-master master-name="ManufacturLabelSize-rest" page-height="100mm" page-width="100mm"   >
                    <fo:region-body margin-top="15mm"/>
                    <fo:region-before />
                    <fo:region-after />
                </fo:simple-page-master>
                <fo:page-sequence-master master-name="ManufacturLabelSize-portrait">
                    <fo:repeatable-page-master-alternatives>
                        <fo:conditional-page-master-reference master-reference="ManufacturLabelSize-first" 
                            page-position="first"/>
                        <fo:conditional-page-master-reference master-reference="ManufacturLabelSize-rest" 
                            page-position="rest"/>
                    </fo:repeatable-page-master-alternatives>
                </fo:page-sequence-master>
            </fo:layout-master-set>
            
            <fo:page-sequence master-reference="ManufacturLabelSize-portrait" id="pSeqID">
                
                <fo:flow flow-name="xsl-region-body">
                   
                    <fo:table>
                   
                        <fo:table-body >
                            <fo:table-row>
                                <fo:table-cell><fo:block>Descp:</fo:block></fo:table-cell>
                                <fo:table-cell> <fo:block >Weight:</fo:block></fo:table-cell>
                            </fo:table-row>
                            <fo:table-row>
                                <fo:table-cell>
                                  
                                    <fo:block>
                                        <xsl:call-template name="line"></xsl:call-template>
                                    </fo:block>
                                    
                                   
                                </fo:table-cell>
                            </fo:table-row>
                        </fo:table-body>          
                    </fo:table> 
                </fo:flow>
            </fo:page-sequence>
        </fo:root>
        
     </xsl:template> 
    
   
    <xsl:template name="line">
        <fo:table >
            
            <fo:table-body >
                <xsl:apply-templates
                    select="cXML/Request/Header"/>
                
            </fo:table-body>
        </fo:table>
    </xsl:template>
    
    <xsl:template match="Header" name="pallet">
        
        <xsl:for-each select="Level">
        <xsl:for-each select="Item">
            
            <fo:table-row>
                <fo:table-cell>
                    
                    <fo:block><xsl:value-of select="Items[ItemLevel='3']/ItemType"/></fo:block>
                </fo:table-cell>
                <fo:table-cell start-indent="25mm">
                    <xsl:variable name="unitNetWeight">
                        <xsl:value-of select="Items[ItemLevel='3'][1]/Dimension[@type='unitNetWeight']/@quantity"/>
                    </xsl:variable>
                    <xsl:variable name="quantity">
                        
                        <xsl:call-template name="TotalAmtAuxiliary">
                            <xsl:with-param name="nodes" select="Items[ItemLevel='3']" />
                            </xsl:call-template>
                        
                    </xsl:variable>
                   
                    <fo:block><xsl:value-of select="$unitNetWeight*$quantity"/></fo:block>
                </fo:table-cell>
            </fo:table-row>
        </xsl:for-each>
        </xsl:for-each>
       <fo:table-row>
           <fo:table-cell start-indent="10mm">
               <fo:block>Total</fo:block>
           </fo:table-cell>
           <fo:table-cell start-indent="25mm">
               
               <xsl:variable name="Total">
                   <xsl:call-template name="TotalWeight">
                       <xsl:with-param name="nodes" select="Level/Item/Items[ItemLevel='3'][1]" />
                   </xsl:call-template>
               </xsl:variable>
               <fo:block><xsl:value-of select="$Total"></xsl:value-of></fo:block>
           </fo:table-cell>
       </fo:table-row>
    </xsl:template>
    
   
    
    <xsl:template name="TotalAmtAuxiliary">
        <xsl:param name="nodes"/>
        <xsl:param name="sum" select="0" />
        
        <xsl:variable name="current" select="$nodes[1]" />
        
        <xsl:if test="$current"> 
            <xsl:call-template name="TotalAmtAuxiliary">
                <xsl:with-param name="nodes" select="$nodes[position() &gt; 1]" />
                <xsl:with-param name="sum" select="$sum + $current/DispatchQuantity/@quantity " />
            </xsl:call-template>
        </xsl:if>
        
        <xsl:if test="not($current)">
            <xsl:value-of select="$sum" />
        </xsl:if>
    </xsl:template>
    
    
    
    <xsl:template name="TotalWeight">
        <xsl:param name="nodes"/>
        <xsl:param name="sum" select="0" />
        
        <xsl:variable name="current" select="$nodes[1]" />
        
        
        <xsl:if test="$current"> 
            <xsl:call-template name="TotalWeight">
                <xsl:with-param name="nodes" select="$nodes[position() &gt; 1]" />
                
                
                <xsl:with-param name="sum" select="$sum + ($current/Dimension[@type='unitNetWeight']/@quantity) * ($current/ancestor::Item/Items[ItemLevel='3']//DispatchQuantity/@quantity)" />
            </xsl:call-template>
        </xsl:if>
        
        <xsl:if test="not($current)">
            <xsl:value-of select="$sum" />
        </xsl:if>
        
    </xsl:template>
        
        
</xsl:stylesheet>
Solution

  • Akash, please check if this solution works for you.

    <?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0"
            xmlns:xs="http://www.w3.org/2001/XMLSchema"
            xmlns:fo="http://www.w3.org/1999/XSL/Format">

<xsl:decimal-format name="generalFormat" grouping-separator="," decimal-separator="." />

<xsl:template match="/" name="Barcode">
    <fo:root>
        <fo:layout-master-set>
            <fo:simple-page-master master-name="ManufacturLabelSize-first" page-height="100mm" page-width="100mm"  >
                <fo:region-body margin-top="15mm" />
                <fo:region-before />
                <fo:region-after />
            </fo:simple-page-master>
            <fo:simple-page-master master-name="ManufacturLabelSize-rest" page-height="100mm" page-width="100mm"   >
                <fo:region-body margin-top="15mm"/>
                <fo:region-before />
                <fo:region-after />
            </fo:simple-page-master>
            <fo:page-sequence-master master-name="ManufacturLabelSize-portrait">
                <fo:repeatable-page-master-alternatives>
                    <fo:conditional-page-master-reference master-reference="ManufacturLabelSize-first"
                                                          page-position="first"/>
                    <fo:conditional-page-master-reference master-reference="ManufacturLabelSize-rest"
                                                          page-position="rest"/>
                </fo:repeatable-page-master-alternatives>
            </fo:page-sequence-master>
        </fo:layout-master-set>

        <fo:page-sequence master-reference="ManufacturLabelSize-portrait" id="pSeqID">
            <fo:flow flow-name="xsl-region-body">
                <fo:table>
                    <fo:table-body>
                        <fo:table-row>
                            <fo:table-cell><fo:block>Descp:</fo:block></fo:table-cell>
                            <fo:table-cell> <fo:block >Weight:</fo:block></fo:table-cell>
                        </fo:table-row>
                        <fo:table-row>
                            <fo:table-cell>
                                <fo:block>
                                    <xsl:call-template name="line"></xsl:call-template>
                                </fo:block>
                            </fo:table-cell>
                        </fo:table-row>
                    </fo:table-body>
                </fo:table>
            </fo:flow>
        </fo:page-sequence>
    </fo:root>
</xsl:template>

<xsl:template name="line">
    <fo:table>
        <fo:table-body>
            <xsl:apply-templates select="cXML/Request/Header"/>
        </fo:table-body>
    </fo:table>
</xsl:template>

<xsl:template match="Header" name="pallet">
    <xsl:for-each select="Level/Item/Items[ItemLevel='3']">
        <fo:table-row>
            <fo:table-cell>
                <fo:block>
                    <xsl:value-of select="ItemType"/>
                </fo:block>
            </fo:table-cell>
            <fo:table-cell start-indent="25mm">
                <xsl:variable name="unitNetWeight" select="Dimension[@type='unitNetWeight']/@quantity"/>
                <xsl:variable name="quantity" select="DispatchQuantity/@quantity"/>
                <fo:block>
                    <xsl:value-of select="$unitNetWeight * $quantity"/>
                </fo:block>
            </fo:table-cell>
        </fo:table-row>
    </xsl:for-each>
    <fo:table-row>
        <fo:table-cell start-indent="10mm">
            <fo:block>Total</fo:block>
        </fo:table-cell>
        <fo:table-cell start-indent="25mm">
            <xsl:variable name="Total">
                <xsl:call-template name="TotalWeight">
                    <xsl:with-param name="nodes" select="Level/Item/Items[ItemLevel='3']" />
                </xsl:call-template>
            </xsl:variable>
            <fo:block><xsl:value-of select="$Total"/></fo:block>
        </fo:table-cell>
    </fo:table-row>
</xsl:template>

<xsl:template name="TotalWeight">
    <xsl:param name="nodes" />
    <xsl:param name="sum" select="0" />
    <xsl:choose>
        <xsl:when test="not($nodes)">
            <xsl:value-of select="$sum" />
        </xsl:when>
        <xsl:otherwise>
            <xsl:variable name="product" select="$nodes[1]/Dimension[@type='unitNetWeight']/@quantity * $nodes[1]/DispatchQuantity/@quantity" />
            <xsl:call-template name="TotalWeight">
                <xsl:with-param name="nodes" select="$nodes[position() > 1]" />
                <xsl:with-param name="sum" select="$sum + $product" />
            </xsl:call-template>
        </xsl:otherwise>
    </xsl:choose>
</xsl:template>
</xsl:stylesheet>

    My output:

    <?xml version="1.0" encoding="UTF-8"?>
<fo:root xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:fo="http://www.w3.org/1999/XSL/Format">
<fo:layout-master-set>
    <fo:simple-page-master master-name="ManufacturLabelSize-first" page-height="100mm" page-width="100mm">
        <fo:region-body margin-top="15mm"/>
        <fo:region-before/>
        <fo:region-after/>
    </fo:simple-page-master>
    <fo:simple-page-master master-name="ManufacturLabelSize-rest" page-height="100mm" page-width="100mm">
        <fo:region-body margin-top="15mm"/>
        <fo:region-before/>
        <fo:region-after/>
    </fo:simple-page-master>
    <fo:page-sequence-master master-name="ManufacturLabelSize-portrait">
        <fo:repeatable-page-master-alternatives>
            <fo:conditional-page-master-reference master-reference="ManufacturLabelSize-first"
                                                  page-position="first"/>
            <fo:conditional-page-master-reference master-reference="ManufacturLabelSize-rest" page-position="rest"/>
        </fo:repeatable-page-master-alternatives>
    </fo:page-sequence-master>
</fo:layout-master-set>
<fo:page-sequence master-reference="ManufacturLabelSize-portrait" id="pSeqID">
    <fo:flow flow-name="xsl-region-body">
        <fo:table>
            <fo:table-body>
                <fo:table-row>
                    <fo:table-cell>
                        <fo:block>Descp:</fo:block>
                    </fo:table-cell>
                    <fo:table-cell>
                        <fo:block>Weight:</fo:block>
                    </fo:table-cell>
                </fo:table-row>
                <fo:table-row>
                    <fo:table-cell>
                        <fo:block>
                            <fo:table>
                                <fo:table-body>
                                    <fo:table-row>
                                        <fo:table-cell>
                                            <fo:block>Pencil</fo:block>
                                        </fo:table-cell>
                                        <fo:table-cell start-indent="25mm">
                                            <fo:block>100</fo:block>
                                        </fo:table-cell>
                                    </fo:table-row>
                                    <fo:table-row>
                                        <fo:table-cell>
                                            <fo:block>Clock</fo:block>
                                        </fo:table-cell>
                                        <fo:table-cell start-indent="25mm">
                                            <fo:block>300</fo:block>
                                        </fo:table-cell>
                                    </fo:table-row>
                                    <fo:table-row>
                                        <fo:table-cell>
                                            <fo:block>Box</fo:block>
                                        </fo:table-cell>
                                        <fo:table-cell start-indent="25mm">
                                            <fo:block>400</fo:block>
                                        </fo:table-cell>
                                    </fo:table-row>
                                    <fo:table-row>
                                        <fo:table-cell>
                                            <fo:block>Pen</fo:block>
                                        </fo:table-cell>
                                        <fo:table-cell start-indent="25mm">
                                            <fo:block>200</fo:block>
                                        </fo:table-cell>
                                    </fo:table-row>
                                    <fo:table-row>
                                        <fo:table-cell>
                                            <fo:block>Carton</fo:block>
                                        </fo:table-cell>
                                        <fo:table-cell start-indent="25mm">
                                            <fo:block>200</fo:block>
                                        </fo:table-cell>
                                    </fo:table-row>
                                    <fo:table-row>
                                        <fo:table-cell start-indent="10mm">
                                            <fo:block>Total</fo:block>
                                        </fo:table-cell>
                                        <fo:table-cell start-indent="25mm">
                                            <fo:block>1200</fo:block>
                                        </fo:table-cell>
                                    </fo:table-row>
                                </fo:table-body>
                            </fo:table>
                        </fo:block>
                    </fo:table-cell>
                </fo:table-row>
            </fo:table-body>
        </fo:table>
    </fo:flow>
</fo:page-sequence>
</fo:root>

    Best regards, Vasyl