Comparison

Comparison widgets set values side by side: one bar or row per category. They never sort or cut rows themselves: the order and the LIMIT belong in the query.

Bar chart

Vertical bars: one group per category, one bar per series.

Open and overdue orders per branch, two series as columns (wide format):

SELECT b.name AS branch,
       count(wo.id) FILTER (WHERE wo.status NOT IN ('COMPLETED', 'CANCELLED')) AS open,
       count(wo.id) FILTER (WHERE wo.status NOT IN ('COMPLETED', 'CANCELLED')
                            AND wo.due_at < now()) AS overdue
FROM branches b
LEFT JOIN work_orders wo ON wo.branch = b.id
GROUP BY b.name
ORDER BY b.name

The same data can come as rows (long format): SELECT b.name AS branch, wo.type, count(*) AS orders ... GROUP BY 1, 2 with Series set to type draws a series per order type. A value missing for a category is left empty. Series names and colors come from the column's presentation, such as the labels and colors of type's CHECK list.

BAR

Vertical bars - one group per category, one bar per series.

Size: 6 × 4 of the 12-column grid, at least 3 × 3 · Also: series, thresholds, events, zoom, export

SELECT * FROM (VALUES ('North', 120, 80), ('South', 95, 110), ('East', 140, 60), ('West', 70, 90)) AS t(region, sales, returns)

Horizontal bar

The same roles and settings, with bars across: for long labels. Categories read top to bottom in the query's order. Parts used on work orders by category:

SELECT p.category, sum(-m.quantity) AS used
FROM stock_movements m
JOIN parts p ON p.id = m.part
WHERE m.reason = 'WORK_ORDER'
  AND ($VAR.period.from IS NULL OR m.created_at >= $VAR.period.from)
  AND ($VAR.period.to IS NULL OR m.created_at < $VAR.period.to)
GROUP BY p.category
ORDER BY used DESC

BAR_HORIZONTAL

Horizontal bars - for long labels; order the query yourself.

Size: 6 × 4 of the 12-column grid, at least 3 × 3 · Also: series, thresholds, events, zoom, export

SELECT * FROM (VALUES ('Laptops', 320), ('Phones', 280), ('Tablets', 150), ('Monitors', 90)) AS t(product, stock) ORDER BY stock DESC

Combo chart

Bars and lines on two axes: the first value column as bars on the left axis, the rest as lines on the right, each axis in its own format. It needs at least two value columns and no Series column; for a series per value of a column, use a Bar chart. Series changes which is which.

Orders per month as bars, hours per order as a line:

SELECT date_trunc('month', wo.reported_at)::date AS month,
       count(DISTINCT wo.id) AS orders,
       round(sum(te.minutes) / 60.0 / nullif(count(DISTINCT wo.id), 0), 1) AS hours_per_order
FROM work_orders wo
LEFT JOIN time_entries te ON te.work_order = wo.id
WHERE wo.reported_at >= date_trunc('month', current_date) - interval '11 months'
GROUP BY 1
ORDER BY 1

A Tooltip role adds more columns to each mark's tooltip.

COMBO

Bars and lines on two axes - the first value column as bars on the left, the rest as lines on the right.

Size: 6 × 4 of the 12-column grid, at least 3 × 3 · Also: series, thresholds, events, zoom, export

SELECT * FROM (VALUES ('Jan', 120, 3500.5), ('Feb', 140, 3720), ('Mar', 95, 4010.25)) AS t(month, visits, avg_ticket)

Waterfall

How a total is built: each row a change up or down, with its sign.

The value of Acme's stock movements this month, by reason:

SELECT m.reason, sum(m.quantity * p.unit_cost) AS value
FROM stock_movements m
JOIN parts p ON p.id = m.part
WHERE m.created_at >= date_trunc('month', now())
GROUP BY m.reason
ORDER BY CASE m.reason WHEN 'PURCHASE' THEN 1 WHEN 'TRANSFER' THEN 2
                       WHEN 'ADJUSTMENT' THEN 3 ELSE 4 END

WATERFALL

How a total is built - each row a change up or down; a total row the running sum so far.

Size: 6 × 4 of the 12-column grid, at least 3 × 3 · Also: thresholds, events, export

SELECT * FROM (VALUES (1, 'Sales', 1000, false), (2, 'Discounts', -150, false), (3, 'Net', NULL, true), (4, 'Paid', -600, false)) AS t(n, step, amount, is_total) ORDER BY n

Leaderboard

A ranked list with a bar per row, sized against the largest value. Rows show in the query's order: order and LIMIT it yourself.

The top ten customers by billing in the chosen period:

SELECT c.name AS customer, sum(ii.quantity * ii.unit_price) AS billed
FROM invoices i
JOIN customers c ON c.id = i.customer
JOIN invoice_items ii ON ii.invoice = i.id
WHERE i.status <> 'DRAFT'
  AND ($VAR.period.from IS NULL OR i.issued_on >= $VAR.period.from)
  AND ($VAR.period.to IS NULL OR i.issued_on < $VAR.period.to)
GROUP BY c.name
ORDER BY billed DESC
LIMIT 10

Its settings: Format, Compact number, Color, Color column and On click (a click on a row).

LEADERBOARD

A ranked list with bars - order and LIMIT the query yourself.

Size: 4 × 4 of the 12-column grid, at least 3 × 3 · Also: events, export

SELECT * FROM (VALUES ('Ana', 48), ('Luis', 41), ('Marta', 35), ('Pedro', 22)) AS t(agent, resolved) ORDER BY resolved DESC LIMIT 10

Next steps

• Trend: lines and areas over time.
• SQL Conventions: _color and the other helper columns.