# useDocyrusPivotCalendar
URL: /docs/web/hooks/use-docyrus-pivot-calendar

Wires the PivotCalendar component to a Docyrus data source using the items endpoint's pivot parameter for server-side aggregation.

`useDocyrusPivotCalendar` builds a `pivot` payload (matrix + calculations) for a Docyrus data source items endpoint, runs it via `RestApiClient`, and parses the result into the `cells` and `groups` shape that `` consumes.

The matrix is rebuilt automatically as the user navigates views (month-calendar / days-of-week / days-of-month / months-of-year) and dates, so all aggregation happens on the server with one round-trip per change.

## Installation

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

## How it builds the request

For a given `view` + reference `date`, the hook constructs a payload like:

```json
{
  "pivot": {
    "matrix": [
      {
        "using": "<dateField>",
        "columns": "bucket:to_char[YYYY-MM-DD]@<dateField>",
        "spread": true,
        "dateRange": { "interval": "day", "min": "<rangeStart>", "max": "<rangeEnd>" }
      },
      {
        "using": "<groupField>",
        "columns": "groupLabel:<labelField>",
        "spread": true
      }
    ]
  },
  "calculations": [
    { "field": "<measure.field>", "func": "<measure.func>", "name": "<measure.id>" }
  ]
}
```

| View | `dateRange.interval` | `bucket` token |
|------|----------------------|----------------|
| `month-calendar` | `day` | `YYYY-MM-DD` |
| `days-of-week` | `day` | `YYYY-MM-DD` |
| `days-of-month` | `day` | `YYYY-MM-DD` |
| `months-of-year` | `month` | `YYYY-MM` |

Each result row contains `bucket`, optional `groupLabel`, and one column per measure (named after `measure.id`). The hook maps each row to an `IPivotCalendarRemoteCell`. The relation's primary key is implicit on the SQL join — by default the hook uses the displayed `groupLabel` (e.g. user name) as the stable group key.

When `groupBy.idField` is set the matrix also exposes a non-join key column (e.g. `'user_id'` for user relations) as `groupId:<idField>`. The hook then uses that resolved UUID as the cell's `groupId` so drilldown queries can filter by `<groupBy.field> = <uuid>` directly. The relation's actual `id` column **cannot** be aliased — it is the SQL join key — so `idField` must point at a separate column on the related data source.

## Example: `base/time_entry`

Wires `` to the `base/time_entry` data source on the Docyrus tenant. Two measures (logged + billable durations, both stored in seconds) are aggregated server-side with `sum`, then converted to hours and formatted as `1.5h` for display. Time entries are grouped by `record_owner` so each user becomes a row in the pivot views.

```tsx
'use client';

import { useDocyrusAuth } from '@docyrus/signin';
import { Clock, RefreshCw } from 'lucide-react';

import { PivotCalendar } from '@docyrus/ui/components/pivot-calendar';
import { useDocyrusPivotCalendar } from '@docyrus/ui/library/hooks/use-docyrus-pivot-calendar';
import { Button } from '@docyrus/ui/primitives/ui/button';
import { Spinner } from '@docyrus/ui/primitives/ui/spinner';

const APP_SLUG = 'base';
const DATA_SOURCE_SLUG = 'time_entry';

function secondsToHours(value: number): number {
  return value / 3600;
}

function formatHours(hours: number): string {
  if (!hours) return '0h';
  const rounded = Math.round(hours * 10) / 10;

  return `${rounded}h`;
}

export function TimeEntriesPage() {
  const { client } = useDocyrusAuth();

  if (!client) return null;

  return ;
}

function TimeEntriesPageInner({
  client
}: {
  client: NonNullable
        </div>
      </div>

      {error ? (
        <div className="rounded-md border border-destructive/40 bg-destructive/5 p-3 text-sm text-destructive">
          Failed to load time entries: {error.message}
        </div>
      ) : null}

      <div className="flex min-h-0 flex-1 overflow-hidden">
        
      </div>
    </div>
  );
}
```

### Generated request

For the **month-calendar** view of May 2026, the hook sends the following payload to `GET /v1/apps/base/data-sources/time_entry/items`:

