XSLT – How to Sort – Group By and Sum

XSLT is really powerful language when it comes to XML transformations but as we already know transformation is not the only thing that XSLT does. It can do a lot more than that. I had chance to experience the power of XSLT while working on requirement that needed data to be Sorted, Grouped and Summed on the fly based on user choice. Below is the detailed requirement:

Requirement:

You have a list of Invoices which has following attributes

  • Invoice Number
  • Invoice Amount
  • Invoice Date
  • Customer Number
  • Serial Number
  • Purchase Order Number

These invoices need to be Sorted and Grouped by Serial Number, Customer Number or Purchase Order Number depending on user selection and they should display the sub totals followed by grand total in the end in a doc format. The example below would explain the requirement:

Raw Data:

Invoice Number Invoice Amount Invoice Date Serial Number Customer Number Purchase Order Number
1000 1000 1/1/2013 123456789 123456789 12345
1001 1001 1/2/2013 123456789 123456789 12345
1002 1002 1/3/2013 123456789 123456789 12345
1003 1003 1/4/2013 123456792 123456792 12346
1004 1004 1/5/2013 123456792 123456792 12347

Expected Output (Word Doc Format):

Customer Number Invoice Number Invoice Date Invoice Amount
123456789 1000 1/1/2013 1000
1001 1/2/2013 1001
1002 1/3/2013 1002
Total of Customer 123456789 3003
123456792 1003 1/4/2013 1003
1004 1/5/2013 1004
Total of Customer 123456792 2007
Grand Total 5010

 

This data needs to be in a doc file and attached to the record.

Now you can imagine the complexity of the code that needs be written if this was to be done using traditional eScript. Where as use of XSLT gave us a script less solution (sort of if we consider XSLT scripting as non scripting).

With the help of XSLT it became a Four step process

  1. Accept user input and create a search spec
  2. Query using EAI Siebel Adapter and get Invoice List as Siebel Message
  3. Use Appropriate XSLT template and get the required HTML file
  4. Save the HTML file with Doc extension and attach the file.

Below is the XSLT used to sort by Amount, Group and Total by PO Number. You just need to replace the Customer Number with Serial Number or PO Number as per user choice.

<?xml version=”1.0″ ?>
<xsl:stylesheet version=”1.0″ xmlns:xsl=”http://www.w3.org/1999/XSL/Transform” >
<xsl:key name=”cust” match=”FS_spcInvoice” use=”@Customer_spcNumber”/>
<xsl:template match=”/” >
<html>
<head>
</head>
<body>
<div id=”container”>
<div id=”InvoiceHeader” style=”font-size:25px; color:gray;font-weight:bold”>Invoice Summary</div>

<div id=”invoicelist”>
<table style”border:solid; width:100%”>
<thead style=”font-size:18px;color:gray”>
<tr><th>Customer Number</th><th>Invoice Number</th><th>Invoice Date</th><th>Invoice Amount</th></tr>
</thead>
<tbody>
<xsl:apply-templates select=”PropertySet/SiebelMessage/ListOfInvoice_spcSummary/FS_spcInvoice[generate-id(.)=generate-id(key(‘cust’,@Customer_spcNumber)[1])]”>
<xsl:sort select=”@Gross_spcAmount” order=”descending” />
</xsl:apply-templates>    <!– end of view –>
<tr><td colspan=”4″ style=”text-align:right; font-weight:bold;font-size:15px”>GRAND TOTAL</td><td colspan=”2″ style=”text-align:center; font-weight:bold;font-size:13px”><xsl:value-of select=”format-number(sum(PropertySet/SiebelMessage/ListOfInvoice_spcSummary/FS_spcInvoice/@Gross_spcAmount),’0.00′)”/> $</td></tr>
</tbody></table></div></div></body></html>
</xsl:template>

<xsl:template match=’FS_spcInvoice’>
<xsl:for-each select=”key(‘cust’, @Customer_spcNumber)”>
<tr>
<td><xsl:value-of select=”@Customer_spcNumber” /></td>
<td><xsl:value-of select=”@Invoice_spcNumber” /></td>
<td><xsl:value-of select=”@Invoice_spcDate” /></td>
<td style=”text-align:center”><xsl:value-of select=”@Gross_spcAmount” /> $</td>
</tr>
</xsl:for-each>
<tr><td colspan=”4″ style=”text-align:right; font-weight:bold;font-size:15px”>TOTAL for Customer<xsl:value-of select=”@Customer_spcNumber” /></td><td colspan=”2″ style=”text-align:center; font-weight:bold;font-size:13px”><xsl:value-of select=”format-number(sum(//FS_spcInvoice[@Customer_spcNumber= current()/@Customer_spcNumber]/@Gross_spcAmount),’0.00′)”/> $</td></tr>
</xsl:template>

</xsl:stylesheet>

Explanation of the XSLT in next post.

Note: I had to manually modify the XSLT in order to post it to Siebel Unleashed so in case if you encounter any issue while getting it to work, Please do let me know.

This Post has been viewed : 9,783 Times


3 Responses to XSLT – How to Sort – Group By and Sum

  1. Trying to solve one of the common req with XSLT :Duplicate Records
    Parent->Child->GrandChild
    With this hierarchy, find the duplicate records in Child and Grandchild of each child. (Note: Out of context search)
    BCHasRows is not an option since solution has to be done out of context.

    Looking at 2-3 solutions in XSLT , but still confused if MUENCHIAN method is the best solution to it as I am looking for a performance oriented soln.

Leave a Reply

Contribute