How to write a report

A report is a query on one team's records: which fields to show, which rows to keep, and how to sort them. Team admins write one on Reports, with New report. An agent can read this page as text at /agents/reports.md.

The language is Google Sheets' QUERY language, plus from, a dot to a parent's fields, date ranges, and values picked when the report runs. Keywords, functions, and date ranges ignore case. Text goes in single quotes.

select member, sum(hours)
from attendance
where status = 'Attended' and year(activity.started_at) = :year
  and member.counted = true
group by member
pivot activity.kind
order by member.name
totals
filter year 'Year' required
  each year(activity.started_at)

This is Hours by member: each member's attended hours in the picked year, a column for each kind of activity, and a total row.

The query

The query's clauses come in this order, each at most once. Only select and from are needed.

select

select field, …

The columns, in order. A field, a function of one, or a count.

select title, started_at, attended_count
from activities

from

from subject

The subject the rows come from. One per report.

select name, joined_at
from members

where

where condition

Keeps the rows the condition is true for.

select title, started_at
from activities
where kind = 'Incident' and started_at = THIS_YEAR

group by

group by field, …

One row for each value. Every other column is a count, sum, average, min, or max.

select activity.kind, count(id)
from attendance
where status = 'Attended'
group by activity.kind

pivot

pivot field

A column for each value of the field, beside the group's own columns.

select member, sum(hours)
from attendance
where status = 'Attended' and activity.started_at = THIS_YEAR
group by member
pivot activity.kind

order by

order by field [asc | desc] [nulls first | nulls last], …

Sorts the rows. Going up unless it says desc.

select name, last_attended
from members
where current = true
order by last_attended desc nulls last

limit

limit n

Shows only the first n rows.

select title, attended_count
from activities
order by attended_count desc
limit 10

offset

offset n

Skips the first n rows. Use it with limit.

select title, attended_count
from activities
order by attended_count desc
limit 10
offset 10

label

label field 'Label', …

A column's heading, in place of the field's own.

select title, attended_count
from activities
label attended_count 'Members'

format

format field as format, …

How a column looks, in place of its field's own. Formats lists them.

select title, started_at, attended_hours
from activities
format title as text, started_at as month, attended_hours as hours

totals

totals

A total row under a report that groups.

select activity.kind, sum(hours)
from attendance
where status = 'Attended'
group by activity.kind
totals

The page

Clauses after the query draw the page around the rows: the search box, tiles, menus, sortable headings, and pages. They come in this order: search, then each switch and filter with its options, then each sort, then page.

search field, …

The search box. It looks for the words in each field named.

select title, started_at
from activities
search title, ref_id, tags

switch

switch name 'Label' [required] [default value]

The tiles above the rows, All first, each with its count. One per report, with an option for each tile.

select title, started_at
from activities
switch activity 'Kind'
  option incident 'Incidents' where kind = 'incident'
  option exercise 'Exercises' where kind = 'exercise'

filter

filter name 'Label' [required] [default value] [more]

A menu, All first. required takes out All, default picks where it starts, and more puts it behind More filters.

select title, started_at
from activities
filter status 'Status'
  option draft 'Draft' where published = false
  option published 'Published' where published = true

option

option value 'Label' [where condition] [sort name [desc]]

One tile or one menu choice, under a switch or filter: the value in the address, its label, and what it keeps.

select title, started_at
from activities
order by started_at desc
filter when 'When'
  option future 'Future' where started_at >= NOW sort date
  option past 'Past' where started_at <= NOW
sort date 'Date' started_at

each

each field [where condition]

A menu choice for each value of the field, under a filter: each year, each of a choice's values, or each title.

select title, started_at
from activities
where year(started_at) = :year
filter year 'Year' required default THIS_YEAR
  each year(started_at)

sort

sort name 'Label' field [desc], …

A column heading that sorts. Its label, or its first field, names the column.

select title, started_at, attended_count
from activities
sort date 'Date' started_at desc
sort attended 'Attended' attended_count desc

page

page n

Shows n rows at a time, from 1 to 500, with buttons for the rest.

select title, started_at
from activities
order by started_at desc
page 50

chart

chart stat | columns | lines | bars | calendar | week | map [compare year] [cumulative]

