Report datasets and formatting

GridDataset is the wire shape consumed by Sapporta’s report renderer. The report read owns authorization and row scope. The mapper receives ordinary visible rows and turns them into stable nodes, raw values, presentation metadata, and footers.

Keep the mapper pure

Pass the date baseline explicitly along with the visible rows:

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[];
  asOf: Temporal.PlainDate;
};

export function projectProgressDataset({
  projects,
  tasks,
  asOf,
}: ProjectProgressInput): GridDataset {
  const tasksByProject = new Map<number, TaskRow[]>();
  for (const task of tasks) {
    const projectTasks = tasksByProject.get(task.project_id) ?? [];
    projectTasks.push(task);
    tasksByProject.set(task.project_id, projectTasks);
  }

  const rows = projects.map((project) => {
    const projectTasks = tasksByProject.get(project.id) ?? [];
    const completed = projectTasks.filter(
      (task) => task.status === "completed",
    ).length;
    const open = projectTasks.filter((task) => task.status === "open").length;
    const inProgress = projectTasks.filter(
      (task) => task.status === "in_progress",
    ).length;
    const overdue = projectTasks.filter(
      (task) =>
        task.status !== "completed" &&
        task.due_date !== null &&
        Temporal.PlainDate.compare(task.due_date, asOf) < 0,
    ).length;

    return {
      project,
      total: projectTasks.length,
      open,
      inProgress,
      completed,
      overdue,
      completion:
        projectTasks.length === 0 ? 0 : completed / projectTasks.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 as of ${asOf.toString()}`,
    rootLevel: "project",
    levels: {
      project: {
        label: "Projects",
        columns: [
          {
            id: "project_id",
            label: "Project ID",
            kind: "number",
            visuallyHidden: true,
          },
          { id: "project", label: "Project", kind: "text", minWidth: 40 },
          { 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,
        },
      },
    ],
  };
}

The overdue rule is readable from the function: a task is not completed, has a due date, and that date is before asOf. The mapper does not read the database, auth context, HTTP request, or Temporal.Now, so the same inputs always produce the same dataset.

A time zone is one of those inputs. A mapper that turns an instant into a day takes the zone as an argument, the same way it takes asOf; the route reads it from the request with workspaceTimeZone(auth) and passes it down. Reading an ambient zone inside the mapper would make the same rows produce two datasets on two machines.

The name remains project-progress; the renderer uses it with the level name for persisted column-sizing identity. The label can vary because it is display text. Render that label as the screen heading above ReportGridDataset.

Preserve values before presentation

Percentage values are ratios, so 0.4 renders as 40%. visuallyHidden keeps project_id in each node for link resolvers without displaying it. Hidden data is still present in the response and must already be authorized. Both rowKey and the hidden ID derive from stable domain identity, not the project label or the row’s array position.

displayFormat, textDisplay, zeroDisplay, colorRule, and the width hints describe presentation. They do not replace source numbers, booleans, dates, or timestamps with formatted strings. Widths are approximate displayed-character counts, not pixels; minWidth: 40 is a reasonable starting point for a project name.

colorRule: "negative" on the overdue column paints its figures with --sap-numeric-negative, which follows the error colour --sap-negative unless the application sets it. An overdue count is a problem, so the default fits. A ledger whose negative figures are ordinary money out sets --sap-numeric-negative to its ink colour instead; see Theme tokens and scales.

Wire dates use canonical strings. Decode them to Temporal values at the domain boundary, then use Temporal for comparison and arithmetic. A date column renders as 2026-08-23 and a timestamp column as 2026-08-23 16:38 in the active workspace’s zone, from the same canonical string the response carries.

A report whose numbers depend on a zone names it. ReportTimeZoneNote from @sapporta/frontend/report renders Asia/Kolkata UTC+05:30 in the toolbar, including when the zone is UTC.

A column that shows the name of a table row, such as each line’s account, can declare which row it names with reference: { table, idColumn }. The grid then draws the cell with the table’s label slot; see Name table rows in report cells.

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.

Show rows that name a parent as one tree

Some rows name a parent row of the same kind. For example, each account in an expense report can name its parent account in parent_id. Child levels would show each depth as a nested grid with its own header. Declare tree on the level instead, so all the accounts form one tree under one header:

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

type ExpenseAccount = {
  id: number;
  parent_id: number | null;
  name: string;
  // The account's own expenses plus those of its sub-accounts.
  amount: number;
};

export function expenseBreakdownDataset(
  accounts: readonly ExpenseAccount[],
  totalExpenses: number,
): GridDataset {
  return {
    name: "expense-breakdown",
    label: "Expense breakdown",
    rootLevel: "account",
    levels: {
      account: {
        columns: [
          {
            id: "parent_id",
            label: "Parent ID",
            kind: "number",
            visuallyHidden: true,
          },
          { id: "account", label: "Account", kind: "text" },
          {
            id: "amount",
            label: "Amount",
            kind: "number",
            displayFormat: "currency",
          },
        ],
        childLevels: [],
        tree: { parentColumn: "parent_id" },
      },
    },
    nodes: accounts.map((account) => ({
      rowKey: String(account.id),
      levelName: "account",
      columns: {
        parent_id: account.parent_id,
        account: account.name,
        amount: account.amount,
      },
    })),
    footerRows: [
      {
        rowKey: "total-expenses",
        columns: { account: "Total expenses", amount: totalExpenses },
      },
    ],
  };
}

The nodes are one flat list. parent_id holds the parent row’s rowKey, and the two are compared as strings. For this reason, the row key is String(account.id); a prefixed key such as account:12 would not match the parent value 12. A row whose parent is not in the list is shown at top level.

The tree is shown in the first visible text column, account. Set tree.column to show it in another visible column. Rows start expanded, and defaultCollapsed: true on the level starts them collapsed. Sorting a column orders the rows among their siblings at every depth, and the footer row stays below the tree.

The renderer does not add up the amounts, so the mapper passes each account’s amount with its sub-accounts included, and the footer’s total.

Define the fixture inside the test

A standalone mapper test owns every row and its fixed baseline:

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

it("maps visible project progress rows deterministically", () => {
  const projects = [
    { id: 1, name: "Website relaunch" },
    { id: 2, name: "Mobile refresh" },
  ];
  const tasks = [
    {
      id: 1,
      project_id: 1,
      status: "completed" as const,
      due_date: Temporal.PlainDate.from("2026-07-01"),
    },
    {
      id: 2,
      project_id: 1,
      status: "open" as const,
      due_date: Temporal.PlainDate.from("2026-07-09"),
    },
    {
      id: 3,
      project_id: 1,
      status: "in_progress" as const,
      due_date: Temporal.PlainDate.from("2026-07-20"),
    },
    {
      id: 4,
      project_id: 2,
      status: "completed" as const,
      due_date: null,
    },
    {
      id: 5,
      project_id: 2,
      status: "open" as const,
      due_date: Temporal.PlainDate.from("2026-07-30"),
    },
  ];

  const result = gridDatasetSchema.parse(
    projectProgressDataset({
      projects,
      tasks,
      asOf: Temporal.PlainDate.from("2026-07-10"),
    }),
  );

  expect(result.name).toBe("project-progress");
  expect(result.label).toBe("Project progress as of 2026-07-10");
  expect(result.nodes).toHaveLength(2);
  expect(result.nodes[0]).toMatchObject({
    rowKey: "project:1",
    columns: { project_id: 1, overdue: 1 },
  });
  expect(result.footerRows?.[0]).toMatchObject({
    rowKey: "grand-total",
    columns: {
      total: 5,
      completed: 2,
      overdue: 1,
      completion: 0.4,
    },
  });
});

The schema parse validates the complete wire result, including the footer. The focused assertions prove the math, hidden ID, stable row key, explicit date, and ratio semantics. Add hierarchy-specific tests when levels have children; schema validity alone does not prove that a hierarchy is coherent.

Related documentation

• GridDataset
• Name table rows in report cells
• Column sizing
• Route-based reports
• Group and filter by day