RE: Another grouping problem

Michael,  here is the original question.  If you could help me to
understand how to accomplish this grouping, I'd very much appriciate it.
The problem has me completely confused.  I fear it may be beyond my
current, very limited skills.


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

All,

I recently posted a grouping problem and with the help of Josh Canfield,
got the problem resolved.  Thanks Josh!!!

I have another grouping problem which I have not been able to figure out.
This report must keep a running total of data by column.

Heres the data

<?xml version="1.0"?>
<report>
      <search_criteria>
            <clec>ALL</clec>
            <region>ALL</region>
            <startdate>01/01/2004</startdate>
            <enddate>07/28/2004</enddate>
            <origin>ALL</origin>
      </search_criteria>
      <data>
            <row>
                  <daysopen>0</daysopen>
                  <region_ind>CT</region_ind>
                  <mnt>2004-01</mnt>
                  <poncnt>2</poncnt>
            </row>
            <row>
                  <daysopen>0</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-02</mnt>
                  <poncnt>49</poncnt>
            </row>
            <row>
                  <daysopen>0</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-01</mnt>
                  <poncnt>80</poncnt>
            </row>
            <row>
                  <daysopen>0</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-01</mnt>
                  <poncnt>45</poncnt>
            </row>
            <row>
                  <daysopen>0</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-02</mnt>
                  <poncnt>127</poncnt>
            </row>
            <row>
                  <daysopen>0</daysopen>
                  <region_ind>CT</region_ind>
                  <mnt>2004-03</mnt>
                  <poncnt>1</poncnt>
            </row>
            <row>
                  <daysopen>0</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-03</mnt>
                  <poncnt>1</poncnt>
            </row>
            <row>
                  <daysopen>0</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-03</mnt>
                  <poncnt>17</poncnt>
            </row>
            <row>
                  <daysopen>1</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-01</mnt>
                  <poncnt>54</poncnt>
            </row>
            <row>
                  <daysopen>1</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-01</mnt>
                  <poncnt>78</poncnt>
            </row>
            <row>
                  <daysopen>1</daysopen>
                  <region_ind>CT</region_ind>
                  <mnt>2004-02</mnt>
                  <poncnt>1</poncnt></row>
            <row>
                  <daysopen>1</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-02</mnt>
                  <poncnt>84</poncnt>
            </row>
            <row>
                  <daysopen>1</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-02</mnt>
                  <poncnt>107</poncnt>
            </row>
            <row>
                  <daysopen>1</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-03</mnt>
                  <poncnt>12</poncnt>
            </row>
            <row>
                  <daysopen>1</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-03</mnt>
                  <poncnt>49</poncnt>
            </row>
            <row>
                  <daysopen>2</daysopen>
                  <region_ind>CT</region_ind>
                  <mnt>2004-01</mnt>
                  <poncnt>11</poncnt>
            </row>
            <row>
                  <daysopen>2</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-01</mnt>
                  <poncnt>56</poncnt>
            </row>
            <row>
                  <daysopen>2</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-01</mnt>
                  <poncnt>70</poncnt>
            </row>
            <row>
                  <daysopen>2</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-02</mnt>
                  <poncnt>15</poncnt>
            </row>
            <row>
                  <daysopen>2</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-02</mnt>
                  <poncnt>96</poncnt>
            </row>
            <row>
                  <daysopen>2</daysopen>
                  <region_ind>DC</region_ind>
                  <mnt>2004-03</mnt>
                  <poncnt>3</poncnt>
            </row>
            <row>
                  <daysopen>2</daysopen>
                  <region_ind>DE</region_ind>
                  <mnt>2004-03</mnt>
                  <poncnt>20</poncnt>
            </row>
      </data>
</report>



The results should look like this:

-------------------------------------------------------------------------------------------------------

      |     CT    |     DC    |     DE    |     Total
Days  |Count      |Cum %      |Count      |Cum %      |Count      |Cum %
|     Sum   |Cum %|
--------------------------------------------------------------------------------------------------------

0     |3    |20.00%|208 |48.15%     |111  |20.90%|322 |32.92%
--------------------------------------------------------------------------------------------------------

1     |1    |26.67%     |150  |82.87%|234 |64.97%     |385  |72.30%
-------------------------------------------------------------------------------------------------------

2     |11   |100.00%|74 |100.00%|186      |100.00%|271      |100.00%
------------------------------------------------------------------------------------------------------

Total |15   |100.00%|432      |100.00%|531      |100.00%|978      |100.00%


Lets look at the count and cum % for CT:

On day 0, the count is 3 and the total is 15.  3 is 20.00% of 15, so we put
that number in the cum %.

On day 1, the count is 1.  Add this to the 3 from day 0 gives us 4.  4 is
26.67% of 15.

On day 2, the count is 11.  Add this to the 3 from day 0 and the 1 from day
1 to give us 15.  15 is 100% of 15.