Draws the rows above the table, the last clause. The first column is the axis: year and month, date, day of the week, or hour fill in every bucket the where allows. compare year draws last year's beside this year's; cumulative adds up as it goes.

select year(started_at), month(started_at), count(id)
from activities
where months_since(started_at) < 12
group by year(started_at), month(started_at)
pivot kind
order by year(started_at), month(started_at)
chart columns

Charts

What each chart takes. The chart line is the last; the rows decide the rest.

Chart Takes
chart stat A number, and a line of it when the first column is a month.
chart columns A time or a name first, then counts. A pivot stacks them.
chart lines A time first, then counts. compare year adds last year.
chart bars A name first, then a count, longest first.
chart calendar date(started_at), then a count.
chart week day_of_week(started_at), hour(started_at), then a count.
chart map An activity and its place: a dot each, near the team's base.

Conditions

A where, and an option's where, keep the rows a condition is true for.

Write Is true when
=, !=, <, >, <=, >= The two sides compare that way. <> is !=.
contains 'river' The text has the words, in any case.
starts with 'Mt', ends with 'Hut' The text starts or ends with them.
like '%river%' The text matches, with % for any text and _ for one letter.
in ('incident', 'exercise') The value is one of the list's. not in is none of them.
includes ('Rope Rescue') A list field has any of the titles. excludes has none.
is null, is not null The field is blank, or is not.
and, or, not, ( ) Conditions together. and goes before or.

A value is a number, true, false, text in single quotes, a date such as date '2026-01-01', a date range, or a picked value such as :year. A choice compares with its label or its value: status = 'Attended' and status = 'attended' keep the same rows. A date compared with a date or a range takes in whole days in the team's time zone: started_at = LAST_YEAR.

Functions

Write Gives
count(field), count(distinct field) How many rows, or how many values.
sum(field) The numbers or hours added up. sum(joined_at = THIS_YEAR) counts the rows a condition holds for.
avg(field) The average.
min(field) The smallest, or the earliest date.
max(field) The largest, or the latest date.
year(date) The year, in the team's time zone: 2026.
month(date) The month, in the team's time zone.
quarter(date) The quarter: 1 to 4.
lower(text) The text in small letters.
upper(text) The text in capitals.
days_since(date) Whole days from the date to today.
date(date) The day, in the team's time zone, for a chart by day.
months_since(date) Whole months from the date's month to this one: 0 is this month.
day_of_week(date) The day of the week: 1 is Monday, 7 is Sunday.
hour(date) The hour, 0 to 23, in the team's time zone.
number(text) The number the text holds, or blank when it holds none.
digits(text) Only the text's digits: PROS#2025-123456 is 2025123456.

Formats

Each field draws its own cell: a date as a date, an activity's kind as its coloured square, a member as a link to their page. format picks another look for a column. The first for each type is the field's own. Other types show one way. Download CSV keeps the values as they are.

For Format Shows
date date Sep 28, 2026
date date_long September 28, 2026
date date_time Sep 28, 2026, 19:00
date month Sep 2026
date time 19:00
date days_ago 12 days ago
hours duration 212h 30m
hours hours 212.5
a record's link link The record's name, a link to its page.
a record's link text The name alone.
an activity's kind marker The kind's coloured square and its name.
an activity's kind text The name alone.

Dates

Times are stored in UTC and read in the team's time zone. Compare a date field with a range, as in started_at = LAST_N_DAYS:30, or with < and >.

Range Takes in
NOW This moment.
TODAY Today, in the team's time zone.
THIS_MONTH This month.
LAST_MONTH The whole month before this one.
THIS_YEAR This year, January 1 to December 31.
LAST_YEAR Last year.
LAST_N_DAYS:n Today and the n days before it, as whole days.
NEXT_N_DAYS:n Today and the n days after it.
LAST_N_MONTHS:n The n months before today, and today.

"The last n days" means one thing everywhere in SAR Duty: today and the n days before it, as whole days. Activities' Last 30 days and Members' Period count the same way.

Values picked when it runs

A name after a colon, such as :year, is a value the team admin picks when the report runs. A filter with the same name is its menu, and must be required when the query reads the value: where year(started_at) = :year with filter year 'Year' required.

