Re: Pivot Reports

On Thu, 18 Jan 2007 09:52:28 -0800 I wrote:
< < > 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.

Thinking about it a bit more on my way in to work, I realized I could
use the same technique of examining the current pivot's 'row' values to
determine the unique rows.

I didn't say this in my previous message, but from what I could gather
of your XSLT, I think the problem you were facing was that you were
using key() and generate-id in to broad a context, in the context of
the entire document (all pivot elements).  That would cause the problem
I think you were having, where only the first table held all the rows.

Here's a revised version of what I sent:

<?xml version="1.0" encoding="UTF-8"?>

<!--
  Build an HTML table pivot report from data in the form
  <notice>
    <pivotes>
      <pivot>
        <item>
          <row>R1</row>
          <col>C1</col>
          <val>1</val>
        </item>
        ...
      </pivot>
      ...
      </pivots>
  </notice>
-->
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

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

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

  <!-- By default do not process elements -->
  <xsl:template match="@*|node()" />

  <!-- Return general parsed entity of table elements, one for each pivot -->
  <xsl:template match="/">
    <xsl:apply-templates select="/notice/pivots/pivot" />
  </xsl:template>

  <!-- Return a table per pivot -->
  <xsl:template match="pivot">
    <!--
      Use generated id of this pivot to determine our rows.
      Extract the unique (by value) row elements within this
      particular pivot.
    -->
    <xsl:variable name="pivot-id" select="generate-id(.)" />
    <xsl:variable name="rows"
      select="key('pivot-rows', $pivot-id)[
                  not(. = ancestor::item[1]/preceding-sibling::item/row)]" />

    <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 matching item)
      -->
      <xsl:apply-templates select="item" />

      <!-- compute column and grand totals -->
      <tr>
        <th>total</th>
        <xsl:for-each select="$rows">
          <td class="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 class="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
    within the list of items (i.e., within the container pivot)
  -->
  <xsl:template match="item[not(col = preceding-sibling::item/col)]">

    <!--
      Use generated id of ancestor pivot to determine our rows.
      Extract the unique (by value) row elements within this
      particular pivot.
    -->
    <xsl:variable name="pivot-id" select="generate-id(ancestor::*[1])" />
    <xsl:variable name="rows"
                  select="key('pivot-rows', $pivot-id)[
                  not(. = ancestor::item[1]/preceding-sibling::item/row)]" />

    <!--
      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 class="{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>

  <!--
    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>

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

--
*/Best regards,
Vitaliy Paykov/*
e-mail: vpay@xxxxxxxxx
ext: 1120
ICQ: 220022522
*/ISD QA/*