xsl-list
[Top] [All Lists]

Re: [xsl] Pivot Reports

2007-01-18 10:53:04

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(_dot_)robinson(_at_)stanford(_dot_)edu
Stanford University HighWire Press      http://highwire.stanford.edu/
+1 650 7237294 (Work)                   +1 650 7259335 (Fax)

--~------------------------------------------------------------------
XSL-List info and archive:  http://www.mulberrytech.com/xsl/xsl-list
To unsubscribe, go to: http://lists.mulberrytech.com/xsl-list/
or e-mail: <mailto:xsl-list-unsubscribe(_at_)lists(_dot_)mulberrytech(_dot_)com>
--~--

<Prev in Thread] Current Thread [Next in Thread>