Some fields take arguments in brackets, such as activity_count('2026', 'all'). An argument can be a picked value, as in activity_count(:when, :tag), and a filter with that name and no options lists its menu by itself.

Argument Is
tag A tag's title, or 'all'.
group A group's title, or 'all'.
period 'all', a year such as '2026', or a number of days such as '90d': the last 90 days.
days A whole number of days: not_seen(365).

Subjects and fields

Each subject's rows are one team's. A field that names a parent record reaches its fields with one dot: member.name, activity.started_at.

activities

Each row is an activity: an incident, exercise, or event.

Field Type Holds
title activity, a link Activity.
ref_id text The activity's ID.
kind choice Kind. One of Incident, Exercise, Event.
started_at date Started.
finished_at date Finished.
finished true or false Whether it has finished: its finish is past. A form can wait for it.
published true or false Published.
tags list Tags. The team's own titles. Compare with includes or excludes.
hours_tag_count number How many of the hours tags it has: 0, 1, or more.
attendance_sent true or false Whether a team admin sent its attendance from SAR Duty.
attended_count number How many members attended.
signed_up_count number How many members replied I'll be there, as its Enough at and Full at badges count.
enough_at number How many people it needs. Empty when not set.
full_at number How many people fill it. Empty when not set.
attended_hours hours Hours.
address text Where it is.
place place: latitude and longitude Its latitude and longitude. Compare it only with is null or is not null.
tracking_number text Tracking number.
description text Description.
duration hours How long it ran: a chore activity's, its members' chores.
chore true or false One of the team's chore activities.
recent true or false Started in the last 30 days and finished, as Activities' Last 30 days.
missing_required_fields true or false A required field of its kind is empty.

attendance

Each row is one member on one activity: their reply, or their attendance.

Field Type Holds
member member, a link Member. Reach its fields in members with a dot: member.name.
activity activity, a link Activity. Reach its fields in activities with a dot: activity.title.
status choice Attended, Signed up, Absent, Can't make it, or No reply.
started_at date Started.
finished_at date Finished.
hours hours Hours.
km km The round trip Travel counts for the row. Empty when not set.
replied_at date Replied.

members

Each row is a member, retired members too.

Field Type Holds
name member, a link Member.
ref_id text The member's ID on the team.
position text Position.
joined_at date Joined.
standing choice Active, Away, Retiring, Not a person, or Retired. Only team admins see Away.
counted true or false Whether SAR Duty counts them: not retiring, and a person.
person true or false Whether they are a person, not a pager or a shared login.
groups list Groups. The team's own titles. Compare with includes or excludes.
qualifications list Qualifications. The team's own titles. Compare with includes or excludes.
last_attended date Last attended.
left_at date Left.
current true or false On the team now: every member but the retired.
not_seen(days) true or false not_seen(365): current members whose attendance clock is older than 365 days, as Needs attention counts. Only team admins can read it.
on_team(period) true or false on_team('2026'): on the team at any point in the period, as Members' Period.
in_group(group) true or false in_group('Callout'): in the group, as Members' Group filter.
activity_count(period, tag) number activity_count('2026', 'all'): activities attended in the period, with the tag.
attended_minutes(period, tag) hours attended_minutes('2026', 'all'): hours attended in the period, with the tag.
email text The email they log in with. Only team admins can read it.
mobile text Mobile number. Only team admins can read it.
address text Their mailing address. Only team admins can read it.
details_missing true or false A current member with no phone, working email, or address on file. Only team admins can read it.
missing_details text Which of mobile phone, working email, and address a member has none of, and why an email does not work. Only team admins can read it.
holds_items true or false Holds any of the team's items. Only team admins can read it.
can_log_in true or false Can log in to the team's records. Only team admins can read it.
no_access true or false Has No access: cannot log in. Only team admins can read it.
id_card text The code printed on their ID card, as K7Q4M-2XA9H. Only team admins can read it.
id_card_link text The page their ID card's QR code opens. Only team admins can read it.
id_card_issued date When their ID card was issued. Only team admins can read it.
has_photo true or false Whether the team's records had a photo of them at the last check. An ID card needs one. Only team admins can read it.

awards

Each row is a qualification a member holds, or held.

