CODEWITHSUNDEEP
xmltextxslt-1.0groupingdistinct-values
Anna Joel
Anna Joel

Reputation: 107

Group within a Group XSLT 1.0 with Sum & Count of Distinct Values

I would like to implement a Group by function within Current-Group using XSLT 1.0...I have managed to get the top level grouping based on "Organization", but a sub group within using "Covers"; I have failed at :(

(http://xsltransform.net/pPzifpL/16) has the XML & XSLT setup currently used.

I also need to apply a Sum(Current-Group()/SumInsured) and a count of distinct(Addenda), but this is not supported with 1.0...Please help me experts.

Expected Output: (Text)

Client Sum Insured Report

UK
SNo.    Policy Number   Customer Name   Cover Note #    No. of Addendas Sum Insured Total Commission
 1       POL1           ABC             50242           2               40000       65
 2       POL2           XYZ             12345           1               30000       30
Totals :                                                                70000       95

US
SNo.    Policy Number   Customer Name   Cover Note #    No. of Addendas Sum Insured Total Commission
 1                      JKL             45678           0               10000       10
Totals :                                                                10000       10

XML:

<?xml version="1.0" encoding="UTF-8"?><?Siebel-Property-Set EscapeNames="true"?><SiebelMessage MessageId="1-OC05" IntObjectName="Client Sum Insured Report IO" MessageType="Integration Object" IntObjectFormat="Siebel Hierarchical">
<ListOfClientSumInsuredReportIo>
    <GroupPolicies>
     <Addenda>50242-1</Addenda>
     <CommAmt>50</CommAmt>
     <Cover>50242</Cover>
     <Customer>ABC</Customer>
     <Policy>POL1</Policy>
     <SumInsured>10000</SumInsured>
     <Organization>UK</Organization>
    </GroupPolicies>
    <GroupPolicies>
     <Addenda>50242-2</Addenda>
     <CommAmt>5</CommAmt>
     <Cover>50242</Cover>
     <Customer>ABC</Customer>
     <Policy>POL1</Policy>
     <SumInsured>20000</SumInsured>
     <Organization>UK</Organization>
    </GroupPolicies>
    <GroupPolicies>
     <Addenda></Addenda>
     <CommAmt>10</CommAmt>
     <Cover>50242</Cover>
     <Customer>ABC</Customer>
     <Policy>POL1</Policy>
     <SumInsured>10000</SumInsured>
     <Organization>UK</Organization>
    </GroupPolicies>
    <GroupPolicies>
     <Addenda>12345-1</Addenda>
     <CommAmt>20</CommAmt>
     <Cover>12345</Cover>
     <Customer>XYZ</Customer>
     <Policy>POL2</Policy>
     <SumInsured>20000</SumInsured>
     <Organization>UK</Organization>
    </GroupPolicies>
    <GroupPolicies>
     <Addenda></Addenda>
     <CommAmt>10</CommAmt>
     <Cover>12345</Cover>
     <Customer>XYZ</Customer>
     <Policy>POL2</Policy>
     <SumInsured>10000</SumInsured>
     <Organization>UK</Organization>
    </GroupPolicies>
    <GroupPolicies>
     <Addenda></Addenda>
     <CommAmt>10</CommAmt>
     <Cover>45678</Cover>
     <Customer>JKL</Customer>
     <Policy></Policy>
     <SumInsured>10000</SumInsured>
     <Organization>US</Organization>
    </GroupPolicies>
</ListOfClientSumInsuredReportIo>

XSLT Used:

<?xml version="1.0" encoding="utf-8"?>
   <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
     <xsl:output method="text" indent="yes"/>      
      <xsl:template match="/SiebelMessage/ListOfClientSumInsuredReportIo">
  <xsl:text>&#09;&#09;</xsl:text>
  <xsl:text>Client Sum Insured Report</xsl:text>
  <xsl:text>&#10;</xsl:text>
        <xsl:apply-templates select="GroupPolicies[not(preceding-sibling::GroupPolicies/Organization = Organization)]/Organization" />
  </xsl:template>

  <xsl:template match="GroupPolicies" >
        <xsl:variable name="count" select="position()"/>
        <!--xsl:value-of select="{$count}"/-->
        <xsl:text>&#09;</xsl:text>
        <xsl:value-of select="Policy"/>
        <xsl:text>&#09;</xsl:text>
        <xsl:value-of select="Customer"/>
        <xsl:text>&#09;</xsl:text>
        <xsl:value-of select="Cover"/>
        <xsl:text>&#09;</xsl:text>
        <xsl:value-of select="Addenda"/> <!-- Getting Distinct Count of Addendas while it is not blank-->
        <xsl:text>&#09;</xsl:text>
        <xsl:value-of select="SumInsured"/>
        <xsl:text>&#09;</xsl:text>
        <xsl:value-of select="CommAmt"/>   
        <xsl:text>&#10;</xsl:text>            
  </xsl:template>        

  <xsl:template match="Organization" >
        <xsl:text>&#10;</xsl:text>
        <xsl:text>SNo.</xsl:text>
        <xsl:text>&#09;</xsl:text>
        <xsl:text>Policy Number</xsl:text>
        <xsl:text>&#09;</xsl:text>
        <xsl:text>Customer Name</xsl:text>
        <xsl:text>&#09;</xsl:text>
        <xsl:text>Cover Note #</xsl:text>
        <xsl:text>&#09;</xsl:text>
        <xsl:text>No. of Addendas</xsl:text>
        <xsl:text>&#09;</xsl:text>
        <xsl:text>Sum Insured</xsl:text>
        <xsl:text>&#09;</xsl:text>
        <xsl:text>Total Commission</xsl:text>
        <xsl:text>&#10;</xsl:text>

     <xsl:variable name="temp" select="." />         
     <xsl:apply-templates select="//GroupPolicies[Organization = current()]" />

     </xsl:template>
   </xsl:stylesheet>

Thank you for any suggestions.

Upvotes: 0

Views: 750

Answers (1)

Matthew
Matthew

Reputation: 7590

Change (line 47 on your link)

<xsl:apply-templates select="//GroupPolicies[Organization = current()]" />

to

<xsl:apply-templates select="//GroupPolicies[Organization = current() and not(preceding-sibling::GroupPolicies/Cover=Cover)]" />

Change (line 21, 23, 25)

<xsl:value-of select="Addenda"/>
<xsl:value-of select="SumInsured"/>
<xsl:value-of select="CommAmt"/>

to

<xsl:value-of select="count(../GroupPolicies[Organization=current()/Organization and Cover=current()/Cover]/Addenda[string-length()&gt;0])"/>
<xsl:value-of select="sum(../GroupPolicies[Organization=current()/Organization and Cover=current()/Cover]/SumInsured)"/>
<xsl:value-of select="sum(../GroupPolicies[Organization=current()/Organization and Cover=current()/Cover]/CommAmt)"/>

Change (line 13)

<!--xsl:value-of select="{$count}"/-->

to

<xsl:value-of select="$count"/>

You'll need to clean up the spacing/formatting a little bit more, but this produces exactly what your desired output calls for.

Edited to add additional requirements asked about by the OP in comment

To add the organization to the top of the tables, just add

<xsl:value-of select="."/>

to the top of the Organization template.

To add per table totals add the following lines

<xsl:value-of select="sum(../../GroupPolicies[Organization=current()]/SumInsured)"/>
<xsl:value-of select="sum(../../GroupPolicies[Organization=current()]/CommAmt)"/>

to the bottom of the organization template (after the apply-templates call), with the appropriate spacing and carriage return formatting.

Upvotes: 2

Related Questions