Pivoting

Pivot Sorting

Pivot columns support sorting. Sort rows in ascending or descending order based on any pivot column.

Note

This guide assumes familiarity with client sorting. Pivot sorting works the same way, but applies sorts to the pivot model. See the Client Row Sorting guide for details.

Sorting Pivots

To sort pivot columns, set the sort property on the pivot model. In the demo below, click a column header to sort by that pivot column. The demo uses a measure that sums Profit.

1
import "@1771technologies/lytenyte-pro/components.css";
2
import "@1771technologies/lytenyte-pro/light-dark.css";
3
import { salesData, type SaleDataItem } from "@1771technologies/grid-sample-data/sales-data";
4
import {
5
  computeField,
6
  Grid,
7
  useClientDataSource,
8
  usePiece,
9
  type PieceWritable,
10
} from "@1771technologies/lytenyte-pro";
11
import {
12
  AgeGroup,
13
  CostCell,
14
  CountryCell,
15
  DateCell,
16
  GenderCell,
17
  Header,
18
  NumberCell,
19
  ProfitCell,
20
} from "./components.jsx";
21
import { sum } from "es-toolkit";
22
import { useMemo, useState } from "react";
23
import { RowGroupCell } from "@1771technologies/lytenyte-pro/components";
24

25
export interface GridSpec {
26
  readonly data: SaleDataItem;
27
  readonly api: {
28
    sorts: PieceWritable<{ id: string; dir: "asc" | "desc" } | null>;
29
  };
30
}
31

32
export const columns: Grid.Column<GridSpec>[] = [
33
  { id: "date", name: "Date", cellRenderer: DateCell, width: 110 },
34
  { id: "age", name: "Age", type: "number", width: 80 },
35
  { id: "ageGroup", name: "Age Group", cellRenderer: AgeGroup, width: 160 },
36
  { id: "customerGender", name: "Gender", cellRenderer: GenderCell, width: 80 },
37
  { id: "country", name: "Country", cellRenderer: CountryCell, width: 150 },
38
  { id: "orderQuantity", name: "Quantity", type: "number", width: 60 },
39
  { id: "unitPrice", name: "Price", type: "number", width: 80, cellRenderer: NumberCell },
40
  { id: "cost", name: "Cost", width: 80, type: "number", cellRenderer: CostCell },
41
  { id: "revenue", name: "Revenue", width: 80, type: "number", cellRenderer: ProfitCell },
42
  { id: "profit", name: "Profit", width: 80, type: "number", cellRenderer: ProfitCell },
43
  { id: "state", name: "State", width: 150 },
44
  { id: "product", name: "Product", width: 160 },
45
  { id: "productCategory", name: "Category", width: 120 },
46
  { id: "subCategory", name: "Sub-Category", width: 160 },
47
];
48

49
const base: Grid.ColumnBase<GridSpec> = { width: 160, widthFlex: 1 };
50

51
const group: Grid.RowGroupColumn<GridSpec> = {
52
  headerRenderer: Header,
53
  cellRenderer: RowGroupCell,
54
  width: 200,
55
  pin: "start",
56
};
57

58
const aggSum: Grid.T.Aggregator<GridSpec["data"]> = (field, data) => {
59
  const values = data.map((x) => computeField<number>(field, x));
60
  return sum(values);
61
};
62

