LyteNyte Grid logo for light mode. Links back to the documentation home page.
Github repository for this project. 1771 Technologies home page
Pivoting

Pivot Filters

Filter pivot rows using specific labels or predicate conditions.

Note

This guide assumes familiarity with general and client data source filtering. Before continuing, read:

Demos in this guide show a measure summing Profit. Rows are pivoted by Country and Product, columns by Age Group.

Filtering Pivots

Pivoting data always creates group rows because pivots aggregate data. To apply filters to pivoted data, set the filter property on the pivot model. The filter property accepts an array of HavingFilterFn functions.

The demo below demonstrates having filter functions. You can update the filter applied to the pivot column by clicking the funnel icon in the column header.

Having filters are applied to rows at a specific depth; in this case, the child rows of countries will be filtered. Refer to the Client Row Having Filters guide for more details.

Filtering Pivots

Fork code on stack blitzFork code on code sandbox

The API extends the grid with a filter model. This model’s state is converted into a filter function and applied to the client data source’s pivot model, as shown below.

const [filter, setFilter] = useState<Record<string, GridFilter>>({});
const filterModel = usePiece(filter, setFilter);


const filterFn = useMemo<Grid.T.HavingFilterFn>(() => {
27 collapsed lines
  const entries = Object.entries(filter);


  const evaluateNumberFilter = (operator: FilterNumberOperator, compare: number, value: number) => {
    if (operator === "equals") return value === compare;
    if (operator === "greater_than") return compare > value;
    if (operator === "greater_than_or_equals") return compare >= value;
    if (operator === "less_than") return compare < value;
    if (operator === "less_than_or_equals") return compare <= value;
    if (operator === "not_equals") return value !== compare;


    return false;
  };


  return (row) => {
    for (const [column, filter] of entries) {
      const value = row.data[column as keyof GridSpec["data"]];


      // This example only supports number filters, so exclude non-number values
      if (typeof value !== "number") return false;


      const compareValue = value;


      if (!evaluateNumberFilter(filter.left.operator, compareValue, filter.left.value)) return false;
    }


    return true;
  };
}, [filter]);


const ds = useClientDataSource<GridSpec>({
  data: salesData,
  pivotMode: true,
  pivotModel: {
    columns: [{ id: "ageGroup" }],
    rows: [{ id: "country" }, { id: "productCategory" }],
    filter: filterFn,
    measures: [
12 collapsed lines
      {
        dim: {
          id: "profit",
          name: "Profit",
          type: "number",
          cellRenderer: ProfitCell,
          width: 140,
          headerRenderer: Header,
        },
        fn: "sum",
      },
    ],
  },
  rowGroupDefaultExpansion: true,
  aggregateFns: { sum: aggSum },
});

Pre-Pivot Row Filtering

By default, LyteNyte Grid does not apply standard row filters when pivoting and uses all rows to create pivot columns.

To apply standard row filters before pivoting, set pivotApplyExistingFilter. The grid filters rows first, then generates pivoted data.

In the demo below, the grid filters rows to include only entries where the Country is the United States or Germany. For row filtering details, see the Client Row Filtering guide.

Filter Before Pivot

Fork code on stack blitzFork code on code sandbox

Label Filters

Pivoting creates labels for rows and columns. Use rowLabelFilter and colLabelFilter to filter pivot results by row and column labels.

Label filters allow you to include or exclude specific identifiers from the pivot output. For details, see the Client Row Label Filters guide.

The demo below applies two label filters. The row label filter keeps only the United States and Germany. The column label filter excludes Youth (<25) age group.

Pivot Label Filters

Fork code on stack blitzFork code on code sandbox

Label filters are always applied as an array. Each item in the array filters labels at the depth corresponding to its index. This means the first item filters the top-level labels, the second item filters the next level, and so on. The code below shows the label filters used in the demo.

const ds = useClientDataSource<GridSpec>({
  data: salesData,
  pivotMode: true,
  pivotModel: {
    colLabelFilter: [(s) => s !== "Under 25"],
    rowLabelFilter: [(s) => s === "Germany" || s === "United States"],
    columns: [{ id: "ageGroup" }],
    rows: [{ id: "country" }, { id: "productCategory" }],
12 collapsed lines
    measures: [
      {
        dim: {
          id: "profit",
          name: "Profit",
          type: "number",
          cellRenderer: ProfitCell,
          width: 140,
        },
        fn: "sum",
      },
    ],
  },
  rowGroupDefaultExpansion: true,
  aggregateFns: { sum: aggSum },
});

Next Steps