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
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, andaddressare for team admins only. - Compare a
placewith anything butis nulloris not null. - Keep a name or an address in the query's text:
where address contains :townpicks 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.nameworks,attendance.member.namedoes not. - Have more than one
switch. A report withpivothas noswitch,page, orsort.