Report datasets and formatting

GridDataset is the stable wire shape consumed by Sapporta’s report renderer. This page maps the task app’s already-scoped rows into a flat project-progress dataset. You will learn how levels, columns, nodes, hidden values, formatting, and footers work, and how to test the mapper without a database. The same primitives can describe flat summaries, nested ledgers, subtotals, and expandable hierarchies.

Create a pure GridDataset mapper for this report. Use stable row and column identities, keep link IDs visually hidden, calculate rollups and footers deterministically, use Temporal for date comparisons, and parse the result with gridDatasetSchema in a focused test.

Map scoped domain rows

The report read owns authentication and database scope. The mapper receives ordinary visible rows plus an explicit date baseline:

import { Temporal } from "@sapporta/shared/temporal";
import type { GridDataset } from "@sapporta/shared/grid-dataset";

type ProjectRow = { id: number; name: string };
type TaskRow = {
  id: number;
  project_id: number;
  status: "open" | "in_progress" | "completed";
  due_date: Temporal.PlainDate | null;
};

type ProjectProgressInput = {
  projects: readonly ProjectRow[];
  tasks: readonly TaskRow[];
  today: Temporal.PlainDate;
};

export function projectProgressDataset(
  input: ProjectProgressInput,
): GridDataset {
  const rows = input.projects.map((project) => {
    const tasks = input.tasks.filter((task) => task.project_id === project.id);
    const completed = tasks.filter(
      (task) => task.status === "completed",
    ).length;
    const open = tasks.filter((task) => task.status === "open").length;
    const inProgress = tasks.filter(
      (task) => task.status === "in_progress",
    ).length;
    const overdue = tasks.filter(
      (task) =>
        task.status !== "completed" &&
        task.due_date !== null &&
        Temporal.PlainDate.compare(task.due_date, input.today) < 0,
    ).length;

    return {
      project,
      total: tasks.length,
      open,
      inProgress,
      completed,
      overdue,
      completion: tasks.length === 0 ? 0 : completed / tasks.length,
    };
  });

  const totals = rows.reduce(
    (sum, row) => ({
      total: sum.total + row.total,
      open: sum.open + row.open,
      inProgress: sum.inProgress + row.inProgress,
      completed: sum.completed + row.completed,
      overdue: sum.overdue + row.overdue,
    }),
    { total: 0, open: 0, inProgress: 0, completed: 0, overdue: 0 },
  );

  return {
    name: "project-progress",
    label: "Project progress",
    rootLevel: "project",
    levels: {
      project: {
        label: "Projects",
        columns: [
          {
            id: "project_id",
            label: "Project ID",
            kind: "number",
            visuallyHidden: true,
          },
          { id: "project", label: "Project", kind: "text", minWidth: 220 },
          { id: "total", label: "Tasks", kind: "number" },
          { id: "open", label: "Open", kind: "number", zeroDisplay: "dot" },
          {
            id: "in_progress",
            label: "In progress",
            kind: "number",
            zeroDisplay: "dot",
          },
          {
            id: "completed",
            label: "Completed",
            kind: "number",
            zeroDisplay: "dot",
          },
          {
            id: "overdue",
            label: "Overdue",
            kind: "number",
            colorRule: "negative",
            zeroDisplay: "dot",
          },
          {
            id: "completion",
            label: "Completion",
            kind: "number",
            displayFormat: "percentage",
          },
        ],
        childLevels: [],
      },
    },
    nodes: rows.map((row) => ({
      rowKey: `project:${row.project.id}`,
      levelName: "project",
      columns: {
        project_id: row.project.id,
        project: row.project.name,
        total: row.total,
        open: row.open,
        in_progress: row.inProgress,
        completed: row.completed,
        overdue: row.overdue,
        completion: row.completion,
      },
    })),
    footerRows: [
      {
        rowKey: "grand-total",
        columns: {
          project: "Grand total",
          total: totals.total,
          open: totals.open,
          in_progress: totals.inProgress,
          completed: totals.completed,
          overdue: totals.overdue,
          completion: totals.total === 0 ? 0 : totals.completed / totals.total,
        },
      },
    ],
  };
}

Percentage values are ratios, so 0.4 renders as 40%. visuallyHidden keeps project_id in the node for link resolvers without displaying it. zeroDisplay, colorRule, width hints, and text display settings describe presentation without converting source numbers into formatted strings.

For hierarchical reports, add named child levels, put source values in each node’s columns, put computed parent values in rollup, and attach child arrays through children. Root totals belong in footerRows; totals inside a child collection belong in childFooterRows.

Test the contract, not the pixels

import { gridDatasetSchema } from "@sapporta/shared/grid-dataset";
import { Temporal } from "@sapporta/shared/temporal";

it("maps the canonical project progress totals", () => {
  const result = projectProgressDataset({
    projects: canonicalProjects,
    tasks: canonicalTasks,
    today: Temporal.PlainDate.from("2026-07-10"),
  });

  expect(() => gridDatasetSchema.parse(result)).not.toThrow();
  expect(result.nodes).toHaveLength(2);
  expect(result.footerRows?.[0]?.columns).toMatchObject({
    total: 5,
    completed: 2,
    overdue: 1,
    completion: 0.4,
  });
});

Run the focused mapper test and then inspect the route output:

pnpm --filter ./packages/api exec vitest run project-progress
pnpm exec sapporta api get /api/reports/project-progress

The mapper now produces a deterministic renderer contract from visible domain rows. It can be tested independently of Hono, Drizzle, and React. Keep navigation out of this object, keep date arithmetic in Temporal, and add hierarchy only when expanding a row reveals a meaningful lower level.

Related reference

• GridDataset
• Report links