Sorting

Flexmonster Pivot allows you to sort field members and values. This guide describes which sorting options are available in Flexmonster and how to configure them.

You can also check out our video tutorial to see how sorting works.

Sorting in the pivot table and in the chart view

This section describes how to sort members and values in the classic form, the compact form, and the chart view.

Sorting members

Members can have ascending, descending, or unsorted order. Based on the field’s type, members will be sorted alphabetically, numerically, or by dates.

Note By default, field members are sorted in ascending order. To override the default sorting order, use the options.defaultHierarchySortName property Live example.

Sorting can be set via UI, in the report, or using API calls:

Via UI

Step 1. Open the filter view.

Step 2. Use the AZ and ZA toggle buttons to sort field members in ascending and descending orders. To display members in unsorted order, deselect an active toggle.

Step 3. To save the configuration, click the APPLY button.

In the report

Specify the sorting type in the sort property when configuring the slice:

slice: {
  rows: [
    {
      uniqueName: "Category",
      sort: "desc",
    },
  ],
  // Other slice configs
}

Live example

Using API calls

Set the sorting type using the setSort() API call:

pivot.setSort("Category", "desc");

Live example

To see the current sorting, use getSort().

Sorting values

Sorting for values can be set via UI, in the report, or using API calls:

Via UI

You can sort a specific row or column on the grid using sorting arrows, which appear when hovering over field headers. Click the arrow the first time to sort in descending order and the second time for ascending.

Sorting can also be managed through the context menu of each member cell.

To clear sorting, use the context menu of the needed member cell.

Note Sorting values directly in the chart view is not supported. To sort the data, configure the sorting through the сompact or classic view.

In the report

If you want to sort values in a specific row or column in your report, we recommend the following approach:

1. Sort values via UI.
2. Save the report.

Your report will now contain the sorting configurations in the slice.sorting property. If needed, you can edit the configurations programmatically. To learn more, see the SortingObject.

Using API calls

Sort a specific row or column using the sortValues() API call:

pivot.sortValues(
  "columns",
  "asc",
  ["category.[bikes]", "color.[red]"],
  {"uniqueName": "Price"}
);

Live example

Sorting in the flat form

In the flat table, you can sort a column via UI, in the report, or using API calls. It is also possible to sort multiple columns at once.

slice: {
  flatSort: [
    {
      uniqueName: "Category",
      sort: "desc",
    },
    {
      uniqueName: "Price",
      sort: "asc",
    },
  ],
  // Other slice configs
}

let sort = [
  {
    uniqueName: "Category",
    sort: "desc"
  },
  {
    uniqueName: "Price",
    sort: "asc"
  }
];

pivot.setFlatSort(sort);

Overriding the default sort order

In Flexmonster, it is possible to override the default ascending order to set the custom order for field members. For example, you can make sure that some members are always kept at the end or at the start of the list.

Custom ascending order can be set using the sortingMethod() API call or the sortOrder property in the slice (works only for JSON and CSV).

Using the sortOrder property (only for JSON and CSV)

You can set the custom ascending order for the field members using the sortOrder property from the slice. This feature is supported for JSON and CSV data source types.

For example, we specified a slice with the "Category" field in rows. This field has the following members: "Accessories", "Bikes", "Clothing", "Components", and "Cars". Let’s override their default sorting order using the sortOrder property:

slice: {
  rows: [
    {
      uniqueName: "Category",
      sortOrder: [
        "Bikes",
        "Cars",
        "Clothing",
        "Accessories",
        "Components",
      ],
    },
  ],
  // Other slice configs
}

As a result, field members will be displayed in the specified order when sorted in ascending order. If the descending sorting is applied, the members’ order will be the opposite to the one defined in the sortOrder property:

If you remove sorting in both ascending and descending order, field members will be displayed in the order they came from the data source.

Live example

Using the sortingMethod() API call

The custom ascending sort order can be set using the sortingMethod() API call.

In the example below, members that begin with a specific letter (e.g., "F") are put first, while all other members remain in alphabetical order:

const letterToPutFirst = "F";

pivot.sortingMethod("Contact Last Name", function(a, b) {
  if (a.at(0) == letterToPutFirst && b.at(0) == letterToPutFirst)
    return a > b ? 1 : -1;
  if (a.at(0) == letterToPutFirst)
    return -1;
  if (b.at(0) == letterToPutFirst)
    return 1;
  return a > b ? 1 : -1;
});

The result will look similar to the following:

If you remove sorting, field members will be displayed in the order they came from the data source.

Live example

Sorting order in OLAP

For SSAS, ascending order is the order defined inside the cube, and descending order is the reverse order from the cube. You can override the ascending order using the sortingMethod() API call.

Managing the visibility of the sorting controls

You can specify where the sorting arrows are visible using the sorting option. Possible values:

• In rows ("rows").
• In columns ("columns").
• In rows and columns ("on" or true).
• Not visible at all ("off" or false).

For example:

report: {
  dataSource: {
    filename: "data/data.csv",
  },
  options: {
    sorting: "columns"
  },
}

Live example

Sorting in the Field List

You can sort the Field List items using the sortFieldList() API call.

In the example below, we sort the Field List items in the reverse alphabetical order:

pivot.sortFieldsList(function(firstItem, secondItem, viewType) {
  return secondItem.caption.localeCompare(firstItem.caption);
});

Live example

You can also define sortFieldsList() as an initialization parameter:

const pivot = new Flexmonster({
  container: "pivotContainer",
  report: {
    dataSource: {
      filename: "data/data.csv"
    },
  },
  sortFieldsList: function(firstItem, secondItem, viewType) {
    return secondItem.caption.localeCompare(firstItem.caption);
  }
});

See also

• Managing Flexmonster's functionality
• Configuring the slice
• Overview of the filtering features