?
Solved

XSLT and summing at a group level

Posted on 2007-03-22
7
Medium Priority
?
1,134 Views
Last Modified: 2013-11-18
I'm trying to perform an XSL Transformation on XML from a dataset that will give me results similar to a Pivot Table/Cross Table.

The idea is to group the data by Product, Brand and Sum the quantity by month and Total them by Brand - eg:
 
Product        Brand            2006 Jan              2006 Feb            2006 Mar            Total Units
------------------------------------------------------------------------------------------------------------
DeskTop  
                     M100                  2                           3                                                    5         
*** total                  2                           3                                                    5         
Laptop                                        
                     M100                  2                                                      1                         3         
                     P100                   5                                                      1                         6         
*** total                                    7                                                      2                         9
-------------------------------------------------------------------------------------------------------         
Total                                                                                                                    14       

The trouble I'm having is summing the data per month and keeping the correct data grouped.

I'd appreciate if someone could put me on the right path for dealing with this type of query, an help me out in accessing
correct SystemQuantity to group

Here's what I have so far;

XML:
<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <ReportingBusUnitDesc>Ireland</ReportingBusUnitDesc>
    <OrderDate>2006-03-08T00:00:00+00:00</OrderDate>
    <ProductDescription>Laptop</ProductDescription>
    <BrandDescription>M100</BrandDescription>
    <SystemQuantity>1</SystemQuantity>
    <TotalRevenue>1000</TotalRevenue>
    <OrderDateFormatted>200603</OrderDateFormatted>
  </Table>
  <Table>
    <ReportingBusUnitDesc>Ireland</ReportingBusUnitDesc>
    <OrderDate>2006-03-08T00:00:00+00:00</OrderDate>
    <ProductDescription>Laptop</ProductDescription>
    <BrandDescription>M100</BrandDescription>
    <SystemQuantity>2</SystemQuantity>
    <TotalRevenue>2000</TotalRevenue>
    <OrderDateFormatted>200601</OrderDateFormatted>
  </Table>
  <Table>
    <ReportingBusUnitDesc>Ireland</ReportingBusUnitDesc>
    <OrderDate>2006-03-08T00:00:00+00:00</OrderDate>
    <ProductDescription>Laptop</ProductDescription>
    <BrandDescription>P100</BrandDescription>
    <SystemQuantity>1</SystemQuantity>
    <TotalRevenue>1000</TotalRevenue>
    <OrderDateFormatted>200603</OrderDateFormatted>
  </Table>
  <Table>
    <ReportingBusUnitDesc>Ireland</ReportingBusUnitDesc>
    <OrderDate>2006-03-08T00:00:00+00:00</OrderDate>
    <ProductDescription>Laptop</ProductDescription>
    <BrandDescription>P100</BrandDescription>
    <SystemQuantity>2</SystemQuantity>
    <TotalRevenue>2000</TotalRevenue>
    <OrderDateFormatted>200601</OrderDateFormatted>
  </Table>
  <Table>
    <ReportingBusUnitDesc>Ireland</ReportingBusUnitDesc>
    <OrderDate>2006-03-08T00:00:00+00:00</OrderDate>
    <ProductDescription>Laptop</ProductDescription>
    <BrandDescription>P100</BrandDescription>
    <SystemQuantity>3</SystemQuantity>
    <TotalRevenue>3000</TotalRevenue>
    <OrderDateFormatted>200601</OrderDateFormatted>
  </Table>
  <Table>
    <ReportingBusUnitDesc>Ireland</ReportingBusUnitDesc>
    <OrderDate>2006-03-08T00:00:00+00:00</OrderDate>
    <ProductDescription>DeskTop</ProductDescription>
    <BrandDescription>P100</BrandDescription>
    <SystemQuantity>2</SystemQuantity>
    <TotalRevenue>2000</TotalRevenue>
    <OrderDateFormatted>200602</OrderDateFormatted>
  </Table>
  <Table>
    <ReportingBusUnitDesc>Ireland</ReportingBusUnitDesc>
    <OrderDate>2006-03-08T00:00:00+00:00</OrderDate>
    <ProductDescription>DeskTop</ProductDescription>
    <BrandDescription>P100</BrandDescription>
    <SystemQuantity>3</SystemQuantity>
    <TotalRevenue>3000</TotalRevenue>
    <OrderDateFormatted>200601</OrderDateFormatted>
  </Table>
