useDocyrusPivotGrid

Wires the PivotGrid component to a Docyrus data source with optional server-side aggregation via pivot.matrix + calculations.

Edit on GitHubEditllms.txt

useDocyrusPivotGrid fetches data from a Docyrus items endpoint and feeds it into the usePivotGrid controller that <PivotGridView /> consumes. It supports two modes:

'client' (default) — fetches raw records and aggregates in the browser. Simple to set up; suitable for datasets up to ~5 000 rows.
'server' — sends a pivot.matrix + calculations payload so the database aggregates. Returns one row per dimension combination; scales to any dataset size.

Installation

pnpm dlx @docyrus/cli add @docyrus/hooks-use-docyrus-pivot-grid

No schema fetch. Unlike the grid / table / kanban / gallery / map hooks, useDocyrusPivotGrid never calls getBySlug — it works purely off the items query plus the dimension/measure config you pass. It is therefore already metadata-free and needs no dataSource injection. (Dimensions and measures are declared by the caller, not derived from a data-source schema.)

Client mode

The hook builds a standard columns + optional filters / orderBy / limit query, fetches raw records, then hands them to usePivotGrid together with mapped dimension/measure descriptors. All grouping and aggregation happens in the browser.

Server mode

For each date dimension that has dateFormat + dateRange set, the hook builds a matrix entry:

{
  "using": "<field>",
  "columns": "<dimAlias>:to_char[<dateFormat>]@<field>",
  "spread": true,
  "dateRange": { "interval": "day", "min": "<min>", "max": "<max>" }
}

Important: always use interval: "day" for date bucketing (e.g. monthly). The backend generate_series creates one entry per day; the to_char token then groups them into buckets (months, years, etc.) via the SQL GROUP BY. Using interval: "month" would only match records that fall exactly on the first day of each month.

For relation/userSelect dimensions the matrix entry uses the subField directly:

{ "using": "record_owner", "columns": "dim_user:name", "spread": true }

The full server-mode payload looks like:

{
  "columns": "id",
  "pivot": {
    "matrix": [
      { "using": "record_owner", "columns": "dim_user:name", "spread": true },
      {
        "using": "date",
        "columns": "dim_month:to_char[YYYY-MM]@date",
        "spread": true,
        "dateRange": { "interval": "day", "min": "2025-11-01", "max": "2026-05-31" }
      }
    ]
  },
  "calculations": [
    { "field": "duration", "func": "sum", "name": "logged" },
    { "field": "duration_billable", "func": "sum", "name": "billable" }
  ]
}

Each result row contains dim_user, dim_month, logged, billable. The hook reads these via server aliases and passes them to usePivotGrid for rendering.

Example: `base/time_entry`

Pivot grid of logged and billable hours by user × month. Server-mode aggregation — the database returns one row per user+month combination.

'use client';

import { useMemo } from 'react';

import { useDocyrusAuth } from '@docyrus/signin';
import {
  PivotGridExportMenu,
  PivotGridToolbar,
  PivotGridView
} from '@docyrus/ui/components/pivot-grid';
import { useDocyrusPivotGrid } from '@docyrus/ui/library/hooks/use-docyrus-pivot-grid';

function secondsToHours(v: number) { return v / 3600; }
function formatHours(h: number) { return `${Math.round(h * 10) / 10}h`; }
function formatMonth(value: unknown) {
  if (typeof value !== 'string' || !value) return '—';
  const [y, m] = value.split('-').map(Number);
  return new Date(y, m - 1, 1).toLocaleDateString('en-US', { month: 'short', year: 'numeric' });
}

export function TimeEntryReportsPage() {
  const { client } = useDocyrusAuth();
  if (!client) return null;

  const dateRange = useMemo(() => {
    const now = new Date();
    const min = new Date(now.getFullYear(), now.getMonth() - 5, 1).toISOString().slice(0, 10);
    const max = new Date(now.getFullYear(), now.getMonth() + 1, 0).toISOString().slice(0, 10);
    return { interval: 'day', min, max };
  }, []);

  const rowDimensions = useMemo(() => [{
    id: 'user',
    label: 'User',
    field: 'record_owner',
    subField: 'name',
    emptyLabel: 'Unassigned'
  }], []);

  const columnDimensions = useMemo(() => [{
    id: 'month',
    label: 'Month',
    field: 'date',
    dateFormat: 'YYYY-MM',
    dateRange,
    formatValue: formatMonth,
    sort: (a: unknown, b: unknown) => String(a).localeCompare(String(b))
  }], [dateRange]);

  const measures = useMemo(() => [
    { id: 'logged', label: 'Logged (h)', field: 'duration', aggregate: 'sum' as const, transform: secondsToHours, formatValue: formatHours },
    { id: 'billable', label: 'Billable (h)', field: 'duration_billable', aggregate: 'sum' as const, transform: secondsToHours, formatValue: formatHours }
  ], []);

  const { controller } = useDocyrusPivotGrid({
    client,
    appSlug: 'base',
    dataSourceSlug: 'time_entry',
    mode: 'server',
    rowDimensions,
    columnDimensions,
    measures,
    height: 'auto'
  });

  return (
    <div>
      <PivotGridToolbar controller={controller} />
      <PivotGridView controller={controller} />
      <PivotGridExportMenu controller={controller} />
    </div>
  );
}

API Reference