XSLT calculations where grouping is on grand-children

Question

i have a XML datadump from a database that i want to present using xslt. The structure of the data is not 'straightforward' for the layout that i want (i also use the XML data for another report).

What i want is to do some calculations on data from Level-A where i need to group on Level-C children.

I know i can probably select the data again into an XML file where the structure is 'easy' for my report, but that is my last resort because i have the feeling that it can also be accomplished in XSLT itself. Most probbaly i need some 'Muenchian' trick to get it done, but since i am a 'Muenchian Virgin' i get stuck in every attempt (that i try to 'steal' and change ...).

Does anybody know if Muenchian is the way to proceed and can somebody help me to get on the right track ? I did some reading (including Jeni Tennison's), but the stuff i have seen so far is not covering my problem as far as i know ...

Below is a simplified XML structure that is (more or less) representative for my real problem.

Any ideas?

Kind regards, Henk

Simplyfied XML:

<data>
  <a>
    <a_id>A1</a_id>
    <a_desc>A one</a_desc>
    <a_val>1</a_val>
    <b>
      <c>
        <c_id>C2</c_id>
        <c_desc>C two</c_desc>
      </c>
    </b>
  </a>
  <a>
    <a_id>A2</a_id>
    <a_desc>A two</a_desc>
    <a_val>2</a_val>
    <b>
      <c>
        <c_id>C2</c_id>
        <c_desc>C two</c_desc>
      </c>
    </b>
  </a>
  <a>
    <a_id>A3</a_id>
    <a_desc>A three</a_desc>
    <a_val>3</a_val>
    <b>
      <c>
        <c_id>C1</c_id>
        <c_desc>C one</c_desc>
      </c>
    </b>
  </a>
  <a>
    <a_id>A4</a_id>
    <a_desc>A four</a_desc>
    <a_val>7</a_val>
    <b>
      <c>
        <c_id>C3</c_id>
        <c_desc>C three</c_desc>
      </c>
    </b>
  </a>
  <a>
    <a_id>A5</a_id>
    <a_desc>A five</a_desc>
    <a_val>11</a_val>
    <b>
      <c>
        <c_id>C1</c_id>
        <c_desc>C one</c_desc>
      </c>
    </b>
  </a>
</data>

Required output should be something like:

C_desc  Count() Sum(a_val)  Avg(a_val) 
------  ------- ----------  ----------
C one       3       15          5
C two       1       2           2
C three     1       7           7

Answer 1

As you mention, Muenchian grouping is the way to go (in XSLT1.0). You say you want to group a elements, using values in a c element. Therefore you would define a key like so:

<xsl:key name="a" match="a" use="b/c/c_desc" />

Then, you need to get 'distinct' a elements, which is done by selecting a elements that happen to be the first element in the group for a given key. You do this with this fairly scary expression

<xsl:apply-templates 
     select="//a[generate-id() = generate-id(key('a', b/c/c_desc)[1])]" />

Here, key('a', b/c/c_desc)[1] will find the first element in the key's group, and then you use generate-id to compare the elements.

Then, you would have a template to match the distinct a elements, and within in this you can then do calculations on the group. For example, to get the sum:

<xsl:value-of select="sum(key('a', b/c/c_desc)/a_val)" />

Here is the full XSLT

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:exsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="exsl">
   <xsl:output method="html" indent="yes"/>
   <xsl:key name="a" match="a" use="b/c/c_desc" />
   <xsl:template match="/">
      <table>
         <tr>
            <td>C_desc</td>
            <td>Count</td>
            <td>Sum</td>
            <td>Avg</td>
         </tr>
      <xsl:apply-templates select="//a[generate-id() = generate-id(key('a', b/c/c_desc)[1])]" />
      </table>
   </xsl:template>

   <xsl:template match="a">
      <xsl:variable name="c_desc" select="b/c/c_desc" />
      <tr>
         <td><xsl:value-of select="count(key('a', $c_desc))" /></td>
         <td><xsl:value-of select="sum(key('a', $c_desc)/a_val)" /></td>
         <td><xsl:value-of select="sum(key('a', $c_desc)/a_val) div count(key('a', $c_desc))" /></td>
      </tr>
   </xsl:template>
</xsl:stylesheet>

When applied to your sample XML, the following is output

<table>
   <tr>
      <td>C_desc</td>
      <td>Count</td>
      <td>Sum</td>
      <td>Avg</td>
   </tr>
   <tr>
      <td>3</td>
      <td>15</td>
      <td>5</td>
   </tr>
   <tr>
      <td>1</td>
      <td>2</td>
      <td>2</td>
   </tr>
   <tr>
      <td>1</td>
      <td>7</td>
      <td>7</td>
   </tr>
</table>