Sort Pivot Table - KoolReport

Applicatta commented on May 15, 2019

Hi, It is possible to sort a Pivot Table by a row name that it's not in the table but in the data?

I have this data:

Array (

[0] => Array
    (  [year] => 2019
        [month] => January
        [month_order] => 1
        [amount] => 92
    )
[1] => Array
    (  [year] => 2019
        [month] => January
        [month_order] => 1
        [amount] => 120
    )
[2] => Array
    (   [year] => 2019
        [month] => January
        [month_order] => 1
        [amount] => 101
    )
[3] => Array
    (   [anio] => 2019
        [month] => February
        [month_order] => 2
        [amount] => 75
    )
[4] => Array
    (   [year] => 2019
        [month] => February
        [month_order] => 2
        [amount] => 90
    )
[5] => Array
    (   [year] => 2019
        [month] => February
        [month_order] => 2
        [amount] => 100
    )
[6] => Array
    (   [year] => 2019
        [month] => March
        [month_order] => 3
        [amount] => 150
    )

)

And i need to group by "year" and "month", but order by "month_order".

I hope you can unsderstand. Thanks!

David Winterburn commented on May 16, 2019

Hi,

There're 2 solutions for your case:

Group by "year" and "month". Then use the "rowSort" and "columnSort" properties of PivotTable to sort the "month" field using "month_order" instead of by "asc" or "desc":

//MyReport.php
->pipe(new Pivot(array(
	"dimensions" => array(
		"column" => "year",
		"row" => "month",
	),
	"aggregates" => array(
		"sum" => "dollar_sales",
	),
)))

//MyReport.view.php
$monthOrder = [
	'January' => 1,
	'February' => 2,
	...
];
PivotTable::create(array(
	"dataStore"=>$ds,
	'columnSort' => array(
		'month' => function($a, $b) use($monthOrder) {
			return $monthOrder[$a] <= $monthOrder[$b];
		},
	),
));

Group by "year" and "month_order". Then use the "map" property of PivotTable to change the "month_order" field value to its respective month name:

//MyReport.php
->pipe(new Pivot(array(
	"dimensions" => array(
		"column" => "year",
		"row" => "month_order",
	),
	"aggregates" => array(
		"sum" => "dollar_sales",
	),
)))

//MyReport.view.php
$monthName = [
	1 => 'January',
	2 => 'February',
	...
];
PivotTable::create(array(
	"dataStore"=>$ds,
	'map' => array(
		'rowHeader' => function($rowHeader, $headerInfo) use ($monthName) {
			if ($headerInfo['fieldName'] === 'month_order')
				return $monthName[$rowHeader];
			else
				return $rowHeader;
		},
		'columnHeader' => function($colHeader, $headerInfo) use ($monthName) {
			if ($headerInfo['fieldName'] === 'month_order')
				return $monthName[$colHeader];
			else
				return $colHeader;
		},
	),
));

Let us know if this helps you solve your problem. Thanks!

Applicatta commented on May 16, 2019

Yees!! Thanks so much, you solved my problem, i used the first solution you gave me and it worked perfect. Thank you very much.

KoolReport's Forum

Sort Pivot Table #874

Build Your Excellent Data Report