Build a dashboard pivot table

← Dashboards & Pages

A TABLE widget can lay out its query result as a cross-tab: one field’s values down the side, another field’s values across the top, and metric values in the cells. Give the widget pivot instead of columns. The table only moves the cells of the result it received into place; it never adds or averages them. Totals come from extra Semantic queries that you name, so the warehouse computes every total with the metric’s own definition.

Agents create and edit dashboards; you review the result in the app. With MCP, read this guide with monomer_docs at /en/dashboards/pivot-tables/index.md; the Dashboard definition schema’s TABLE pivot description points here. Follow Create a dashboard with an agent for the complete create workflow and Review and apply dashboard changes to change an existing dashboard.

Choose a pivot, a flat table, or a chart

To show	Use
One row per result row: lists, rankings, detail records, or a few metrics side by side	A flat TABLE with columns
Exact values where two sets of keys cross, such as region by month, when the column keys are few and known	A pivot TABLE with pivot
Each metric as a row with periods across, as in a monthly report	A pivot with "valuesAxis": "ROWS"
Patterns across many cells, where the shape matters more than exact values	A CHART with an ECharts heatmap; see Write a dashboard chart source

The dashboard definition fixes the pivot layout. Readers can sort rows and copy cells, but they can’t move fields between rows and columns; ask your agent to change the draft instead. A pivot can generate at most 200 value columns, so a column field with hundreds of values, such as days or customers, belongs in a flat table or a chart.

Describe the pivot

pivot has four required fields and an optional totals list:

Field	Contents
rows	0–8 row header fields, left to right. Each entry is a column like a flat table’s: field, an optional header, and an optional width from 80 to 640 pixels. Use [] when the table needs no row headers.
columns	1–3 fields whose values become column headers, outermost first. Each entry has field and an optional header; it has no width.
values	1–20 fields shown in the cells, written like rows entries. A width applies to every column generated for that value.
valuesAxis	"COLUMNS" repeats the values under each column key. "ROWS" gives each value its own row, named in a Values row header column.
totals	Optional. 1–3 names of Semantic queries that supply totals. Omit it for a pivot without totals; an empty list is rejected.

A field can appear in only one of the three lists, and every field must be a result key of the widget’s queryName query. Semantic result keys combine the kind, model, element, and any grain you selected, such as dimension.orders.region, metric_time.month, and metric.revenue. SQL results use their declared column names.

• Semantic query: the rows and columns fields together must be exactly the query’s group-by result keys: place every group-by, and no other field. Every values field must be one of the query’s metric result keys. This guarantees at most one result row for each row and column combination. Creating or editing the definition fails if these don’t match, and the error names the field and the query’s actual group-by or metric keys.
• SQL query: each field must be one of the query’s declared columns. If a result repeats a row and column key pair, the widget shows an error instead of choosing or combining rows. A pivot over a SQL query can’t have totals.

The widget keeps the rest of the TABLE contract: title, rowHeight, and an optional module that formats cells, as in Format dashboard table cells. In a pivot, the module formats value cells and row header cells with their original result row and field; it doesn’t format empty cells or totals, and its row function isn’t used. A value cell’s displayText uses the same digits as the totals beside it.

Example: revenue and orders by region and month

This example assumes revenue and order_count metrics that can be grouped by a region dimension on the orders model. Replace both illustrative UUIDs. After describing the advertised create entry with monomer_describe, call monomer_create with this request:

monomer_create input

{
  "workspaceId": "0198ef28-2000-7000-8000-000000000001",
  "path": "workspace/dashboards",
  "input": {
    "kind": "DASHBOARD_DEFINITION",
    "definition": {
      "schemaVersion": 1,
      "meta": {"title": "Regional revenue"},
      "parameters": {},
      "variables": {},
      "queries": {
        "revenue_by_region_month": {
          "type": "SEMANTIC",
          "connectorId": "0198ef28-2000-7000-8000-000000000002",
          "query": {
            "schemaVersion": 4,
            "metrics": ["revenue", "order_count"],
            "groupBys": [{"kind": "DIMENSION", "semanticModelName": "orders", "name": "region"}, {"kind": "METRIC_TIME", "granularity": "MONTH"}],
            "metricTimeRange": {"start": {"kind": "DATE", "value": "2026-01-01"}, "end": {"kind": "DATE", "value": "2026-06-30"}},
            "filters": [],
            "orderBy": [{"target": {"kind": "GROUP_BY", "groupBy": {"kind": "DIMENSION", "semanticModelName": "orders", "name": "region"}}, "direction": "ASC"}],
            "rowLimit": 1000
          }
        }
      },
      "widgets": {
        "region_month_pivot": {
          "type": "TABLE",
          "title": "Revenue and orders by region",
          "queryName": "revenue_by_region_month",
          "pivot": {
            "rows": [{"field": "dimension.orders.region", "header": "Region", "width": 160}],
            "columns": [{"field": "metric_time.month", "header": "Month"}],
            "values": [{"field": "metric.revenue", "header": "Revenue"}, {"field": "metric.order_count", "header": "Orders"}],
            "valuesAxis": "COLUMNS"
          }
        }
      },
      "layout": {"sections": [{"id": "main", "height": {"mode": "SCALED", "scale": 1.5}, "root": {"type": "WIDGET", "id": "pivot_node", "widgetId": "region_month_pivot"}}]}
    }
  }
}