</NewDataSet>

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

  <!--**********************************************************************-->
  <!-- Variables-->
  <!--**********************************************************************-->
  <xsl:param name="sCountry"/>
  <xsl:param name="sCustomer"/>
  <xsl:param name="sFromDate"/>
  <xsl:param name="sToDate"/>
  <xsl:param name="sProductDesc"/>
  <xsl:param name="sBrandDesc"/>

  <!--**********************************************************************-->
  <!-- Keys-->
  <!--**********************************************************************-->
  <xsl:key name="keyProductDesc" match="Table" use="ProductDescription"/>
  <xsl:key name="keyBrandDesc" match="Table" use="BrandDescription"/>
  <xsl:key name="keyOrderDateFormatted" match="Table" use="OrderDateFormatted"/>

  <!--**********************************************************************-->
  <!-- Entry Point-->
  <!--**********************************************************************-->
  <xsl:template match="/">
    <xsl:value-of select="sFromDate"/>
    <!-- Insert Data Table -->
    <xsl:call-template name="tableMain" />
  </xsl:template>

  <!--**********************************************************************-->
  <!-- Main Table -->
  <!--**********************************************************************-->
  <xsl:template name="tableMain">
    <br />
    <table border="0" cellpadding="0" cellspacing="0" width="600">
      <!-- Insert the Table Header -->
      <xsl:call-template name="tableHeader" />
      <!-- Insert the body of the table -->
      <xsl:call-template name="tableBody" />
      <!-- insert the footer of the table-->
      <xsl:call-template name="tableFooter" />
    </table>
  </xsl:template>

  <!--**********************************************************************-->
  <!-- Main Table Headers-->
  <!--**********************************************************************-->
   <xsl:template name="tableHeader">
    <tr>
      <td class="header" width="150">
        Product Description
      </td>
      <td class="header" width="150">
        Brand Description
      </td>
      <!-- insert the Cross-Tab Headers of the table-->
      <xsl:call-template name="tableCrossTabHeaders" />
      <td class="header" width="150">
        Total Units
      </td>
    </tr>
  </xsl:template>

  <!--**********************************************************************-->
  <!-- Main Table Cross Tab Headers-->
  <!--**********************************************************************-->
  <xsl:template name="tableCrossTabHeaders">
    <xsl:for-each select="NewDataSet/Table[generate-id(.) = generate-id(key('keyOrderDateFormatted', OrderDateFormatted))]">
      <xsl:sort select="OrderDateFormatted"/>
      <td class="header" width="50">
         <xsl:call-template name="FormatDateYYYYMMM">
          <xsl:with-param name="DateTime" select="OrderDateFormatted"/>
        </xsl:call-template>
      </td>
    </xsl:for-each>
  </xsl:template>

  <!--**********************************************************************-->
  <!-- Main Table Body and Data-->
  <!--**********************************************************************-->
  <xsl:template name="tableBody">
    <xsl:for-each select="NewDataSet/Table[generate-id(.) = generate-id(key('keyProductDesc', ProductDescription)[1])]">
      <!--Insert the Product Seperator-->
      <xsl:sort select="ProductDescription"/>
      <tr>
        <td class="columnTextLeft" colspan="3">
          <xsl:value-of select="ProductDescription"/>
        </td>
      </tr>
      <!--Insert the Brands-->
      <xsl:variable name="varProductDesc">
        <xsl:value-of select="ProductDescription" />
      </xsl:variable>
      <!-- Select all the Brands belonging to the Product -->
      <xsl:variable name="lstBrandDesc" select="//Table[ProductDescription=$varProductDesc]" />
      <!-- Show details for Brands in Product -->
      <xsl:call-template name="insertBrands">
        <xsl:with-param name="lstBrandDesc" select="$lstBrandDesc" />
      </xsl:call-template>

      <!--Insert the Sub Total for the Row-->
      <tr>
        <td class="subFooter">
          *** total
        </td>
        <td class="subFooter">

        </td>
        <td class="subFooterRight">
          <xsl:value-of select="sum($lstBrandDesc[ProductDescription=$varProductDesc]/SystemQuantity)" />
        </td>
      </tr>

      <!-- Insert the Brands -->
    </xsl:for-each>
  </xsl:template>

  <!--**********************************************************************-->
  <!-- Main Table Body - Insert a row for each Brand in that product-->
  <!--**********************************************************************-->
  <xsl:template name="insertBrands">
    <xsl:param name="lstBrandDesc" />
    <!-- Show the total hours for each Employee in the Team -->
    <xsl:for-each select="$lstBrandDesc[generate-id(.) = generate-id(key('keyBrandDesc', BrandDescription))]">

      <xsl:variable name="varBrandDesc" select="BrandDescription" />
      <!-- Show details of each Employee -->
      <tr>
        <td class="columnTextLeft">
        </td>
        <td class="columnData">
          <xsl:value-of select="$lstBrandDesc[BrandDescription=$varBrandDesc]/BrandDescription" />
        </td>
        <!-- insert the Cross-Tab Data of the table-->
        <xsl:call-template name="insertCrossTabData">
          <xsl:with-param name="lstBrandDesc" select="$lstBrandDesc" />
        </xsl:call-template>
       
        <td class="columnData">
          <xsl:value-of select="sum($lstBrandDesc[BrandDescription=$varBrandDesc]/SystemQuantity)" />
        </td>
      </tr>
    </xsl:for-each>
  </xsl:template>

  <!--**********************************************************************-->
  <!-- Main Table Body - Insert a column for each Month and Sum the Units for that Month -->
  <!--**********************************************************************-->
  <xsl:template name="insertCrossTabData">
    <xsl:param name="lstBrandDesc" />
    <xsl:variable name="var" select="OrderDateFormatted" />

    <xsl:for-each select="//Table[generate-id(.) = generate-id(key('keyOrderDateFormatted', OrderDateFormatted))]">
      <xsl:sort select="OrderDateFormatted"/>
      <td class="columnData" width="50">
        <xsl:value-of select="$lstBrandDesc[current()/OrderDateFormatted=$var]/SystemQuantity" />
        <!--xsl:value-of select="sum($lstBrandDesc[OrderDateFormatted='2007 Jan']/SystemQuantity)" /-->
      </td>
    </xsl:for-each>
  </xsl:template>

  <!--**********************************************************************-->
  <!-- Main Table Body - Insert the footer of the table, calculating the totals of quantities and revenue-->
  <!--**********************************************************************-->
  <xsl:template name="tableFooter">
    <tr>
      <td class="footer">
        Total
      </td>
      <td class="footer" >
        _
      </td>
      <td class="footer">
        <xsl:value-of select="sum(/NewDataSet/Table/SystemQuantity)" />
      </td>
    </tr>
  </xsl:template>

  <!--**********************************************************************-->
  <!-- Utility Function -->
  <!-- Format the date from yyyy-mo-ddTss:mm to mmm yyyy -->
  <!--**********************************************************************-->
  <xsl:template name="FormatDateYYYYMMM">
    <xsl:param name="DateTime" />
    <!-- new date format 2006-01-14T08:55:22 -->
    <xsl:variable name="year">
      <xsl:value-of select="substring($DateTime,1,4)" />
    </xsl:variable>
    <xsl:variable name="month">
      <xsl:value-of select="substring($DateTime,5,2)" />
    </xsl:variable>
    <xsl:value-of select="$year"/>
    <br />
    <xsl:choose>
      <xsl:when test="$month = '01'">Jan</xsl:when>
      <xsl:when test="$month = '02'">Feb</xsl:when>
      <xsl:when test="$month = '03'">Mar</xsl:when>
      <xsl:when test="$month = '04'">Apr</xsl:when>
      <xsl:when test="$month = '05'">May</xsl:when>
      <xsl:when test="$month = '06'">Jun</xsl:when>
      <xsl:when test="$month = '07'">Jul</xsl:when>
      <xsl:when test="$month = '08'">Aug</xsl:when>
      <xsl:when test="$month = '09'">Sep</xsl:when>
      <xsl:when test="$month = '10'">Oct</xsl:when>
      <xsl:when test="$month = '11'">Nov</xsl:when>
      <xsl:when test="$month = '12'">Dec</xsl:when>
    </xsl:choose>
  </xsl:template>

