[XSL-LIST Mailing List Archive Home] [By Thread] [By Date] [Recent Entries] [Reply To This Message]

Re: Pivot Reports

Subject: Re: Pivot Reports
From: "James A. Robinson" <jim.robinson@xxxxxxxxxxxx>
Date: Thu, 18 Jan 2007 09:52:28 -0800
stylesheet xsl
> First block is generated absolutely correct, all the next blocks contain 
> only combinations of rows and cols which haven't been met in any of 
> already generated blocks.

I hope I'm understanding what you want. I was a bit confused by the
ascii diagrams vs. what your XSLT was generating. I assume the XSLT is
building the pivot report (and I think I grok what a pivot table is...).
Here's my stab at solving the problem using XSLT 1.0 (I think this would
be easier to solve in 2.0 if you have access to that).

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

  <!-- keys by col name (e.g., C1, C2, ..., CN) -->
  <xsl:key name="col" match="col" use="." />

  <!-- keys by row name (e.g., R1, R2, ..., RN) -->
  <xsl:key name="row" match="row" use="." />

  <!-- keys for col per pivot -->
  <xsl:key name="pivot-cols" match="pivot/item/col"
    use="generate-id(ancestor::*[2])" />

  <!-- keys for row per pivot -->
  <xsl:key name="pivot-rows" match="pivot/item/row"
    use="generate-id(ancestor::*[2])" />

  <!-- total rows computed off the first pivot table -->
  <xsl:variable name="row-count">
    <xsl:call-template name="count-rows">
      <xsl:with-param name="rows" select="/notice/pivots/pivot[1]/item/row" />
      <xsl:with-param name="count" select="0" />
    </xsl:call-template>
  </xsl:variable>

  <xsl:template match="@*|node()" />

  <xsl:template match="/">
    <xsl:apply-templates select="/notice/pivots/pivot" />
  </xsl:template>

  <xsl:template match="pivot">

    <!-- user our generated id to determine which rows apply to this table -->
    <xsl:variable name="pivot-id" select="generate-id(.)" />
    <xsl:variable name="rows"
      select="key('pivot-rows', $pivot-id)[position() &lt;= $row-count]" />

    <table border="1" cellspacing="0" class="stn">
      <!-- emit the row headers -->
      <tr>
        <th>*</th>
        <xsl:for-each select="$rows">
          <th>
            <xsl:value-of select="." />
          </th>
        </xsl:for-each>
        <th>total</th>
      </tr>
      <!-- 
        process all items (though only unique columns will be processed by
        virtue of the predicate on the template matchin item)
      -->
      <xsl:apply-templates select="item" />
      <!-- compute column and grand totals -->
      <tr>
        <th>total</th>
        <xsl:for-each select="$rows">
          <td id="sum-{.}">
            <xsl:call-template name="sum">
              <xsl:with-param name="val" select="../../item[row=current()]/val" />
              <xsl:with-param name="sum" select="0" />
            </xsl:call-template>
          </td>
        </xsl:for-each>
        <td sum="grand-total">
          <xsl:call-template name="sum">
            <xsl:with-param name="val" select="item/val" />
            <xsl:with-param name="sum" select="0" />
          </xsl:call-template>
        </td>
      </tr>
    </table>
  </xsl:template>

  <!-- process each item when it is the first time the column has been seen -->
  <xsl:template match="item[not(col = preceding-sibling::item/col)]">

    <!-- use generated id of our pivot ancestor to determine our rows -->
    <xsl:variable name="pivot-id" select="generate-id(ancestor::*[1])" />
    <xsl:variable name="rows"
      select="key('pivot-rows', $pivot-id)[position() &lt;= $row-count]" />

    <!--
      since we are emitting a row of columns, select all item with matching
      column to the current item, and stick them in $items
    -->
    <xsl:variable name="items"
      select="key('pivot-cols', $pivot-id)[.=current()/col]/.." />

    <xsl:variable name="col" select="col" />
    <tr>
      <th>
        <xsl:value-of select="$col" />
      </th>
      <xsl:for-each select="$rows">
        <!-- for each row emit a column, using 0 if none exists -->
        <td id="{concat($col,'x',.)}">
          <xsl:variable name="val" select="$items[row=current()][col=$col]/val" />
          <xsl:choose>
            <xsl:when test="$val">
              <xsl:value-of select="$val" />
            </xsl:when>
            <xsl:otherwise>0</xsl:otherwise>
          </xsl:choose>
        </td>
      </xsl:for-each>
      <!-- and compute our total values -->
      <td class="sum-{$col}">
        <xsl:call-template name="sum">
          <xsl:with-param name="val" select="$items/val" />
          <xsl:with-param name="sum" select="0" />
        </xsl:call-template>
      </td>
    </tr>
  </xsl:template>
  
  <!--
    Count the number of unique row names in $rows
    this is used to determine when to stop processing
    rows returned by the pivot-rows key.

    param: $rows - row nodes from a pivot.
    param: $count - number of unique row values encountered so far.
    returns:  number of unique row values encountered in $rows.
  -->
  <xsl:template name="count-rows">
    <xsl:param name="rows" />
    <xsl:param name="count" />
    <xsl:choose>
      <xsl:when test="not($rows)">
        <xsl:value-of select="$count" />
      </xsl:when>
      <xsl:when test="generate-id($rows[1])=generate-id(key('row', $rows[1]))">
        <xsl:call-template name="count-rows">
          <xsl:with-param name="rows" select="$rows[position()!=1]" />
          <xsl:with-param name="count" select="$count+1" />
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="count-rows">
          <xsl:with-param name="rows" select="$rows[position()!=1]" />
          <xsl:with-param name="count" select="$count" />
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

  <!--
    Sum the values in $val
    
    param: $val a sequence of numbers to sum
    param: $sum the total so far
    returns: the sum of $val
  -->
  <xsl:template name="sum">
    <xsl:param name="val" />
    <xsl:param name="sum" />
    <xsl:choose>
      <xsl:when test="not($val)">
        <xsl:value-of select="$sum" />
      </xsl:when>
      <xsl:otherwise>
        <xsl:call-template name="sum">
          <xsl:with-param name="val" select="$val[position()!=1]" />
          <xsl:with-param name="sum" select="$sum + $val[1]" />
        </xsl:call-template>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>

