Hi Wessel,

1 . You wouldn't need to set column meta for the sum percent field because the Pivot process would do that for you ('decimals' => 2, 'suffix' => '%').

2 . I think you also don't need the Group process before the Pivot process so the code could look like:

...
            ->pipe(new TimeBucket(array(
                "date" => "year",
                "date2" => "quarter"
            )))
            ->pipe(new Pivot(array(
                'dimensions' => array(
                    'row' => 'project.duodeka_type_name, project.customer.name',
                    'column' => 'date, date2'
                ),
                'aggregates' => array(
                    'sum' => 'worked_hours',
                    'sum percent' => 'worked_hours', //you could use sum percent on the same column
                )
            )))
            ->pipe($this->dataStore("duodeka_cockpit")); // cockpit. main pivot table

For changing the data field names please try:

        PivotTable::create(array(
            ...
            'map' => array(
                'dataField' => function($dataField, $fieldInfo) {
                    $v = $dataField;
                    if ($v === 'worked_hours - sum')
                        $v = 'Hours';
                    else if ($v === 'worked_hours - sum percent')
                        $v = 'Percent';
                    return $v;
                },
            ),
            ...
        ));

3 . You can't set css property in column meta. To make the pivot row and column headers align left (which should be default but it seems your css theme sets text-align: center for table td), please add the following css rules to your page:

<style>
    .pivot-row-header,
    .pivot-column-header {
        text-align: left !important;
    }
</style>

4 . I'm not sure about your final question but if you expend the column headers 2018 and 2019 you could see their total columns. If you don't want them to collapse by default, please remove this option from your view file:

"columnCollapseLevels" => array(0),

Thanks!

Hi Wessel,

I think you have syntax problem with these commands:

$cellInfo["formattedValue"] => round($value,0) . "%";
...
$cellInfo["formattedValue"] => round($value,0);

They should be:

$cellInfo["formattedValue"] = round($value,0) . "%";
...
$cellInfo["formattedValue"] = round($value,0);

As for your sum percent per year column problem, it's a bit complicated. We will see if a solution could be found for you. Thanks!

Hi Wessel,

I've managed to find a solution for your percent per column problem. This needs the latest version of Pivot which is 6.0.0:

            $delimiter = " || ";
            $this->src("flatModel")
            ...
            ->pipe(new copyColumn(array(
                "date2" => "date",
                "worked_hours2" => "worked_hours"
            )))
            ...
            ->pipe(new \koolreport\pivot\processes\Pivot2D(array(
                'dimensions' => array(
                    'row' => 'project.duodeka_type_name, project.customer.name',
                    'column' => 'date, date2'
                ),
                'aggregates' => array(
                    'sum' => 'worked_hours, worked_hours2',
                ),
                "fieldDelimiter" => $delimiter,
            )))
            ->pipe(new \koolreport\processes\Map([
            '{value}' => function ($row, $meta, $index, $mapState) use ($delimiter) {
                //save all rows to map state
                $all = Util::get($mapState, 'all', []);
                array_push($all, $row);
                $mapState['all'] = $all;

                //find the grand total row
                $label = Util::get($row, 'label', '');
                $label = explode($delimiter, $label);
                $isGrandTotalRow = true;
                foreach ($label as $rowField) {
                    if ($rowField !== '{{all}}') {
                        $isGrandTotalRow = false;
                        break;
                    }
                }
                if ($isGrandTotalRow) {
                    $mapState['grandTotalRow'] = $row;
                } 
                
                //save map state
                return [ '{state}' => $mapState ];
            },
            '{end}' => function($count, $mapState) use ($delimiter) {
                $grandRow = Util::get($mapState, 'grandTotalRow', []);
                $all = Util::get($mapState, 'all', []);
                // Loop through all rows, divide each row's worked_hours2 - sum value
                // by the grand row's worked_hours2 - sum value
                foreach ($all as & $row) {
                    foreach ($row as $k => $v) {
                        if ($k === 'label') continue;
                        $colFields = explode($delimiter, $k);
                        $dataField = end($colFields);
                        if ($dataField !== 'worked_hours2 - sum') continue;
                        $grand = (float) Util::get($grandRow, $k, 0);
                        if ($grand == 0) $grand = 1;
                        if (isset($row[$k])) $row[$k] = 
                            number_format(($v / $grand) * 100, 2) . " %";
                    }
                }
                return $all;
            }
        ]))
            ->pipe($this->dataStore("duodeka_cockpit")); // cockpit. main pivot table

Then in the view file, just map the "worked_hours2 - sum" field to "percent" like this:

PivotTable::create([
    ...
    'map' => [
            ...
            'dataField' => function($dataField, $fieldInfo) {
                    $v = $dataField;
                    if ($v === 'worked_hours - sum')
                        $v = 'Hour';
                    else if ($v === 'worked_hours2 - sum')
                        $v = 'Percent';
                    return $v;
                },
                'dataHeader' => function($dataField, $fieldInfo) {
                    $v = $dataField;
                    if ($v === 'worked_hours - sum')
                        $v = 'Hour';
                    else if ($v === 'worked_hours2 - sum')
                        $v = 'Percent';
                    return $v;
                },
    ...

Please try this solution and let us know if you need anything more. Thanks!

Hi Wessel,

It seems we have a silly bug relating to dimensions' order in Pivot2D process. Please put the "column" dimension before the "row" dimension when piping Pivot2D like this:

            ->pipe(new \koolreport\pivot\processes\Pivot2D(array(
                'dimensions' => array(
                     'column' => 'date, date2',
                    'row' => 'project.duodeka_type_name, project.customer.name'                   
                ),
                ...
            )))

We will fix this issue in the next version of Pivot. For now please try this workaround and let us know if there's still any issue. Thanks!