By: Michel
Date: 2006-03-02
Time: 14:30
|
Sum in groups
Maybe a stupid question, but I'm a newby to php:
I want to display the totals of several numeric colums in the footer of a group (Grouping example: The scores)
• United States
• Washington
Nom Score1 Score2 Score3
° Bob 100 150 600
° Julia 25 200 225
° Mark 78 56 123
→ Totals 203 406 948
• New York ...
ondata function? array_sum?
Thx in advance,
Michel
|
By: Skrol29
Date: 2006-03-02
Time: 15:22
|
Re: Sum in groups
Hi Michel,
You have to code your own calculation using an "ondata" custom function.
Example:
function f_total($Name,&$CurrRec,$RecNum) {
global $tot_1, $tot_2, $tot_3;
if (!isset($tot_1)) {
$tot_1 = 0;
$tot_2 = 0;
$tot_3 = 0;
}
$tot_1 += $CurrRec['score1'];
$tot_2 += $CurrRec['score2'];
$tot_3 += $CurrRec['score3'];
}
|
|
By: Michel
Date: 2006-03-03
Time: 08:02
|
Re: Sum in groups
Hi Skrol29,
you are doing an excellent job since a long time. So thanks for your answer(s).
The function seems to work fine but the result is for all groups the same (the total is overall and not for every group individual as desired).
A problem of the html page probably. The table is displayed o.k.-great class-, so please have a look at my code
[onload;file=tbs_header.html]
<p class="titel1">InfoPage</p>
<table width="500" border="1" align="center" cellpadding="4" cellspacing="0">
<tr><td>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<colgroup><col align="right" /><col align="right" /><col align="right" /><col align="right" /></colgroup>
<tr class="tbsgroup1">
<td colspan="4" align="left">[block1.YEAR;block=tr;headergrp=YEAR;ondata=f_total]</td>
</tr>
<tr class="tbsgroup2">
<td> [block1;block=td;headergrp=YEAR]</td>
<td><strong>MONTH</strong></td>
<td><strong>INVALUE</strong></td>
<td><strong>OUTVALUE</strong></td>
</tr>
<tr class="tbsrow1">
<td> </td>
<td>[block1.MONTH;block=tr]</td>
<td>[block1.INVALUE;frm='0.000,00 €']</td>
<td>[block1.OUTVALUE;frm='0.000,00 €']</td>
</tr>
<tr class="tbsgroup2">
<td colspan="2">Totals: [block1;block=tr;footergrp=YEAR]</td>
<td>Wert: [var.tot_1]</td>
<td>Wert: [var.tot_2]</td>
</tr>
<tr>
<td colspan="4">[block1;block=tr;splittergrp=YEAR]
<hr noshade></td>
</tr>
</table>
</td></tr>
</table>
</body>
</html>
|
Any ideas?
|
By: Skrol29
Date: 2006-03-03
Time: 12:00
|
Re: Sum in groups
Hi Michel,
In this case, it's better to save the totals into new columns of the current record in order to display them in a "footergrp" section. You also have to set totals to zero when a new "headergrp" section starts.
The calculation becomes:
function f_total($Name,&$CurrRec,$RecNum) {
global $save_year, $tot_1, $tot_2, $tot_3;
if (!isset($save_year)) $save_year = '';
if ($save_year!=$CurrRec['YEAR']) {
$tot_1 = 0;
$tot_2 = 0;
$tot_3 = 0;
$save_year = $CurrRec['YEAR'];
}
// Calculate totals
$tot_1 += $CurrRec['score1'];
$tot_2 += $CurrRec['score2'];
$tot_3 += $CurrRec['score3'];
// Save into the current record
$CurrRec['tot_1'] = $tot_1;
$CurrRec['tot_2'] = $tot_2;
$CurrRec['tot_3'] = $tot_3;
}
|
|
By: Michel
Date: 2006-03-03
Time: 15:39
|
Re: Sum in groups
Dear Skrol,
your function is plausible, but unfortunately the var.total_xy are the totals of the last group, but displayed on every previous group.
example:
Year 2006 var.total_1=12345 // incorrect value
Year 2005 var.total_1=12345 // incorrect value
Year 2004 var.total_1=12345 (this is the last record and the value is correct)
Here is my table structure:
<!—Head1: Show YEAR -->
[block1.YEAR;block=tr;headergrp=YEAR;ondata=f_total]
<!—Head1: Show column titel MONTH,INVALUE,OUTVALUE -->
MONTH [block1;block=tr;headergrp=YEAR]
INVALUE
OUTVALUE
<!—Rows: Show value MONTH,INVALUE,OUTVALUE -->
[block1.MONTH;block=tr]
[block1.INVALUE]
[block1.OUTVALUE]
<!—Footer1: Show total value of year for INVALUE,OUTVALUE -->
Totals: [block1;block=tr;footergrp=YEAR]
Total: [var.tot_1]
Total: [var.tot_2]
<!—Groupbreak: Next YEAR -->
[block1;block=tr;splittergrp=YEAR]
I tried some codechanges without succeed. Maybe I should do a weekend break...
Do you have any solution in mind?
Bye, Michel
|
By: Skrol29
Date: 2006-03-05
Time: 22:59
|
Re: Sum in groups
Hi Michel,
In my mind, global variables $tot_1 and $tot_2 where not made to be used in the "footergrp" but in order to save the sum over each records. Use calculated columns of the record instead. Like this:
<tr>
Totals: [block1;block=tr;footergrp=YEAR]
Total: [block1.tot_1]
Total: [block1.tot_2]
</tr>
|
|
By: Michel
Date: 2006-03-06
Time: 07:16
|
Re: Sum in groups
That's it!
Thanks so much for your great help.
Michel
|
|
Posting in progress.
Please wait...
|