XSLT and summing at a group level

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>
LVL 5
johnaryanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Geert BormansInformation ArchitectCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnaryanAuthor Commented:
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
Geert BormansInformation ArchitectCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

johnaryanAuthor Commented:
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
Geert BormansInformation ArchitectCommented:
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
johnaryanAuthor Commented:
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
Geert BormansInformation ArchitectCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Languages and Standards

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.