xsl-list
[Top] [All Lists]

RE: Another grouping problem

2004-07-28 09:27:02




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


|---------+---------------------------->
|         |           "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>
--+--







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