63
export default function PivotDemo() {
64
  const [pivotSorts, setPivotSorts] = useState<{ id: string; dir: "asc" | "desc" } | null>({
65
    id: "Adults (35-64)-->profit",
66
    dir: "desc",
67
  });
68
  const sorts = usePiece(pivotSorts, setPivotSorts);
69

70
  const sortDimension = useMemo(() => {
71
    if (!pivotSorts) return null;
72

73
    return [
74
      { dim: { id: pivotSorts.id }, descending: pivotSorts.dir === "desc" },
75
    ] satisfies Grid.T.DimensionSort<GridSpec["data"]>[];
76
  }, [pivotSorts]);
77

78
  const ds = useClientDataSource<GridSpec>({
79
    data: salesData,
80
    pivotMode: true,
81
    pivotModel: {
82
      columns: [{ id: "ageGroup" }],
83
      rows: [{ id: "country" }, { id: "productCategory" }],
84
      measures: [
85
        {
86
          dim: {
87
            id: "profit",
88
            name: "Profit",
89
            type: "number",
90
            cellRenderer: ProfitCell,
91
            width: 200,
92
            headerRenderer: Header,
93
          },
94
          fn: "sum",
95
        },
96
      ],
97
      sort: sortDimension,
98
    },
99
    rowGroupDefaultExpansion: true,
100
    aggregateFns: { sum: aggSum },
101
  });
102

103
  const pivotProps = ds.usePivotProps();
104
  const apiExtension = useMemo(() => ({ sorts }), [sorts]);
105
  return (
106
    <>
107
      <div className="ln-grid" style={{ height: 500 }}>
108
        <Grid
109
          apiExtension={apiExtension}
110
          columns={columns}
111
          rowSource={ds}
112
          columnBase={base}
113
          rowGroupColumn={group}
114
          {...pivotProps}
115
        />
116
      </div>
117
    </>
118
  );
119
}

1
import type { Grid } from "@1771technologies/lytenyte-pro";
2
import type { GridSpec } from "./demo";
3
import { format, isValid, parse } from "date-fns";
4
import { countryFlags } from "@1771technologies/grid-sample-data/sales-data";
5
import { clsx, type ClassValue } from "clsx";
6
import { twMerge } from "tailwind-merge";
7
import { ArrowDownIcon, ArrowUpIcon } from "@radix-ui/react-icons";
8

9
export function Header({ api, column }: Grid.T.HeaderParams<GridSpec>) {
10
  const sorts = api.sorts.useValue();
11
  const sort = sorts?.id === column.id;
12
  const dir = sorts?.dir;
13

14
  return (
15
    <div
16
      className="group relative flex h-full w-full cursor-pointer items-center text-sm transition-colors"
17
      onClick={() => {
18
        const nextSort = dir === "desc" ? "asc" : dir === "asc" ? null : "desc";
19
        if (nextSort === null) api.sorts.set(null);
20
        else api.sorts.set({ id: column.id, dir: nextSort });
21
      }}
22
    >
23
      <div
24
        className={tw(
25
          "sort-button flex w-full items-center gap-2 rounded py-1 transition-colors",
26
          column.type === "number" && "flex-row-reverse",
27
        )}
28
      >
29
        {column.name ?? column.id}
30

31
        {sort && dir === "asc" && <ArrowUpIcon className="text-ln-text-dark size-4" />}
32
        {sort && dir === "desc" && <ArrowDownIcon className="text-ln-text-dark size-4" />}
33
      </div>
34
    </div>
35
  );
36
}
37

38
export function DateCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
39
  const field = api.columnField(column, row);
40

41
  if (typeof field !== "string") return "-";
42

43
  const dateField = parse(field as string, "MM/dd/yyyy", new Date());
44

45
  if (!isValid(dateField)) return "-";
46

47
  const niceDate = format(dateField, "yyyy MMM dd");
48
  return <div className="flex h-full w-full items-center tabular-nums">{niceDate}</div>;
49
}
50

51
export function AgeGroupPivotHeader({ column }: Grid.T.HeaderParams<GridSpec>) {
52
  const field = column.name ?? column.id;
53

54
  if (field === "Under 25") return <div className="text-[#944cec] dark:text-[#B181EB]">Under 25</div>;
55
  if (field === "25-34") return <div className="text-[#aa6c1a] dark:text-[#E5B474]">25-34</div>;
56
  if (field === "35-64") return <div className="text-[#0f7d4c] dark:text-[#52B086]">35-64</div>;
57

58
  if (field === "Grand Total") return "Grand Total";
59

60
  return "Other";
61
}
62

