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> </td>
</tr>
<tr>
<td><b><font size="2">CLEC</font></b></td>
<td> </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> </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> </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> </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> </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
|---------+---------------------------->
| | "Michael Kay" |
| | <mhk(_at_)mhk(_dot_)me(_dot_)uk> |
| | |
| | 07/28/04 12:01 PM|
| | Please respond to|
| | xsl-list |
| | |
|---------+---------------------------->
--------------------------------------------------------------------------------------------------------------|
|
|
| To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
|
| cc:
|
| Subject: RE: [xsl] Another grouping problem
|
--------------------------------------------------------------------------------------------------------------|
Michael,
How do I store the information so as to be able to reference it to the
correct region?
I'm sorry, I don't understand the question, because I didn't read/remember
the problem description in enough detail. I just saw that it involved
calculating running totals, and gave some general advice on the subject.
Sorry if it didn't take you all the way to a specific solution.
Michael Kay
|---------+---------------------------->
| | "Michael Kay" |
| | <mhk(_at_)mhk(_dot_)me(_dot_)uk> |
| | |
| | 07/28/04 04:56 AM|
| | Please respond to|
| | xsl-list |
| | |
|---------+---------------------------->
-------------------------------------------------------------
-------------------------------------------------|
|
|
| To: xsl-list(_at_)lists(_dot_)mulberrytech(_dot_)com
|
| cc:
|
| Subject: RE: [xsl] Another grouping problem
|
-------------------------------------------------------------
-------------------------------------------------|
This report must keep a running total of data by column.
To get a running total, you either need to calculate each
total by summing
all the previous entries (which involves n*n/2 operations),
or you need
recursion, passing the current total as a parameter at each
recursive step.
Interestingly, running totals are one of the few examples I
found where
linear head-tail recursion (recursive processing of a
sequence) is still
needed in XSLT 2.0.
Michael Kay
--+------------------------------------------------------------------
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>
--+--
--+------------------------------------------------------------------
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>
--+--
--+------------------------------------------------------------------
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>
--+--