KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.

{ASK} Pivot Table with calculatedcolumn #1200

Open entahsiapa opened this topic on on Dec 3, 2019 - 1 comments

entahsiapa commented on Dec 3, 2019

how to insert column with calculated

<?php
//Step 1: Load KoolReport
require_once "../../../load.koolreport.php";
use \koolreport\pivot\processes\Pivot;
use \koolreport\pivot\processes\PivotExtract;
use \koolreport\processes\ColumnMeta;
use \koolreport\processes\Filter;
use \koolreport\processes\calculatedcolumn;

//Step 2: Creating Report class
class MyReport extends \koolreport\KoolReport
{
    function settings()
    {
        $config = include "../../../config.php";
        return array(
            "dataSources" => array(
                "laporan"=>$config["laporan"]
                 )         
       );
    }

    public function setup()
    {
        $this->src('laporan')
        ->query("select * from kegiatan")
        ->pipe(new pivot(array(
            "dimensions" => array(
                "column" => "kdbeban", 
                "row" => "kdgiat, kdoutput, kdkmpnen, kdskmpnen, kdakun",
            ),
            "aggregates" => array(
                "sum" => "pagu, realisasi", 
        ),
        )))->saveto($source);

        $source->pipe($this->datastore('lapkeg'));
 
    }
}
David Winterburn commented on Dec 3, 2019

Hi there,

To add a custom column like that to Pivot please try this code:

	use \koolreport\pivot\processes\Pivot2D;

	...
	$delimiter = " || ";
	...
	->pipe(new Pivot2D(array(
		"dimensions" => array(
			"column" => "kdbeban", 
			"row" => "kdgiat, kdoutput, kdkmpnen, kdskmpnen, kdakun",
		),
		"aggregates" => array(
			"sum" => "pagu, realisasi", 
        ),
		"fieldDelimiter" => $delimiter,
	)))
	->pipe(new Map([
		'{value}' => function ($row) use ($delimiter) {
			$row["{{all}}" . $delimiter . "sum difference"] = $row["{{all}}" . $delimiter . "pagu - sum"] - $row["{{all}}" . $delimiter . "realisasi - sum"]
			return $row;
		},
	]))
	->pipe($this->datastore('lapkeg')); 

Let me know if this works for you. Thanks!

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
help needed
suggestion

Pivot