Table Row Group

This example shows how to setup row grouping with Table widgets

MonthSale Amount
Year 2003
January, 2003 $26,268
February, 2003 $144,384
March, 2003 $199,704
April, 2003 $136,314
May, 2003 $159,882
June, 2003 $180,219
July, 2003 $158,247
August, 2003 $246,205
September, 2003 $161,206
October, 2003 $316,858
November, 2003 $694,293
December, 2003 $826,638
Total of year 2003$3,250,218
Year 2004
January, 2004 $234,152
February, 2004 $106,652
March, 2004 $404,603
April, 2004 $173,246
May, 2004 $208,524
June, 2004 $185,843
July, 2004 $284,191
August, 2004 $378,094
September, 2004 $476,446
October, 2004 $185,103
November, 2004 $857,187
December, 2004 $819,286
Total of year 2004$4,313,328
Year 2005
January, 2005 $137,468
February, 2005 $252,321
March, 2005 $385,268
April, 2005 $183,898
May, 2005 $272,249
June, 2005 $59,089
Total of year 2005$1,290,293
Grand Totals $8,853,839

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 "MyReport.php";
$report = new MyReport;
$report->run()->render();
<?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>
paymentDateamount
2004-10-19 6066.78
2003-06-05 14571.44
2004-12-18 1676.14
2004-12-17 14191.12
2003-06-06 32641.98
2004-08-20 33347.88
2003-05-20 45864.03
2004-12-15 82261.22
2003-05-31 7565.08
2004-03-10 44894.74

What People Are Saying

"KoolReport helps me very much in creating data report for my corporate! Keep up your good work!"
-- Alain Melsens

"The first use of your product. I was impressed by its easiness and powerfulness. This product is a great and amazing."
-- Dr. Lew Choy Onn

"Fantastic framework for reporting!"
-- Greg Schneider

Download KoolReport Get KoolReport Pro