# Charts and dashboards

# Charts and dashboards

Chart your test data and share it. A **chart** is a saved query plus how to draw
its rows. A **dashboard** is a grid of charts. Both rerun their query each time
you open them, so "the last 30 days" is always current.

Build them by hand, or ask the assistant: "a dashboard of this week's acceptance
runs: yield by part number, failures by measurement, and the runs that failed".
It writes the queries, checks each chart, and gives you the links.

## Where the rows come from

Two sources are always there: what your devices **measured**, and how your **runs** went.

| Source | What it holds | Ask it |
|---|---|---|
| **Reservoir** | everything your devices recorded: channels, video, and one row per judged measurement of every run (`trellis_measurements`) | a channel's trend, a measurement's values against its limit, which measurement fails most |
| **Runs** | one row per run: recipe and version, unit, outcome, verdict counts, and a column for every [run field](https://phloem.ai/docs/trellis/recipes/) the run started with (`field_item`, `field_batch`, …) | yield by item, failures per batch, runs per person |

Run fields are only in **Runs**. A measurement row knows its recipe, unit and
outcome, but not the item or batch someone entered. To break down by a run
field, query **Runs**.

Any database or sheet your org connected on **Connectors** is also a source.

## Build a chart

1. Open **Dashboards**, switch to the **Charts** tab, and choose **New chart**.
2. Pick the source (**Reservoir**, **Runs** or a connector) and write the
   query. Press **Run** and check the rows.
3. Choose how to draw it and which columns go where. The preview updates live.
4. **Save**.

To combine two sources, add a second **Query** and a **Join**. Within one
source, join in SQL.

### Choose how to draw it

| You want to show | Draw it as |
|---|---|
| something over time | a line, with a column of times on the x axis |
| counts per category (per part, per day) | bars. Stack them when the parts make a whole (passed and failed per day) |
| two quantities in different units | small multiples: one panel each, one shared time axis |
| one number (this week's yield) | a stat, with the change from last week beside it |
| the rows themselves | a table |
| a limit | a band behind the trace, or a line at the value |

Each chart has one y axis. Put quantities with different units in separate
panels, never on a second axis.

## Queries for test data

Each of these was tested on real data. Swap in your recipe and measurement
names. The query editor suggests tables and columns as you type, and
**Reservoir → Explore** lists Reservoir's.

**Runs**: yield by part number, counting each unit once. A unit retested until
it passed has a row per try; `latest` keeps only the newest:

```sql
SELECT field_part AS part,
       count(*) FILTER (WHERE outcome = 'passed') AS passed,
       count(*) FILTER (WHERE outcome = 'failed') AS failed,
       count(*) FILTER (WHERE outcome NOT IN ('passed', 'failed')) AS not_judged
FROM runs
WHERE latest
GROUP BY 1
ORDER BY failed DESC
```

**Runs**: runs per day by outcome (stacked bars, day on the x axis):

```sql
SELECT start_ms // 86400000 * 86400000 AS day_ms,
       count(*) FILTER (WHERE outcome = 'passed') AS passed,
       count(*) FILTER (WHERE outcome = 'failed') AS failed
FROM runs
GROUP BY 1
ORDER BY 1
```

**Reservoir**: one measurement across every run, with its limits:

```sql
SELECT started_ms AS t_ms, uut, f64 AS value, bound_lo, bound_hi
FROM trellis_measurements
WHERE recipe = 'unit_accept' AND name = 'output_v' AND f64 IS NOT NULL
ORDER BY started_ms
```

**Reservoir**: which measurements fail most:

```sql
SELECT name,
       count(*) FILTER (WHERE verdict = 'fail') AS failed,
       count(*) FILTER (WHERE verdict IN ('unavailable', 'error')) AS not_measured
FROM trellis_measurements
WHERE recipe = 'unit_accept'
GROUP BY name
ORDER BY failed DESC
```

> **Note:** The two sources' SQL differs slightly. In **Runs**, `/` divides
> exactly, so a day bucket uses `//`. In **Reservoir**, `/` between whole numbers
> already drops the remainder, so the same bucket is
> `started_ms / 86400000 * 86400000`.

**Yield is passed ÷ (passed + failed).** A run that couldn't measure, was
aborted, or passed with configuration pending is neither. Show it as its own
series.

## Build a dashboard

1. Open **Dashboards** and choose **New dashboard**. Name it; it opens
   ready to edit.
2. **Add Chart** for each saved chart, then drag and resize the tiles.
3. Add **Dashboard filters**. A filter on a column narrows every chart that has
   that column. Use the same column name in every query (`recipe`,
   `field_part`, `device`) and one filter drives them all.
4. **Save**.