</xsl:stylesheet>
0
Comment
Question by:johnaryan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 2000 total points
ID: 18772177
I would restructure and make the table generation a real nested Muenchian

If you store the current triggers (such as Brand and Product) in a variable,
you have constant access to the states

I wrapped up an example, it should be pretty easy to make it as you please
Pay attention to the predicates with the keys and the use of variables

cheers

Geert

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
   
    <!--**********************************************************************-->
    <!-- Keys-->
    <!--**********************************************************************-->
    <xsl:key name="keyProductDesc" match="Table" use="ProductDescription"/>
    <xsl:key name="keyBrandDesc" match="Table" use="BrandDescription"/>
    <xsl:key name="keyOrderDateFormatted" match="Table" use="OrderDateFormatted"/>
   
    <!--**********************************************************************-->
    <!-- Entry Point-->
    <!--**********************************************************************-->
    <xsl:template match="/">
        <table border="1">
            <xsl:for-each select="/NewDataSet/Table[generate-id() = generate-id(key('keyProductDesc', ProductDescription)[1])]">
                <xsl:variable name="curProd" select="ProductDescription"/>
                <tr><td colspan="5"><b><xsl:value-of select="$curProd"/></b></td></tr>
                <xsl:for-each select="/NewDataSet/Table[generate-id() = generate-id(key('keyBrandDesc', BrandDescription)[ProductDescription = $curProd][1])]">
                    <xsl:variable name="curBrand" select="BrandDescription"/>
                    <tr><td><xsl:value-of select="$curBrand"/></td>
                        <xsl:for-each select="/NewDataSet/Table[generate-id() = generate-id(key('keyOrderDateFormatted', OrderDateFormatted)[1])]">
                            <xsl:sort order="ascending" select="OrderDateFormatted" data-type="number"/>
                            <xsl:variable name="curDate" select="OrderDateFormatted"/>
                            <td>
                                <xsl:value-of select="sum(key('keyOrderDateFormatted', $curDate)[BrandDescription = $curBrand][ProductDescription = $curProd]/SystemQuantity)"/>
                            </td>
                         </xsl:for-each>
                        <td><b>
                            <xsl:value-of select="sum(key('keyBrandDesc', $curBrand)[ProductDescription = $curProd]/SystemQuantity)"/>
                        </b></td>
                    </tr>
                </xsl:for-each>
                <tr>
                    <td>Total</td>
                    <xsl:for-each select="/NewDataSet/Table[generate-id() = generate-id(key('keyOrderDateFormatted', OrderDateFormatted)[1])]">
                        <xsl:sort order="ascending" select="OrderDateFormatted" data-type="number"/>
                        <xsl:variable name="curDate" select="OrderDateFormatted"/>
                        <td><b>
                            <xsl:value-of select="sum(key('keyOrderDateFormatted', $curDate)[ProductDescription = $curProd]/SystemQuantity)"/>
                        </b></td>
                    </xsl:for-each>
                    <td><b><i><xsl:value-of select="sum(key('keyProductDesc', $curProd)/SystemQuantity)"/></i></b></td>
                </tr>
            </xsl:for-each>
        </table>
     </xsl:template>
   
   
