Columns Transpose

Transpose a table using first column as transposed column names

CustomerQ 1Q 2Q 3Q 4
Euro+ Shopping Channel $285,274.59 $249,674 $64,949 $220,792
Mini Gifts Distributors Ltd. $239,256.23 $7,639 $197,065 $147,867
Australian Collectors, Co. $44,894.74 $53,429 $0 $82,261
Muscle Machine Inc $0.00 $58,841 $20,314 $98,758
La Rochelle Gifts $49,523.67 $41,623 $47,924 $19,502

->pipe(new Transpose2())
    
c0Euro+ Shopping ChannelMini Gifts Distributors Ltd.Australian Collectors, Co.Muscle Machine IncLa Rochelle Gifts
Q 1 285274.59 239256.23 44894.74 0 49523.67
Q 2 249673.72 7639.1 53429.11 58841.35 41623.44
Q 3 64949.38 197065.27 0 20314.44 47924.19
Q 4 220791.85 147866.74 82261.22 98758.16 19501.82

The examples show how to use Transpose2 process. This is another type of transpose in which the first column will be used as column labels of the resulted table. It is extremely useful when you use with Cube process to generate desired result to draw charts in your report like above example.

<?php
require_once "MyReport.php";

$report = new MyReport;
$report->run()->render();
<?php
//Step 1: Load KoolReport
require_once "../../../../koolreport/autoload.php";
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Limit;
use \koolreport\processes\Sort;
use \koolreport\processes\RemoveColumn;
use \koolreport\processes\Transpose2;
use \koolreport\processes\Map;
use \koolreport\cube\processes\Cube;
use \koolreport\core\Utility as Util;

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        return array(
            "dataSources" => array(
                "dollarsales"=>array(
                    'filePath' => '../../../databases/customer_product_dollarsales2.csv',
                    'fieldSeparator' => ';',
                    'class' => "\koolreport\datasources\CSVDataSource"      
                ), 
            )
        );
    }
  
    function setup()
    {
        $node = $this->src('dollarsales')
        ->pipe(new Map(array(
            '{value}' => function($row, $metaData) {
                $row['orderQuarter'] = 'Q ' . $row['orderQuarter'];
                return array($row);
            },
            '{meta}' => function($metaData) {
                $metaData['columns']['dollar_sales'] = array(
                    'type' => 'number',
                    "prefix" => "$",
                );
                $metaData['columns']['orderQuarter'] = array(
                    'type' => 'string',
                );
                return $metaData;
            },
        )))
        ;

        $node->pipe(new Cube(array(
            "row" => "customerName",
            "column" => "orderQuarter",
            "sum" => "dollar_sales",
        )))
        
        ->pipe(new Map(array(
            '{value}' => function($row, $meta) {
                $row['H 1'] = Util::get($row, 'Q 1', 0) + Util::get($row, 'Q 2', 0);
                $row['H 2'] = Util::get($row, 'Q 3', 0) + Util::get($row, 'Q 4', 0);
                return $row;
            },
            '{meta}' => function($meta) {
                $cMetas = & $meta['columns'];
                $cMetas['Q 1']['type'] = 'number';
                $cMetas['Q 1']['decimals'] = 2;
                return $meta;
            }
        )))
        ->pipe(new Sort(array(
            '{{all}}' => 'desc'
        )))
        ->pipe(new Limit(array(
            5, 0
        )))
        ->pipe(new ColumnMeta(array(
            "{{all}}"=>array(
                "label"=>"Total",
            ),
            "customerName"=>array(
                "label"=>"Customer",
            ),
        )))
        ->pipe(new RemoveColumn(array(
            "{{all}}", 'H 1', 'H 2'
        )))
        ->saveTo($node2);
        
        $node2->pipe($this->dataStore('salesQuarterCustomer'));
        
        $node2->pipe(new Transpose2())
        ->saveTo($node3);

        $node3->pipe($this->dataStore('salesCustomerQuarter'));

        $node3->pipe(new Map(array(
            '{value}' => function($row, $meta, $index) {
                $sum = 0;
                foreach ($row as $v)
                    $sum += is_numeric($v) ? $v : 0;
                $row['Total'] = $sum;
                return $row;
            }
        )))->pipe($this->dataStore('salesCustomerQuarterAll'));

    }
}
<?php
use \koolreport\datagrid\DataTables;
use \koolreport\morris_chart;
use \koolreport\sparklines;
use \koolreport\widgets\google;
use \koolreport\widgets\koolphp\Table;
?>
<div class="report-content">
    <div class="text-center">
        <h1>Columns Transpose</h1>
        <p class="lead">
            Transpose a table using first column as transposed column names
        </p>
    </div>

    <?php
        $ds = $this->dataStore('salesQuarterCustomer');
        Table::create(array(
            'name' => 'salesQuarterCustomer',
            // "dataSource" => $data, 
            "dataSource" => $ds,
            "options" => array(
                "searching" => true,
                "paging" => true,
                "colReorder" => true,
                "order" => [],
            ),
            "showFooter" => true,
        ));
    ?>

    <i class="fa fa-arrow-down" style="font-size:24px;"></i>
    <pre style="font-weight:bold"><code>
->pipe(new Transpose2())
    </code></pre>
    <i class="fa fa-arrow-down" style="font-size:24px;"></i>
    <div style="margin-top:20px;">

    <?php
    $ds = $this->dataStore('salesCustomerQuarter');
    Table::create(array(
        'name' => 'salesCustomerQuarter',
        // "dataSource" => $data, 
        "dataSource" => $ds,
        "options" => array(
            "searching" => true,
            "paging" => true,
            "colReorder" => true,
            "order" => [],
        ),
        "showFooter" => true,
    ));
    ?>

    <?php
        google\PieChart::create(array(
            "dataStore" => $this->dataStore('salesCustomerQuarterAll'),
            "options" => array(
                'title' => 'Top 5 Customers\' Yearly Sales',
                // 'legend' => 'bottom',
                // 'is3D' => true,
            ),
            "width" => '100%',
            // 'height'=>'300px',
        ));
    ?>
</div>
customerNameproductNameproductLineorderDateorderDayorderMonthorderYearorderQuarterdollar_sales
Vitachrome Inc. 1937 Lincoln Berline Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 3726.45
Vitachrome Inc. 1936 Mercedes-Benz 500K Special Roadster Vintage Cars 2003-01-10 00:00:00 10 1 2003 1 1768.3300000000002
Baane Mini Imports 1952 Alpine Renault 1300 Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5571.8
Baane Mini Imports 1962 LanciaA Delta 16V Classic Cars 2003-01-29 00:00:00 29 1 2003 1 5026.14
Baane Mini Imports 1958 Setra Bus Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3284.28
Baane Mini Imports 1940 Ford Pickup Truck Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 3307.5
Baane Mini Imports 1926 Ford Fire Engine Trucks and Buses 2003-01-29 00:00:00 29 1 2003 1 1283.48
Baane Mini Imports 1913 Ford Model T Speedster Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2489.13
Baane Mini Imports 1934 Ford V8 Coupe Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2164.4
Baane Mini Imports 18th Century Vintage Horse Carriage Vintage Cars 2003-01-29 00:00:00 29 1 2003 1 2173

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