KoolReport's Forum

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

Sort Pivot Table #874

Closed Applicatta opened this topic on on May 15 - 2 comments

Applicatta commented on May 15

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

Hi,

There're 2 solutions for your case:

  1. 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];
		},
	),
));
  1. 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 7 days ago

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

Give tips to supporter for his good work

If you feel that supporter has done a good work, consider giving him some credit. Any amount put into the tips box below is appreciated. By doing so, you have contribtuted to the existence of KoolReport and the quality of support.

Tips box
help needed
solved

Pivot