Table Row Group

This example shows how to setup row grouping with Table widgets

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

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 "../../../../koolreport/autoload.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
    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>"
            ),
        ),
        "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