Field Type Holds
member member, a link Member. Reach its fields in members with a dot: member.name.
qualification qualification, a link Qualification. Reach its fields in qualifications with a dot: qualification.title.
starts_at date Starts.
ends_at date Expires.
current true or false Whether it has started and not ended.

qualifications

Each row is one of the team's qualifications.

Field Type Holds
title qualification, a link Qualification.
expires_months number How many months an award lasts, or null when it never expires.

equipment

Each row is an item: equipment, a vehicle, or a supply.

Field Type Holds
title item, a link Item.
type choice Type. One of Equipment, Vehicle, Supply.
kind text Kind.
status choice Status. One of In service, Unserviceable, Lost, Retired.
barcode text Barcode.
serial text Serial.
location text Location.
expires_at date Expires.
holder member, a link Held by. Reach its fields in members with a dot: holder.name.
cost_per_km dollars What it costs per km, or its kind's cost.
cost_per_hour dollars What it costs per hour, or its kind's cost.
cost_per_use dollars What it costs per use, or its kind's cost.

equipment_use

Each row is an item used on an activity.

Field Type Holds
activity activity, a link Activity. Reach its fields in activities with a dot: activity.title.
item item, a link Item. Reach its fields in equipment with a dot: item.title.
hours hours Hours.
km km Distance.
quantity number How many of a supply were used.

groups

Each row is one of the team's groups.

Field Type Holds
title group, a link Group.
member_count number How many current members are in it.

tax_credit_hours

Each row is one member's hours in one year, counted as their tax credit letter counts them. A member with no hours that year has no row.

Field Type Holds
member member, a link Member. Reach its fields in members with a dot: member.name.
year number The year the hours count in, as a letter for that year.
primary hours Primary hours, as a letter prints them.
secondary hours Secondary hours, as a letter prints them.
total hours Primary and secondary hours together.

team

One row: the team, and what its tax credit letters print about it.

Field Type Holds
name text The team's name.
legal_name text The name tax credit letters print.
mailing_address text Mailing address.
city text The city a form prints after the mailing address.
postal_code text The postal code a form prints after the city.
signer text Who signs tax credit letters.
signer_title text Signer's title.
signer_phone text Signer's phone.
signer_email text Signer's email.

organization

One row: the organization the team belongs to, or none.

Field Type Holds
name text The organization's name.
short_name text Short name.
website text Website.

Custom fields

A team's custom fields are fields too, on activities, members, and equipment. Write one by its title in backticks, as the Fields tab shows it, whatever its case: `Primary Cause`, or from a parent with a dot: activity.`Task Type`. The editor lists each team's own.

select `Task Type`, count(id)
from activities
where kind = 'Incident' and started_at = THIS_YEAR
group by `Task Type`
order by count(id) desc
Its type Reads as Compare it with
Text, text area, time text contains, =, starts with
Number number <, >, sum(); blank when it holds no number
Date date, a day ranges, year(); the day it says, never the day before
Date and time date ranges, year(), in the team's time zone
Single choice choice its options' labels: `Region` = 'South West'
Multiple choice list includes, excludes, as tags
  • An activity's field is its kind's. Fields with one title on two kinds, such as an event's and an exercise's Event Leader, are one field: each activity has one of them.
  • A member's fields are the ones the team copies, on team settings' Members tab, and only team admins read them. So do an item's.
  • A restricted field is for team admins only.
  • A renamed field takes its new title: a report that named the old one says so, with the closest title.

Limits

  • A report shows at most 5,000 rows. With page, each page reads only its own rows, so there is no limit.
  • A pivot makes at most 12 columns.
  • A report stops after 2 seconds. Narrow it with where.

What a report cannot do

  • Read emergency contacts, letter text, or keys. They are not fields. A member's email, mobile, and address are for team admins only.
  • Compare a place with anything but is null or is not null.
  • Keep a name or an address in the query's text: where address contains :town picks it each time it runs, and an agent never sees it.
  • Change records. A report only reads.
  • Read another team's records.
  • Show every field with select *. Name each one.
  • Reach further than a parent's fields: member.name works, attendance.member.name does not.
  • Have more than one switch. A report with pivot has no switch, page, or sort.