Hello,
I'm trying to format my PivotTable values. I've tried to use a ColumnMeta before piping the data throught the Pivot process and I've tried to use dataMap to format the values. Unfortunately I remain unsuccesful.
I'm want to round all values to whole numbers (without decimals), add a % suffix to "%_hours - sum percent" and change the datafield names to "hours" and "percent".
This is my piping process:
$this->src("flatModel")
->pipe(new OnlyColumn(array(
"project.duodeka_type_name",
"project.customer.name",
"date",
"worked_hours",
)))
->pipe(new copyColumn(array(
"date2" => "date",
"%_hours" => "worked_hours"
)))
->pipe(new TimeBucket(array(
"date" => "year",
"date2" => "quarter"
)))
->pipe(new Group(array(
"by" => array("project.duodeka_type_name", "project.customer.name", "date", "date2"),
"sum" => "worked_hours"
)))
->pipe(new ColumnMeta(array(
"worked_hours" => array(
"type" => "number",
"decimals" => 0,
"thousandSeparator" => ".",
"decimalPoint" => ",",
),
"%_hours" => array(
"type" => "number",
"decimals" => 0,
"suffix" => "%"
),
"project.duodeka_type_name" => array(
"align" => "left"
),
"project.customer.name" => array(
"align" => "left"
)
)))
->pipe(new Pivot(array(
'dimensions' => array(
'row' => 'project.duodeka_type_name, project.customer.name',
'column' => 'date, date2'
),
'aggregates' => array(
'sum' => 'worked_hours',
'sum percent' => '%_hours'
)
)))
->pipe($this->dataStore("duodeka_cockpit")); // cockpit. main pivot table
And here's my view:
PivotTable::create(array(
"dataStore"=>$report->dataStore("duodeka_cockpit"),
"rowDimension"=>"row",
"rowSort" => array("worked_hours - sum" => "desc",),
"rowCollapseLevels" => array(0),
"columnDimension"=>"column",
"columnSort" => array("year, quarter" => "asc"),
"columnCollapseLevels" => array(0),
"measures"=>array(
"worked_hours - sum",
"%_hours - sum percent"),
"width" => "100%",
"template"=>"PivotTable-Bun",
'showDataHeaders'=>true,
));
Oh and another question: I would like to see a total per year, instead of a full total of the table. right now the total is split between 2018 and 2019. How can I achieve this?
Kind regards,
Wessel