```json
{
  "columns": "...record_owner(name)",
  "pivot": {
    "matrix": [
      {
        "using": "date",
        "columns": "bucket:to_char[YYYY-MM-DD]@date",
        "spread": true,
        "dateRange": {
          "interval": "day",
          "min": "2026-05-01T00:00:00.000Z",
          "max": "2026-05-31T23:59:59.999Z"
        }
      },
      {
        "using": "record_owner",
        "columns": "groupLabel:name, groupId:user_id",
        "spread": true
      }
    ]
  },
  "calculations": [
    { "field": "duration",          "func": "sum", "name": "logged" },
    { "field": "duration_billable", "func": "sum", "name": "billable" }
  ]
}
```

### Sample response row

```json
{
  "bucket": "2026-05-06",
  "groupLabel": "Cameron Shaw",
  "groupId": "aeafdd7a-9217-4438-8e70-d3ac6d9b709a",
  "name": "Cameron Shaw",
  "logged": "10800",
  "billable": "9000"
}
```

The hook converts each row into an `IPivotCalendarRemoteCell` (with `idField: 'user_id'` set):

```ts
{
  bucket: '2026-05-06',
  groupId: 'aeafdd7a-9217-4438-8e70-d3ac6d9b709a',
  values: [
    { measureId: 'logged',   value: 3 },   // 10800 / 3600
    { measureId: 'billable', value: 2.5 }  // 9000 / 3600
  ]
}
```

The corresponding `IPivotCalendarGroup` keeps the user's name as `label`, and the UUID as `id` so drilldown filters can target `record_owner = <uuid>` directly.

## Drilldown

When the user clicks a stat tile, `` emits an `IPivotCalendarCellClick` with the bucket, resolved date range, group, measure, and value. The hook lets you turn that click into a ready-to-run Docyrus query in two steps:

1. Pass an `onCellClick` handler to the hook (forwarded to ``).
2. Inside the handler, call `buildDrilldownQuery(info)` to obtain a `DocyrusPivotDrilldownQuery` containing the merged filters, suggested `columns`, and `orderBy` for the underlying records.

The hook never owns the dialog or the data table — it only emits the parameters. Render whatever you need (a generic dialog backed by `useDocyrusDataGrid`, a side panel, navigation to a saved view, etc.) on top of those params.

### Filter shape

`buildDrilldownQuery` always emits an `and` group with:

