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 - 1 comments

entahsiapa commented on Dec 3

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

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!

KoolReport Is Free and Open-Source!

KoolReport is a professional php reporting framework which saves you tons of time to construct dynamic data report & dashboard.

  • Connect to various datasources such as MySQL, SQL Server, MongoDB or even from CSV or Excel file.
  • Contain series of powerful built-in data processes for your data manipulation.
  • Generate stunning charts and graphs to help you communicate data insights to your audiences effectively.
  • Integrate seamlessly with any php frameworks such as Laravel, CodeIgniter, Symfony.
Download Now and register our tutorials to get started!

Download KoolReport
help needed
suggestion

Pivot