# useDocyrusPivotGrid
URL: /docs/web/hooks/use-docyrus-pivot-grid

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

`useDocyrusPivotGrid` fetches data from a Docyrus items endpoint and feeds it into the `usePivotGrid` controller that `` 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

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

## How it works

### 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:

```json
{
  "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:

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

The full server-mode payload looks like:

```json
{
  "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.

```tsx
'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>
      
      
      
    </div>
  );
}
```

## API Reference

### `UseDocyrusPivotGridOptions`

| Prop | Type | Default | Description |
|------|------|---------|-------------|
| `client` | `RestApiClient` | — | Authenticated Docyrus API client. |
| `appSlug` | `string` | — | App slug (e.g. `'base'`). |
| `dataSourceSlug` | `string` | — | Data source slug (e.g. `'time_entry'`). |
| `mode` | `'client' \| 'server'` | `'client'` | Aggregation mode. |
| `rowDimensions` | `DocyrusPivotGridDimension[]` | — | Row grouping axes. |
| `columnDimensions` | `DocyrusPivotGridDimension[]` | — | Column grouping axes. |
| `measures` | `DocyrusPivotGridMeasure[]` | — | Values to aggregate in each cell. |
| `columns` | `string` | auto-derived | Columns expression forwarded to the items endpoint. |
| `filters` | `unknown` | — | Server-side filters forwarded verbatim. |
| `orderBy` | `string` | — | Client mode only. |
| `limit` | `number` | `5000` | Client mode only. Max records to fetch. |
| `getRowId` | `(row, index) => string` | — | Stable row identifier for `usePivotGrid`. |
| `initialState` | `Partial<PivotGridState>` | — | Initial expand/pin/size state. |
| `cellColorRules` | `PivotGridCellColorRule[]` | — | Formula-based cell colour rules. |
| `drilldown` | `PivotGridDrilldown<TData>` | — | Drilldown configuration. |
| `height` | `number \| 'auto'` | `600` | Grid height in pixels or `'auto'`. |
| `enabled` | `boolean` | `true` | Enable/disable the query. |
| `staleTime` | `number` | `30000` | TanStack Query stale time in ms. |

### `DocyrusPivotGridDimension`

| Prop | Type | Description |
|------|------|-------------|
| `id` | `string` | Unique id used internally and as server alias prefix (`dim_<id>`). |
| `label` | `string` | Header label. |
| `field` | `string` | Field slug on the data source. |
| `subField` | `string` | Sub-field for relation/userSelect fields (e.g. `'name'`). |
| `dateFormat` | `string` | Postgres `to_char` token for server-mode date bucketing (e.g. `'YYYY-MM'`). |
| `dateRange` | `{ interval, increment?, min, max }` | Required when `dateFormat` is set. Use `interval: 'day'` for monthly/yearly bucketing. |
| `getValue` | `(row) => unknown` | Custom extractor. Overrides server alias accessor — only set for custom logic. |
| `formatValue` | `(value) => string` | Display formatter for header values. |
| `sort` | `(a, b) => number` | Custom sort for dimension values. |
| `emptyLabel` | `string` | Label for null/empty dimension values. |

### `DocyrusPivotGridMeasure`

| Prop | Type | Description |
|------|------|-------------|
| `id` | `string` | Unique id — also used as the server-side calculation alias. |
| `label` | `string` | Column header label. |
| `field` | `string` | Field slug to aggregate (use `'id'` for count). |
| `aggregate` | `PivotGridAggregate` | Aggregation function (`'sum'`, `'count'`, `'avg'`, `'min'`, `'max'`). |
| `transform` | `(value: number) => number` | Post-aggregation transform (e.g. seconds → hours). |
| `getValue` | `(row) => number \| null` | Custom extractor — overrides field accessor. |
| `formatValue` | `(value: number) => string` | Display formatter for cell values. |

### `UseDocyrusPivotGridResult`

| Property | Type | Description |
|----------|------|-------------|
| `controller` | `PivotGridController<TData>` | Pass to ``. |
| `items` | `TData[]` | Raw records (client mode) or aggregated rows (server mode). |
| `isLoading` | `boolean` | True on the first fetch. |
| `isFetching` | `boolean` | True on any background refetch. |
| `error` | `Error \| null` | Query error if any. |
| `refetch` | `() => void` | Manually trigger a refetch. |

## Server-mode notes

- **`getValue` on dimensions**: in server mode, the hook auto-generates `(row) => row['dim_<id>']` for each dimension. Set `getValue` only when you need custom transformation logic on the server-returned value.
- **`dateRange.interval`**: use `'day'` even for monthly/yearly bucketing. The backend `generate_series` uses this as the axis step; `to_char` then groups days into the desired bucket. Using `'month'` only matches records that fall exactly on the first day of each month.
- **`columns` in server mode**: defaults to `'id'`. Override only if you need extra fields in the main query alongside the pivot payload.