Row Grouping is a new feature of KoolReport's Table widget. In this example, we have query data including month and amount in which amount is the sale of particular month.
Now we would like to group those months into it particular year, calculate the total sale of that years. Furthermore, at the end of table we would like to have the Grand Total amount.
The Grand Total amount is easy as we can use the "footer" feature of Table.
In order to group by year and calculate sum of each year we do this:
<?php
Table::create(array(
...
"grouping"=>array(
"year"=>array(
"calculate"=>array(
"{sumAmount}"=>array("sum","amount")
),
"top"=>"<b>Year {year}</b>",
"bottom"=>"<td><b>Total of year {year}</b></td><td><b>{sumAmount}</b></td>"
),
),
))
We define grouping by year followed by settings: please calcuate {sumAmount} by sum the amount of the year. Then fill the {sumAmount} to the "start" template and "end" template.
The "top" template defines what will show at beginning of the group while "bottom" define what to show at the end of the group. So we want to show the year on top and total year at the bottom.
Note: In above example, we show single level of grouping (by year) only. The Table can support multilevel grouping in which you can define another group as a sub group. For example:
Table::create(array(
"grouping"=>array(
"country"=>array(...),
"city"=>array(...)
)
))
<?php
require_once "../../../load.koolreport.php";
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\DateTimeFormat;
use \koolreport\processes\CopyColumn;
use \koolreport\processes\Group;
class MyReport extends \koolreport\KoolReport
{
function settings()
{
return array(
"dataSources" => array(
"payments"=>array(
'filePath' => dirname(__FILE__).'/../../../databases/payments.csv',
'class' => "\koolreport\datasources\CSVDataSource"
),
)
);
}
function setup()
{
$this->src("payments")
->pipe(new ColumnMeta(array(
"paymentDate"=>array(
"type"=>"date",
"format"=>"Y-m-d"
),
"amount"=>array(
"type"=>'number'
)
)))
->pipe(new CopyColumn(array(
"year"=>"paymentDate",
"month"=>"paymentDate",
)))
->pipe(new DateTimeFormat(array(
"year"=>"Y",
"month"=>"F, Y"
)))
->pipe(new Group(array(
"by"=>"month",
"sum"=>"amount"
)))
->pipe($this->dataStore("payments"));
}
}
<?php
use \koolreport\widgets\koolphp\Table;
?>
<div class="report-content">
<div class="text-center">
<h1>Table Row Group</h1>
<p class="lead">
This example shows how to setup row grouping with Table widgets
</p>
</div>
<?php
// $data = array_slice($this->dataStore("payments")->data(), 0, 10);
// echo "<pre>" . var_export($data) . "</pre>";
// $data = array(
// array('paymentDate' => '2003-04-11', 'amount' => 136313.91999999998, 'year' => '2003', 'month' => 'April, 2003'),
// array('paymentDate' => '2004-04-26', 'amount' => 173245.96000000002, 'year' => '2004', 'month' => 'April, 2004'),
// array('paymentDate' => '2005-04-16', 'amount' => 183897.72, 'year' => '2005', 'month' => 'April, 2005'),
// array('paymentDate' => '2003-08-15', 'amount' => 246204.86, 'year' => '2003', 'month' => 'August, 2003'),
// array('paymentDate' => '2004-08-20', 'amount' => 378094.3, 'year' => '2004', 'month' => 'August, 2004'),
// array('paymentDate' => '2003-12-11', 'amount' => 826637.6400000001, 'year' => '2003', 'month' => 'December, 2003'),
// array('paymentDate' => '2004-12-18', 'amount' => 819285.6199999999, 'year' => '2004', 'month' => 'December, 2004'),
// array('paymentDate' => '2003-02-16', 'amount' => 144384.36, 'year' => '2003', 'month' => 'February, 2003'),
// array('paymentDate' => '2004-02-28', 'amount' => 106652.01, 'year' => '2004', 'month' => 'February, 2004'),
// array('paymentDate' => '2005-02-22', 'amount' => 252321.21999999997, 'year' => '2005', 'month' => 'February, 2005'),
// );
Table::create(array(
"dataSource" => $this->dataStore('payments'),
"grouping" => array(
"year" => array(
"calculate" => array(
"{sumAmount}" => array("sum", "amount")
),
"top" => "<b>Year {year}</b>",
"bottom" => "<td><b>Total of year {year}</b></td><td><b>{sumAmount}</b></td>",
),
),
"sorting" => array(
"paymentDate" => "asc"
),
"showFooter" => true,
"columns" => array(
"month" => array(
"label" => "Month",
"footerText" => "<b>Grand Totals</b>"
),
"amount" => array(
"label" => "Sale Amount",
"prefix" => "$",
"footer" => "sum",
"footerText" => "<b>@value</b>"
)
),
"cssClass" => array(
"table" => "table-bordered",
"tf" => "darker"
)
));
?>
<style>
.darker {
background: #ccc;
}
</style>
</div>