Regions appear in the query’s orderBy order, region ascending. Each month becomes a column header with Revenue and Orders beneath it: six months with two values generate 12 value columns. A region with no orders in a month has empty cells under that month. Region and month together are the query’s group-bys, so every result row fills exactly one pair of cells.

Example: a metric scorecard

Set valuesAxis to "ROWS" to list each metric as a row with periods across, as in a monthly business review. With rows empty, the query groups only by month. These fragments are a query entry and a widget entry for the same definition:

{
  "type": "SEMANTIC",
  "connectorId": "0198ef28-2000-7000-8000-000000000002",
  "query": {
    "schemaVersion": 4,
    "metrics": ["revenue", "order_count"],
    "groupBys": [{"kind": "METRIC_TIME", "granularity": "MONTH"}],
    "metricTimeRange": {"start": {"kind": "DATE", "value": "2026-01-01"}, "end": {"kind": "DATE", "value": "2026-06-30"}},
    "filters": [],
    "orderBy": [],
    "rowLimit": 100
  }
}

{
  "type": "TABLE",
  "title": "Monthly scorecard",
  "queryName": "monthly_scorecard",
  "pivot": {
    "rows": [],
    "columns": [{"field": "metric_time.month", "header": "Month"}],
    "values": [{"field": "metric.revenue", "header": "Revenue"}, {"field": "metric.order_count", "header": "Orders"}],
    "valuesAxis": "ROWS"
  }
}

Declare the query as monthly_scorecard. The Values column names Revenue and Orders, one row each, and each month becomes one column, so this pivot generates six value columns. Adding a row field, such as region, repeats the value rows for each region. In this layout, readers sort by row field headers only.

Add totals

Why totals are queries

Adding the visible cells gives the wrong total for most metrics, so the table never does it:

Metric	Adding the cells gives
Distinct counts (COUNT_DISTINCT), such as unique customers	Customers who ordered in several months are counted once per month.
Ratios and conversion rates	An unweighted mix of rates, not total conversions divided by total visits.
Averages and medians	An average of averages, or a median of medians.
Cumulative metrics	Running totals added to each other.
Balances and MRR, from a measure with nonAdditiveDimension	A meaningless sum; the period value is usually the last month’s.

Each name in totals is an ordinary named Semantic query that asks for the same metrics at a coarser grouping. The warehouse computes the total with the metric’s definition, just like any other cell.

Write the totals queries

You don’t say where a total goes. Its query’s group-bys decide the placement:

Totals query group-bys	Placement
Exactly the rows fields	Total at the right, one column per value with "COLUMNS": each row key across every column key
Exactly the columns fields	A Total row at the bottom: each column key across every row key
None	The grand total, at the bottom right

When rows is empty, the total column and the grand total are the same placement, so use one query with no group-bys. Each totals query must:

• be a Semantic query, over a Semantic primary query;
• use the primary query’s connectorId, the same metricTimeRange, and the same filters, in any order, including the same parameter and variable references;
• select every metric in values;
• group by one of the three placements above, and not by the primary query’s own group-bys;
• be the only totals query for its placement;
• not be the primary query or a name in module.inputs.queries.

The query’s orderBy doesn’t matter. Its rowLimit does: Monomer applies each query’s rowLimit as a limit in the warehouse, so a totals query must return every row key or column key that the table displays. Creating or editing the definition fails if a totals query breaks a rule, and the error names the pivot.totals entry and what differs.

This monomer_create request extends the first example with a channel filter on the same orders model and all three totals. Every totals query repeats the primary query’s connector, time range, filter, and both metrics:

monomer_create input

