Categories > TinyButStrong general >

Sum in groups

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
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