Sort pivot table PivotData C# examples

By default rows and columns in a PivotTable model are ordered by labels (A-Z).

Sort by labels

Custom order for dimension keys (labels) can be defined with PivotTable constructor that accepts IComparer<ValueKey> implementations for rows and columns (null = default A-Z comparer). The following code snippet sorts table columns by labels in descending order (Z-A):

IPivotData pvtData; // cube instance
var pvtTbl = new PivotTable( 
  new[] {"Product"},  // rows
  new[] {"Country"},  // columns
  pvtData,
  new CustomSortKeyComparer(new[] {  // rows comparer
    NaturalSortKeyComparer.Instance }),
  new CustomSortKeyComparer(new[] { // column comparer
    NaturalSortKeyComparer.ReverseInstance })  
);

CustomSortKeyComparer accepts list of IComparer<object> for each dimension specified for rows or column axis.

In some cases labels order should be defined explicitely (like month names or days of the week) with SortAsComparer:

IPivotData pvtData; // cube instance with "Year" (number) and "Month" (Jan, Feb, Mar etc) dimensions
var pvtTbl = new PivotTable( 
  new[] {"Year"},  // rows
  new[] {"Month"},  // columns
  pvtData,
  null,  // leave default A-Z order for rows
  new CustomSortKeyComparer(new[] {  // columns comparer
    new SortAsComparer(new [] {"Jan","Feb","Mar","Apr",
	  "May","Jun","Jul","Aug","Sept","Oct","Nov","Dec"})
  })  
);

Sort by values

Pivot table can be ordered by values with the following methods:

PivotTable pvtTbl;  // model from the code snippet above
pvtTbl.SortRowKeys( 3, ListSortDirection.Ascending );  // sort rows by column #3 values
pvtTbl.SortColumnKeys( 2, ListSortDirection.Ascending );  // sort columns by row #2 values

// apply the same order by dimension keys (labels) instead of indexes
pvtTbl.SortRowKeysByColumnKey( new ValueKey("Dec"), 0 /*measureIndex*/, ListSortDirection.Ascending );
pvtTbl.SortColumnKeysByRowKey( new ValueKey(2014), 0 /*measureIndex*/, ListSortDirection.Ascending );

Customize labels sorting in web pivot builder PivotData Toolkit

In ToolkitPivotBuilderMvc example (online demo) pivot table is created with help of PivotTableFactory and sorting options are provided with PivotTableConfiguration model:

IPivotData pvtData;  // cube instance
var pvtTblFactory = new PivotTableFactory();
var pvtTbl = pvtTblFactory.Create(pvtData,
  new PivotTableConfiguration() {
    Rows = new[] {"Product"},
    Columns = new[] {"Country"},

    SortByValue = new PivotTableConfiguration.AxisValuesOrder(
      PivotTableConfiguration.TableAxis.Rows,  // sort rows 
      0,  // values column index 
      ListSortDirection.Descending ),

    OrderKeys = new PivotTableConfiguration.AxisKeysOrder[] { 
      new PivotTableConfiguration.AxisKeysOrder(
        PivotTableConfiguration.TableAxis.Columns, 
        0,  // axis dimension index
        ListSortDirection.Descending )
	}	
  } );

Sorting is performed by PivotTableFactory and if custom comparers should be used for certain dimensions they may be specified with RegisterDimensionComparer method:

var pvtTblFactory = new PivotTableFactory();
pvtTblFactory.RegisterDimensionComparer("Month", 
  new SortAsComparer(
    new [] {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sept","Oct","Nov","Dec"}
  ) 
);