{
  "workspaceId": "0198ef28-2000-7000-8000-000000000001",
  "path": "workspace/dashboards",
  "input": {
    "kind": "DASHBOARD_DEFINITION",
    "definition": {
      "schemaVersion": 1,
      "meta": {"title": "Online revenue by region"},
      "parameters": {},
      "variables": {},
      "queries": {
        "revenue_by_region_month": {
          "type": "SEMANTIC",
          "connectorId": "0198ef28-2000-7000-8000-000000000002",
          "query": {
            "schemaVersion": 4,
            "metrics": ["revenue", "order_count"],
            "groupBys": [{"kind": "DIMENSION", "semanticModelName": "orders", "name": "region"}, {"kind": "METRIC_TIME", "granularity": "MONTH"}],
            "metricTimeRange": {"start": {"kind": "DATE", "value": "2026-01-01"}, "end": {"kind": "DATE", "value": "2026-06-30"}},
            "filters": [{"kind": "TEXT", "dimension": {"semanticModelName": "orders", "name": "channel"}, "operator": "EQUALS", "value": "online"}],
            "orderBy": [{"target": {"kind": "GROUP_BY", "groupBy": {"kind": "DIMENSION", "semanticModelName": "orders", "name": "region"}}, "direction": "ASC"}],
            "rowLimit": 1000
          }
        },
        "revenue_by_region": {
          "type": "SEMANTIC",
          "connectorId": "0198ef28-2000-7000-8000-000000000002",
          "query": {
            "schemaVersion": 4,
            "metrics": ["revenue", "order_count"],
            "groupBys": [{"kind": "DIMENSION", "semanticModelName": "orders", "name": "region"}],
            "metricTimeRange": {"start": {"kind": "DATE", "value": "2026-01-01"}, "end": {"kind": "DATE", "value": "2026-06-30"}},
            "filters": [{"kind": "TEXT", "dimension": {"semanticModelName": "orders", "name": "channel"}, "operator": "EQUALS", "value": "online"}],
            "orderBy": [],
            "rowLimit": 1000
          }
        },
        "revenue_by_month": {
          "type": "SEMANTIC",
          "connectorId": "0198ef28-2000-7000-8000-000000000002",
          "query": {
            "schemaVersion": 4,
            "metrics": ["revenue", "order_count"],
            "groupBys": [{"kind": "METRIC_TIME", "granularity": "MONTH"}],
            "metricTimeRange": {"start": {"kind": "DATE", "value": "2026-01-01"}, "end": {"kind": "DATE", "value": "2026-06-30"}},
            "filters": [{"kind": "TEXT", "dimension": {"semanticModelName": "orders", "name": "channel"}, "operator": "EQUALS", "value": "online"}],
            "orderBy": [],
            "rowLimit": 100
          }
        },
        "revenue_total": {
          "type": "SEMANTIC",
          "connectorId": "0198ef28-2000-7000-8000-000000000002",
          "query": {
            "schemaVersion": 4,
            "metrics": ["revenue", "order_count"],
            "groupBys": [],
            "metricTimeRange": {"start": {"kind": "DATE", "value": "2026-01-01"}, "end": {"kind": "DATE", "value": "2026-06-30"}},
            "filters": [{"kind": "TEXT", "dimension": {"semanticModelName": "orders", "name": "channel"}, "operator": "EQUALS", "value": "online"}],
            "orderBy": [],
            "rowLimit": 1
          }
        }
      },
      "widgets": {
        "region_month_pivot": {
          "type": "TABLE",
          "title": "Online revenue and orders by region",
          "queryName": "revenue_by_region_month",
          "pivot": {
            "rows": [{"field": "dimension.orders.region", "header": "Region", "width": 160}],
            "columns": [{"field": "metric_time.month", "header": "Month"}],
            "values": [{"field": "metric.revenue", "header": "Revenue"}, {"field": "metric.order_count", "header": "Orders"}],
            "valuesAxis": "COLUMNS",
            "totals": ["revenue_by_region", "revenue_by_month", "revenue_total"]
          }
        }
      },
      "layout": {"sections": [{"id": "main", "height": {"mode": "SCALED", "scale": 1.5}, "root": {"type": "WIDGET", "id": "pivot_node", "widgetId": "region_month_pivot"}}]}
    }
  }
}

revenue_by_region fills the Total column, revenue_by_month the Total row, and revenue_total the grand total. Totals labels read Total in bold, and totals cells use the metric’s saved display format. You can include only the totals you need; a pivot with just revenue_by_month has a total row and nothing else.

