useDocyrusPivotCalendar

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

Edit on GitHubEditllms.txt

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 <PivotCalendar mode="remote" /> 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

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:

{
  "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 <PivotCalendar /> 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.

'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 <TimeEntriesPageInner client={client} />;
}

function TimeEntriesPageInner({
  client
}: {
  client: NonNullable<ReturnType<typeof useDocyrusAuth>['client']>;
}) {
  const {
    pivotCalendarProps, isLoading, error, refetch
  } = useDocyrusPivotCalendar({
    client,
    appSlug: APP_SLUG,
    dataSourceSlug: DATA_SOURCE_SLUG,
    dateField: 'date',
    measures: [
      {
        id: 'logged',
        label: 'Logged',
        shortLabel: 'Logged',
        field: 'duration',
        func: 'sum',
        color: '#10b981',
        transform: secondsToHours,
        formatValue: formatHours,
        description: 'Total duration logged on time entries'
      },
      {
        id: 'billable',
        label: 'Billable',
        shortLabel: 'Bill.',
        field: 'duration_billable',
        func: 'sum',
        color: '#6366f1',
        transform: secondsToHours,
        formatValue: formatHours,
        description: 'Billable duration on time entries'
      }
    ],
    groupBy: {
      field: 'record_owner',
      label: 'User',
      labelField: 'name',
      idField: 'user_id'
    }
  });

  return (
    <div className="flex h-full w-full flex-col gap-4 overflow-hidden px-6 py-5">
      <div className="flex shrink-0 items-center justify-between">
        <div className="flex items-center gap-3">
          <div className="flex size-9 items-center justify-center rounded-lg bg-primary/10 text-primary">
            <Clock className="size-4" />
          </div>
          <div className="flex flex-col">
            <h1 className="text-lg font-semibold tracking-tight">Time Entries</h1>
            <p className="text-xs text-muted-foreground">
              {APP_SLUG}/{DATA_SOURCE_SLUG} · server-side pivot
            </p>
          </div>
        </div>
        <div className="flex items-center gap-2">
          {isLoading ? <Spinner className="size-4 text-muted-foreground" /> : null}
          <Button size="sm" variant="outline" className="gap-1.5" onClick={refetch}>
            <RefreshCw className="size-3.5" />
            Refresh
          </Button>
        </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">
        <PivotCalendar {...pivotCalendarProps} className="w-full" />
      </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:

{
  "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

{
  "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):

{
  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, <PivotCalendar /> 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:

Pass an onCellClick handler to the hook (forwarded to <PivotCalendar />).
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

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 (
    <>
      <PivotCalendar {...pivotCalendarProps} />
      {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:

{
  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)

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 <PivotCalendar {...pivotCalendarProps} />;
}

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.

Credits

docyrus/docyrus-ui

useDocyrusPivotCalendar

Credits

On this page