Solved

XSLT and summing at a group level

Posted on 2007-03-22
7
1,127 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
  • 4
  • 3
7 Comments
 
LVL 60

Accepted Solution

by:
Geert Bormans earned 500 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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…
The viewer will learn how to count occurrences of each item in an array.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now