Totals queries run, refresh, and retry like the dashboard’s other queries and appear in its query runs. The widget shows new results only after its primary query and all of its totals queries succeed; until then, it keeps its previous results. Each totals query is one more warehouse run.

To add totals to an existing dashboard, add the queries and the totals list in one draft edit. Adding or removing totals runs the widget’s queries again. Changing only the pivot layout, such as swapping rows and columns or switching valuesAxis, keeps the same queries and doesn’t start new runs.

Metrics that can’t total across months

Some metrics can’t be queried without metric_time: cumulative metrics with a window or grainToDate, and ratio or derived metrics with an input that uses offsetWindow or offsetToGrain, such as month-over-month growth. A totals query that drops metric_time fails for them, and the create or draft edit is rejected with the engine’s error.

When metric_time is a column field, that rules out the Total column and the grand total for those metrics. The Total row keeps metric_time in its group-bys, so it works. A running total or a growth rate has no meaningful value across several months. Because every totals query selects every values metric, put those metrics in their own pivot, with no totals or only a Total row.

Order, empty cells, and copying

• Column keys sort ascending by value, whatever the query’s orderBy: numbers as exact decimals, dates and timestamps chronologically, false before true, and text by Unicode code point, so uppercase letters come before lowercase. Null keys come last. With two or three column fields, keys compare field by field, outermost first.
• Row keys keep the order in which they first appear in the result, so the query’s orderBy decides them. Readers can still select a row field header, or a value or Total column header, to sort rows; with "valuesAxis": "ROWS", only row field headers sort. The Total row stays at the bottom.
• Empty and null are different: a row and column combination missing from the result is an empty cell, while a null value shows null, as in a flat table.
• Copying works as in a flat table: select a cell, choose Copy selected cell, then Copy displayed text or Copy raw value. An empty cell copies as an empty string.

Limits and errors

A pivot has at most 8 row fields, 3 column fields, 20 values, and 3 totals queries. The schema and the definition checks reject larger lists. The query’s rowLimit, up to 100,000 rows, still applies.

The widget shows an error, instead of a partial or guessed table, when its results can’t be placed exactly:

Problem	What to change
The pivot would generate more than 200 value columns. With "COLUMNS", that’s the number of column keys times the number of values; with "ROWS", it’s the number of column keys. The table is never truncated.	Narrow the time range or filters, use a coarser grain, show fewer values, or swap rows and columns.
A SQL result repeats a row and column key pair.	Aggregate in SQL so each pair appears once, or add the field that tells the rows apart to rows or columns.
A totals result is missing a row or column key that the table displays.	Raise that totals query’s rowLimit, and confirm its filters and time range match the primary query.
A totals result repeats a key.	Make its group-bys exactly the placement’s fields.

Check the pivot before you apply it

In the appWith an agent

1. Open the dashboard’s draft with Review draft, as in Review and apply dashboard changes.
2. Check the column headers: they should be the keys you expect, in ascending order, with each value under each key.
3. Compare a few cells and the totals with the analysis you verified, and look for empty cells where you expected values.
4. Select a value or Total column header to sort rows, and confirm that the Total row stays at the bottom.
5. Open Runs to confirm that the primary and totals queries succeeded, then choose Apply.

Ask for the pivot and for the checks before you review it. For example:

In the Analytics workspace, add a pivot table to Regional revenue that shows
revenue and orders by region down the side and month across the top for
January to June 2026, with a total column, a total row, and a grand total.
Check how many months the query returns first, and leave the draft for me to review.

The agent runs the primary query first, as in Query metrics, and counts the distinct column keys in the result before choosing a pivot. After saving the draft, it reads the draft’s readyResults and follows each QueryRun, including every totals query, to completed results. A saved definition doesn’t prove that the widget rendered: a column limit or a missing totals key appears only in the widget.

Agent checklist

1. Run the primary query and count the distinct column key combinations in its result. Multiply by the number of values for "COLUMNS". Choose a flat table or a chart instead if the count can approach 200 as data grows.
2. For a Semantic query, place every group-by result key in rows or columns, and only metric result keys in values.
3. Set the primary query’s orderBy to the row order you want.
4. Copy the primary query’s connectorId, metricTimeRange, and filters into each totals query unchanged, and select every values metric.
5. Give each totals query group-bys that exactly match rows, exactly match columns, or are empty, and a rowLimit at least as large as the number of keys it must cover.
6. Leave out the total column and the grand total along metric_time for cumulative metrics with a window or grain-to-date and for time-offset ratio or derived metrics.
7. After saving, follow every QueryRun to results, totals included, and inspect the widget before asking for Apply.