Total Sums and Operations of the displayed columns

In all the table views we have the option of displaying the sum of a column on the top of a table. It can be displayed inline by default or one result per line, for such we use the variable '$totalsFormat'

$totalsFormat = 'inline'; //optional, inline by default

$totalsFormat = 'break'; 

 

Subtotal: $984.99 
Tax 1:      $125.35
Tax 2:     $0
Total:      $1,110.34

 

This sum, count or calculation is only for the visible results, which means that if we are using pagination and there is more than one page, the sum is only for the current page. For this kind of feature qwe recommend removing the option type N

For this we have one multidimensional array $viewtotals with name, col, calc, static, suffix, and prefix, for example:

Form the options mentioned above the we can only use a combination of 3 at a time:

- name (required)
- col or calc or static (required)
- suffix or prefix (optional)

 

'name' is the Label to show before the avlue, in the next example, Subtotal is the 'name':

Subtotal: $984.99  

 

$viewtotals[0]['name'] = 'Subtotal';
$viewtotals[0]['col'] = 'subtotal';
$viewtotals[0]['prefix'] = '$';
$viewtotals[0]['style'] = 'color:red';

 

Sum Results: 'col' is the name of the column that we want to add up, if the column name is tax then it will add all the values of the column tax1

Tax 1: 125.35

 

$viewtotals[1]['name'] = 'Tax 1';
$viewtotals[1]['col'] = 'tax';

 

'prefix' allows us to add something before the final value, in the case of Taxes a $ would be suitabe:

Tax 1: $125.35

 

$viewtotals[1]['name'] = 'Tax 1';
$viewtotals[1]['col'] = 'tax';
$viewtotals[1]['prefix'] = '$';

 

Count Results: We can also use 'col' to count how many results exists with a specific value, for example, how many invoices were paid, then we use the column 'status=paid', assuming the column name in the database is paid and the value in the database for paid invoices is 'paid'

Paid Invoices: 24

 

$viewtotals[2]['name'] = 'Paid Invoices';
$viewtotals[2]['col'] = 'status=paid';

 

'calc' allows us to do any calculation using columns that have already been used in the sums or counts, For instance we previously used subtotal and tax, so the total amount is subtotal + tax, we can do so by wrapping the column names between '%': 

 

Total: $1,110.34

$viewtotals[3]['name'] = 'Total';
$viewtotals[3]['calc'] = '%subtotal% + %tax1%'; 
//with [calc] %col% must be a previously $viewtotals[n]['col'] used
$viewtotals[3]['prefix'] = '$';

 

It can also be used with "Count Results" results:

Paid Invoices: 24
Pending Invoices: 26
Total Invoices: 50
Paid Rate: 48

 

$viewtotals[4]['name'] = 'Number of invoices';
$viewtotals[4]['calc'] = '%status=paid% + %status=pending%'; 
//with [calc] %col% must be a previously $viewtotals[n]['col'] used

$viewtotals[5]['name'] = 'Paid Rate';
$viewtotals[5]['calc'] = '( ( %status=paid% + %status=pending% ) / %status=paid% ) * 100'; 
//with [calc] %col% must be a previously $viewtotals[n]['col'] used

 

 

Total Number of Results: This one is very useful for 2 reasons, first in this kind of tables "Option Type N" (pagination) is usually not used so the results can be more accurate, so we need to know the number of results, and second, many times to do certain operations using "calc", we need to use the number of results, or example to get an average. We can display the total number of results by using $viewtotals[position] but instead of 'col' we will use 'length' with value true

 

Number of Invoices: 254 invoices

$viewtotals[3]['name'] = 'Number of Invoices';
$viewtotals[3]['length'] = true;  //lenght can be used as acolumn name for calculations, see below
​$viewtotals[3]['prefix'] = '';
$viewtotals[3]['suffix'] = ' invoices';

 

We can use "length" in calculations, assume countINVID is a column with the number of invoices per client, then we can divide countINVID by length :

 

AVG  Invoice per client 2.45 invoices

$viewtotals[5]['name'] = 'AVG Invoice per client';
$viewtotals[5]['calc'] = "%countINVID% / %length%";
$viewtotals[5]['prefix'] = '';
$viewtotals[5]['suffix'] = ' invoices';

 

'suffix' Allows us to add something after the value, like in the case of Paid Rate a percentage sight '%' would be suitable:

Paid Rate: 48%

 

$viewtotals[5]['name'] = 'Paid Rate';
$viewtotals[5]['calc'] = '( ( %status=paid%+%status=pending% ) / %status=paid% ) * 100'; 
//with [calc] %col% must be a previously $viewtotals[n]['col'] used
$viewtotals[5]['suffix'] = '%';

 

'static' Is simply a placeholder that allows you to add any Result that you can calculate manually. This result cannot be used in calc

Report Date: 2021-10-25

 

$viewtotals[6]['name'] = 'Report Date';
$viewtotals[6]['static'] = date('Y-m-d'); 

 

For the index of the array you can use a dynamic variable that can let you show different options if required, in this example we show how we can also hide a sum (display:none) but generate it because it will be used later with a "calc":

 

$itot = 0;
$viewtotals[$itot]['name'] = 'Subtotal';
$viewtotals[$itot]['col'] = 'subtotal';
$viewtotals[$itot]['prefix'] = '$';
$viewtotals[$itot]['style'] = 'display:none'; //hidden, but used later in Total
$itot++;


$viewtotals[$itot]['name'] = 'Tax 1';
$viewtotals[$itot]['col'] = 'tax';
$viewtotals[$itot]['prefix'] = '$';
$itot++;


if(isset($d1)){
  /**** Paid Today only shows if $d1 exists ***/
   $viewtotals[$itot]['name'] = 'Paid Today';
   $viewtotals[$itot]['col'] = 'payment_date='.date('Y-m-d');
   $itot++;

}

$viewtotals[$itot]['name'] = 'Total';
$viewtotals[$itot]['calc'] = '%subtotal% + %tax1%'; 
//with [calc] %col% must be a previously $viewtotals[n]['col'] used
$viewtotals[$itot]['prefix'] = '$';
$viewtotals[$itot]['style'] = 'color:green; font-weight:800';
$itot++;