63
export function AgeGroup({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
64
  const field = api.columnField(column, row);
65

66
  if (field === "Under 25") return <div className="text-[#944cec] dark:text-[#B181EB]">Under 25</div>;
67
  if (field === "25-34") return <div className="text-[#aa6c1a] dark:text-[#E5B474]">25-34</div>;
68
  if (field === "35-64") return <div className="text-[#0f7d4c] dark:text-[#52B086]">35-64</div>;
69

70
  return "-";
71
}
72

73
export function GenderCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
74
  const field = api.columnField(column, row);
75

76
  if (field === "M")
77
    return (
78
      <div className="flex h-full w-full items-center gap-2">
79
        <div className="flex size-6 items-center justify-center rounded-full bg-blue-500/50">
80
          <span className="iconify ph--gender-male-bold size-4" />
81
        </div>
82
        M
83
      </div>
84
    );
85

86
  if (field === "F")
87
    return (
88
      <div className="flex h-full w-full items-center gap-2">
89
        <div className="flex size-6 items-center justify-center rounded-full bg-pink-500/50">
90
          <span className="iconify ph--gender-female-bold size-4" />
91
        </div>
92
        F
93
      </div>
94
    );
95

96
  return "-";
97
}
98

99
function tw(...c: ClassValue[]) {
100
  return twMerge(clsx(...c));
101
}
102

103
const formatter = new Intl.NumberFormat("en-US", {
104
  maximumFractionDigits: 2,
105
  minimumFractionDigits: 0,
106
});
107
export function ProfitCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
108
  const field = api.columnField(column, row);
109

110
  if (typeof field !== "number") return "-";
111

112
  const formatted = field < 0 ? `-$${formatter.format(Math.abs(field))}` : "$" + formatter.format(field);
113

114
  return (
115
    <div
116
      className={tw(
117
        "flex h-full w-full items-center justify-end tabular-nums",
118
        field < 0 && "text-red-600 dark:text-red-300",
119
        field > 0 && "text-green-600 dark:text-green-300",
120
      )}
121
    >
122
      {formatted}
123
    </div>
124
  );
125
}
126

127
export function NumberCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
128
  const field = api.columnField(column, row);
129

130
  if (typeof field !== "number") return "-";
131

132
  const formatted = field < 0 ? `-$${formatter.format(Math.abs(field))}` : "$" + formatter.format(field);
133

134
  return <div className={"flex h-full w-full items-center justify-end tabular-nums"}>{formatted}</div>;
135
}
136

137
export function CostCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
138
  const field = api.columnField(column, row);
139

140
  if (typeof field !== "number") return "-";
141

142
  const formatted = field < 0 ? `-$${formatter.format(Math.abs(field))}` : "$" + formatter.format(field);
143

144
  return (
145
    <div
146
      className={tw(
147
        "flex h-full w-full items-center justify-end tabular-nums text-red-600 dark:text-red-300",
148
      )}
149
    >
150
      {formatted}
151
    </div>
152
  );
153
}
154

155
export function CountryCell({ api, row, column }: Grid.T.CellRendererParams<GridSpec>) {
156
  const field = api.columnField(column, row);
157

158
  const flag = countryFlags[field as keyof typeof countryFlags];
159
  if (!flag) return "-";
160

161
  return (
162
    <div className="flex h-full w-full items-center gap-2">
163
      <img className="size-4" src={flag} alt={`country flag of ${field}`} />
164
      <span>{String(field ?? "-")}</span>
165
    </div>
166
  );
167
}

The demo applies the sort configuration to the pivot model state. As a result, rows are sorted based on the generated pivot columns. If the pivot columns change, the sort is removed because the sorted field no longer exists.

LyteNyte Grid generates IDs for pivot columns based on pivot data. For example, in the demo above, the ID of the 25-34 column is "25-34-->profit". The first segment represents the cell value from the Age Group column, and the second segment represents the ID of the applied measure, in this case Profit. Use this generated sort ID when defining a sort function for pivoted rows.

If you need predictable column IDs, use the pivotColumnProcessor property on the client data source to modify column IDs before the pivot columns are returned.

Next Steps

Pivot Filters: Filter pivots by labels or predicate conditions.
Pivot State Persistence: Persist and restore pivot state across sessions.
Measures: Aggregate and summarize row data when pivoting.

Pivot Sorting

Note

Sorting Pivots

Pivot Sorting

Next Steps

On this page