</xsl:stylesheet>

What it builds is:

<?xml version="1.0" encoding="utf-8"?>
<table border="1" cellspacing="0" class="stn">
   <tr>
      <th>*</th>
      <th>R1</th>
      <th>R2</th>
      <th>total</th>
   </tr>

   <tr>
      <th>C1</th>
      <td id="C1xR1">1</td>
      <td id="C1xR2">3</td>
      <td class="sum-C1">4</td>
   </tr>
   <tr>

      <th>C2</th>
      <td id="C2xR1">5</td>
      <td id="C2xR2">2</td>
      <td class="sum-C2">7</td>
   </tr>
   <tr>
      <th>total</th>

      <td id="sum-R1">6</td>
      <td id="sum-R2">5</td>
      <td sum="grand-total">11</td>
   </tr>
</table>
<table border="1" cellspacing="0" class="stn">
   <tr>
      <th>*</th>

      <th>R2</th>
      <th>R3</th>
      <th>total</th>
   </tr>
   <tr>
      <th>C2</th>
      <td id="C2xR2">1</td>

      <td id="C2xR3">3</td>
      <td class="sum-C2">4</td>
   </tr>
   <tr>
      <th>C3</th>
      <td id="C3xR2">2</td>
      <td id="C3xR3">0</td>

      <td class="sum-C3">2</td>
   </tr>
   <tr>
      <th>total</th>
      <td id="sum-R2">3</td>
      <td id="sum-R3">3</td>
      <td sum="grand-total">6</td>

   </tr>
</table>


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
James A. Robinson                       jim.robinson@xxxxxxxxxxxx
Stanford University HighWire Press      http://highwire.stanford.edu/
+1 650 7237294 (Work)                   +1 650 7259335 (Fax)

Current Thread

PURCHASE STYLUS STUDIO ONLINE TODAY!

Purchasing Stylus Studio from our online shop is Easy, Secure and Value Priced!

Buy Stylus Studio Now

Download The World's Best XML IDE!

Accelerate XML development with our award-winning XML IDE - Download a free trial today!

Don't miss another message! Subscribe to this list today.
Email
First Name
Last Name
Company
Subscribe in XML format
RSS 2.0
Atom 0.3
Site Map | Privacy Policy | Terms of Use | Trademarks
Free Stylus Studio XML Training:
W3C Member
Stylus Studio® and DataDirect XQuery ™are products from DataDirect Technologies, is a registered trademark of Progress Software Corporation, in the U.S. and other countries. © 2004-2013 All Rights Reserved.