Sorting both rows and columns of pivot table

Question

Trying to have pivot table output with sorted rows and columns

I've tried different combinations of sortrowkey, col keys etc and can't find the right syntax. Latest try was making sure the data doing into the cube before the pivot was sorted and using the preservegrouporder.

var pvtTbl = new PivotTable(
    new[] { "YearQuarterMonth" },
    new[] { "StageName" },
    slicedCube);
_ = pvtTbl.PreserveGroupOrder == true;
pvtTbl.SortRowKeysByColumnKey(null, 1, ListSortDirection.Ascending);
_ = pvtTbl.PreserveGroupOrder == true;
pvtTbl.SortColumnKeysByRowKey(null, 1, ListSortDirection.Ascending);
//pvtTbl.SortRowKeys(null, 1, ListSortDirection.Ascending);
//pvtTbl.SortColumnKeys(null, 1, ListSortDirection.Ascending);
var strJsonWr = new StringWriter();
var jsonWr = new PivotTableJsonWriter(strJsonWr);

So far it seems to only apply 1 or none of the sorts, never both.

score 0 · Answer 1 · answered Aug 28 '19 at 06:04

First of all please note that when you set PreserveGroupOrder=true pivot table rows/columns are ALWAYS ordered by labels, even if you call SortRowKeysByColumnKey or SortColumnKeysByRowKey to order by values, this sort is applied only to rows/columns inside groups. In your case you have only one dimension per axis, and this option doesn't change anything.

Let's see what happens next:

pvtTbl.SortRowKeysByColumnKey(null, 1, ListSortDirection.Ascending);

here you sorted rows by row totals values (as column key that identifies a column with values to sort is null), and measure index is 1 (which means 2nd measure). Important: this means that your slicedCube should have at least 2 measures defined with CompositeAggregatorFactory.

Then you call:

pvtTbl.SortColumnKeysByRowKey(null, 1, ListSortDirection.Ascending);

which now sorts columns by column total values (again, by 2-nd measure). Sequence of calls is important, as sort by values for columns may depend on previous sort on rows.

Hope this explanation helps.

Sorting both rows and columns of pivot table

1 Answers1