</xsl:stylesheet>
0
 
LVL 5

Author Comment

by:johnaryan
ID: 18779556
Hi Geert,

That was exactly what I wanted to achieve, my lack of understanding of the proper use of key and predicates being my downfall.
I do have one issue with the solution you provided, when I scaled it up to process a 4000 row XML file it slowed up drastically.

Do you have any recommendations for optimizations.

Thanks,
John
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 18779654
Hi John,

I wouldn't know how to optimise this.
In XSLT 1.0, Muenchian is about as fast as you can go,
so you might twiddle some things to optimise it but it will not improve anything seriously

You might run a two step, making some calculations beforehand and cache the 1st step
I don't know how often your XML changes

If it slows down at 4000, how much rows do you really need? 10000? even more?

There is a possibility to migrate everything to using XSLT2.0 and start using Saxon8.9.net
(www.saxonica.com) but that is a bit a drastic measure

cheers

Geert
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 5

Author Comment

by:johnaryan
ID: 18779686
Would XSLT2.0 offer that much in a performance gain? I don't know what parser I'm using (and not sure how to find out.)
I'm developing in VS2005 using the .Net Framework 2.0
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 18779819
well, you are using the microsoft .net parser.
I didn't do bench-marking that intensive,
but XSLT2 has built-in support for grouping (xsl:for-each-group)
and that seems to be quiet a bit faster

I think, unless you reorganise your process in multiple steps,
and if it is important that you optimise the process,
that it might be worthwhile spending some time exploring XSLT2 and Saxon.net

Are you sure that the delay is in the stylesheet, and not in the steps generating the XML?

If you have the XML as a static file on your system,
then I suggest that you preprocess it to add the totals in the XML already.
You could add a process that does the 1st transform each time the new XML is generated

If you generate the XML from a database realtime, you might consider to do the calculus in the database
and create a slightly different XML

Bottomline is
If the XML can't be changed and you need to do all the calculus in this one XSLT, you will not be able to improve much in the single XSLT, unless exploring XSLT2.0.
If changes outside the XSLT are possible, I would seriously consider those options

good luck

Geert

PS. was your first attempt considerably faster?
0
 
LVL 5

Author Comment

by:johnaryan
ID: 18781474
Unfortunately I don't have access to the data to change it at source as it's provided by a Web Service. I could restructure it after I get the DataSet, but I used XSLT so I wouldn't have to do that. I can request more fields to be returned - would it be quicker to create keys off "int" fields rather than the "String" fields I'm now working off.

I'll look into the "xsl:for-each-group" because I don't see the data getting any smaller.

Thanks Again,
John
0
 
LVL 60

Expert Comment

by:Geert Bormans
ID: 18781635
welcome

XSLT has no notion of datatypes
XSLT2.0 has, so maybe the int could be faster in XSLT2, not in XSLT1

cheers

Geert
0

Featured Post

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question