- `<dateField> between [bucketStartIso, bucketEndIso]`
- The grouping clause (only when `groupBy` is configured and the click had a `groupId`):
  - With `idField`: `<groupBy.field> = <uuid>` (resolved via the matrix's `groupId:<idField>` column)
  - Without `idField`: `rel_<groupBy.field>/<labelField> = <name>` (label-based fallback)
- Any caller-provided `filters` are nested first inside the merged group so they keep their semantics

### Wiring example

```tsx
import { useState } from 'react';

import { useDocyrusAuth } from '@docyrus/signin';

import { PivotCalendar } from '@docyrus/ui/components/pivot-calendar';
import {
  useDocyrusPivotCalendar,
  type DocyrusPivotDrilldownQuery
} from '@docyrus/ui/library/hooks/use-docyrus-pivot-calendar';

import { PivotDrilldownDialog } from '@/components/pivot-drilldown-dialog';

function TimeEntriesPage() {
  const { client } = useDocyrusAuth();
  const [drilldown, setDrilldown] = useState<DocyrusPivotDrilldownQuery | null>(null);

  const { pivotCalendarProps, buildDrilldownQuery } = useDocyrusPivotCalendar({
    client: client!,
    appSlug: 'base',
    dataSourceSlug: 'time_entry',
    dateField: 'date',
    measures: [/* ... */],
    groupBy: {
      field: 'record_owner',
      label: 'User',
      labelField: 'name',
      idField: 'user_id'
    },
    onCellClick: info => setDrilldown(buildDrilldownQuery(info))
  });

  return (
    <>
      
      {drilldown ? (
        <PivotDrilldownDialog
          open
          client={client!}
          drilldown={drilldown}
          onOpenChange={open => { if (!open) setDrilldown(null); }} />
      ) : null}
    </>
  );
}
```

### Sample drilldown payload

For a click on **2026-05-06 → Cameron Shaw → Logged**, `buildDrilldownQuery` returns:

```ts
{
  appSlug: 'base',
  dataSourceSlug: 'time_entry',
  dateField: 'date',
  groupField: 'record_owner',
  groupLabel: 'Cameron Shaw',
  bucketStartIso: '2026-05-06T00:00:00.000Z',
  bucketEndIso: '2026-05-06T23:59:59.999Z',
  measureField: 'duration',
  measureFunc: 'sum',
  measureValue: 3,
  measureFormatted: '3h',
  filters: {
    combinator: 'and',
    rules: [
      { field: 'date', operator: 'between',
        value: ['2026-05-06T00:00:00.000Z', '2026-05-06T23:59:59.999Z'] },
      { field: 'record_owner', operator: '=',
        value: 'aeafdd7a-9217-4438-8e70-d3ac6d9b709a' }
    ]
  },
  columns: 'id, date, duration, ...record_owner(name)',
  orderBy: 'date DESC'
}
```

The `filters`, `columns`, and `orderBy` go straight into `useDocyrusDataGrid({ listParams: { ... } })` — the dialog wrapper at `apps/playground/src/components/pivot-drilldown-dialog.tsx` is a thin reference implementation.

### `DocyrusPivotDrilldownQuery`

| Field | Type | Description |
|-------|------|-------------|
| `appSlug` | `string` | App slug, ready to pass to `useDocyrusDataGrid`. |
| `dataSourceSlug` | `string` | Data source slug. |
| `dateField` | `string` | Date field that produced the bucket. |
| `groupField` | `string \| undefined` | Group field slug, when `groupBy` is configured. |
| `groupLabel` | `string \| null` | Display label of the clicked group (e.g. user name). |
| `bucketStart` / `bucketEnd` | `Date` | Resolved date range of the bucket. |
| `bucketStartIso` / `bucketEndIso` | `string` | ISO strings of the same range. |
| `measureField` | `string` | Field slug of the clicked measure. |
| `measureFunc` | `TPivotCalendarAggregate` | Aggregation function. |
| `measure` | `IPivotCalendarMeasure<PivotRow>` | Measure descriptor (label, color, format). |
| `measureValue` | `number` | Raw aggregated value. |
| `measureFormatted` | `string` | Pre-formatted value (e.g. `1.5h`). |
| `filters` | `DocyrusPivotFilterGroup` | Merged AND filter group. |
| `columns` | `string` | Suggested `columns` selection. |
| `orderBy` | `string` | Suggested `orderBy` (`<dateField> DESC`). |

## Usage (minimal)

```tsx
import { useDocyrusAuth } from '@docyrus/signin';
import { PivotCalendar } from '@docyrus/ui/components/pivot-calendar';
import { useDocyrusPivotCalendar } from '@docyrus/ui/library/hooks/use-docyrus-pivot-calendar';

function Report() {
  const { client } = useDocyrusAuth();

  const { pivotCalendarProps } = useDocyrusPivotCalendar({
    client: client!,
    appSlug: 'base',
    dataSourceSlug: 'time_entry',
    dateField: 'date',
    measures: [
      {
        id: 'logged',
        label: 'Logged',
        field: 'duration',
        func: 'sum',
        transform: s => s / 3600,
        formatValue: h => `${h.toFixed(1)}h`
      }
    ],
    groupBy: { field: 'record_owner', label: 'User', labelField: 'name' }
  });

  return ;
}
```

## Options

| Option | Type | Default | Description |
|--------|------|---------|-------------|
| `client` | `RestApiClient` | — | Authenticated Docyrus API client. |
| `appSlug` | `string` | — | App slug. |
| `dataSourceSlug` | `string` | — | Data source slug. |
| `dateField` | `string` | — | Field used for the date range matrix. |
| `measures` | `Array<DocyrusPivotMeasure>` | — | Up to 2 measures with field, func, color, format. |
| `groupBy` | `DocyrusPivotGroupBy` | — | Optional grouping dimension (e.g. user/team). |
| `columns` | `string` | `''` | Extra `columns` segment for joined labels. |
| `filters` | `unknown` | — | Filters applied to the main query. |
| `defaultView` | `TPivotCalendarView` | `'month-calendar'` | Initial view. |
| `view` | `TPivotCalendarView` | — | Controlled view. |
| `onViewChange` | `(view) => void` | — | View change handler. |
| `defaultDate` | `Date` | now | Initial reference date. |
| `date` | `Date` | — | Controlled reference date. |
| `onDateChange` | `(date) => void` | — | Date change handler. |
| `hideViewSwitcher` | `boolean` | `false` | Forwarded to `<PivotCalendar>`. |
| `hideSidebar` | `boolean` | `false` | Forwarded to `<PivotCalendar>`. |
| `visibleViews` | `Array<TPivotCalendarView>` | all | Forwarded to `<PivotCalendar>`. |
| `enabled` | `boolean` | `true` | TanStack Query gate. |
| `staleTime` | `number` | `30_000` | Cache window in ms. |
| `showEmptyCells` | `boolean` | `true` | When `false`, sends `pivot.hideEmptyRows: true`. |
| `onCellClick` | `(info) => void` | — | Forwarded to `<PivotCalendar>`. Pair with `buildDrilldownQuery` to open a drilldown dialog. |

### `groupBy` (`DocyrusPivotGroupBy`)

| Field | Type | Default | Description |
|-------|------|---------|-------------|
| `field` | `string` | — | Field slug of the grouping relation/select. |
| `label` | `string` | — | Visible label for the sidebar / pivot row header. |
| `labelField` | `string` | `'name'` | Subfield used to label groups in the matrix CTE (`groupLabel:<labelField>`). |
| `idField` | `string` | — | Subfield exposing the relation's stable primary key in the matrix CTE (e.g. `'user_id'` for users). When set, drilldown filters become `<field> = <uuid>`; otherwise the hook falls back to `rel_<field>/<labelField>`. |
| `filters` | `unknown` | — | Filters applied to the group dimension's CTE. |
| `toGroup` | `(row) => Partial<IPivotCalendarGroup>` | — | Per-row override that returns avatar / colour metadata for the resolved group. |

## Returns

| Field | Type | Description |
|-------|------|-------------|
| `pivotCalendarProps` | `PivotCalendarProps<TData>` | Props ready to spread on `<PivotCalendar>` (already configured for `mode='remote'`). |
| `cells` | `Array<IPivotCalendarRemoteCell>` | Parsed remote cells. |
| `groups` | `Array<IPivotCalendarGroup>` | Inferred groups from the response. |
| `measures` | `Array<IPivotCalendarMeasure>` | Component-shaped measures. |
| `view` | `TPivotCalendarView` | Active view. |
| `setView` | `(view) => void` | Manual view setter. |
| `selectedDate` | `Date` | Active reference date. |
| `setSelectedDate` | `(date) => void` | Manual date setter. |
| `isLoading` | `boolean` | TanStack Query loading flag. |
| `error` | `Error \| null` | Last query error. |
| `refetch` | `() => void` | Force a refetch. |
| `buildDrilldownQuery` | `(info: IPivotCalendarCellClick) => DocyrusPivotDrilldownQuery` | Converts a click event into Docyrus query parameters (filters, columns, orderBy) ready for `useDocyrusDataGrid`. |

## Notes

- The hook caps the displayed measures at **2** (matching `<PivotCalendar>`).
- The `count` aggregate uses the `id` field — pass `field: 'id'` together with `func: 'count'`.
- For relations (e.g. `record_owner`, `user`), the default `labelField` is `name`. Override via `groupBy.labelField` when the related data source uses a different display field.
- For user relations like `record_owner`, set `groupBy.idField: 'user_id'` so the matrix surfaces the stable UUID and drilldown filters use `record_owner = <uuid>` instead of the `rel_record_owner/name` fallback. The relation's actual `id` column can never be aliased (it is the SQL join key).
- `transform` runs client-side on each value before formatting — useful for unit conversions like seconds→hours.
- The pivot calendar emits a click for every measure tile / pivot value; `buildDrilldownQuery` is the bridge to `useDocyrusDataGrid` — your dialog or panel stays decoupled from both the calendar and the hook.