The Total column works just like the region columns.


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

<!-- key to return the row nodes with matching daysopen nodes-->
<xsl:key name="days-open" match="row" use="daysopen"/>
<!-- key to return the row nodes with matchine region_ind nodes-->
<xsl:key name="region" match="row" use="region_ind"/>

<xsl:template match="/report">
<table border="0">
<tr>
<td colspan="2"><b><font size="3">SEARCH CRITERIA</font></b></td>
</tr>
<tr>
<td>&#160;</td>
</tr>
<tr>
<td><b><font size="2">CLEC</font></b></td>
<td>&#160;</td>
<td><b><font size="2"><xsl:value-of
select="search_criteria/clec"/></font></b></td>
</tr>
<tr>
<td><b><font size="2">Region</font></b></td>
<td>&#160;</td>
<td><b><font size="2"><xsl:value-of
select="search_criteria/region"/></font></b></td>
</tr>
<tr>
<td><b><font size="2">Start Date</font></b></td>
<td>&#160;</td>
<td><b><font size="2"><xsl:value-of
select="search_criteria/startdate"/></font></b></td>
</tr>
<tr>
<td><b><font size="2">End Date</font></b></td>
<td>&#160;</td>
<td><b><font size="2"><xsl:value-of
select="search_criteria/enddate"/></font></b></td>
</tr>
<tr>
<td><b><font size="2">Origin of LSR</font></b></td>
<td>&#160;</td>
<td><b><font size="2"><xsl:value-of
select="search_criteria/origin"/></font></b></td>
</tr>
</table>
<br></br><br></br>
<table border="1" width="100%">
<!-- Build Header Row -->
<tr bgcolor="#BDBDBD">
<td align="center"><b><font size="2">Count of PON</font></b></td>
<xsl:for-each select="data/row[count(. | key('region',region_ind)[1])=1]">
<xsl:sort select="region_ind" data-type="text"/>
<td align="center" colspan="2"><b><font size="2"><xsl:value-of
select="region_ind"/></font></b></td>
</xsl:for-each>
<td align="center" colspan="2"><b><font size="2">Total</font></b></td>
</tr>
<tr bgcolor="#BDBDBD">
<td align="center"><font size="2">Days to Resolve</font></td>
<xsl:for-each select="data/row[count(. | key('region',region_ind)[1])=1]">
<td align="center"><font size="2"># of PONs</font></td>
<td align="center"><font size="2">Cumulative %</font></td>
</xsl:for-each>
<td align="center"><font size="2"># of PONs</font></td>
<td align="center"><font size="2">Cumulative %</font></td>
</tr>

<!-- Build the content Rows -->
<!-- Iterate over the set of row nodes containing the first unique daysopen
value -->
<xsl:for-each select="data/row[count(. | key('days-open',
daysopen)[1])=1]">
<xsl:sort select="daysopen" data-type="number"/>
<!-- Hold on to the current days-open -->
<xsl:variable name="days-open" select="daysopen"/>
<tr>
<td align="center"><xsl:value-of select="$days-open"/></td>
<!-- Iterate over a set of row nodes containing the first unique region_ind
value -->
<xsl:for-each select="../row[count(. | key('region',region_ind)[1])=1]">
<xsl:sort select="region_ind" data-type="text"/>
<!-- create a cell containing the sum poncnt nodes from the row nodes with
common daysopen and region_ind nodes but different months.  These will be
the data cells-->
<td align="center"><xsl:value-of select="sum(../row[(daysopen=$days-open)
and (region_ind=current()/region_ind)]/poncnt)"/></td>
<td align="center">0%</td>
</xsl:for-each>
<!-- create a cell containing the sum of the poncnt nodes of rows with a
common daysopen node - this will be the row sum cell-->
<td align="center"><xsl:value-of
select="sum(../row[daysopen=$days-open]/poncnt)"/></td>
<td align="center">0%</td>
</tr>
</xsl:for-each>
<tr bgcolor="#BDBDBD">
<td align="center"><b><font size="2">Grand Total</font></b></td>

<xsl:for-each select="data/row[count(. | key('region', region_ind)[1])=1]">
<xsl:sort select="region_ind" data-type="text"/>
<!-- Hold on to the current region -->
<xsl:variable name="region" select="region_ind"/>
<td align="center"><b><font size="2"><xsl:value-of
select="sum(../row[(region_ind=$region)]/poncnt)"/></font></b></td>
<td align="center">0%</td>
</xsl:for-each>
<xsl:for-each select="data/row[1]">
<td align="center"><b><font size="2"><xsl:value-of
select="sum(../row/poncnt)"/></font></b></td>
</xsl:for-each>
<td align="center">0%</td>
</tr>
</table>
</xsl:template>

</xsl:stylesheet>

This one has me baffled.  I can't figure out how to keep the running total
for the columns.

I'd really appreciate any help on this one.

Thanks

Mike