# 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](/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.

```sql
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.

```sql
select title, started_at, attended_count
from activities
```

### from

`from subject`

The subject the rows come from. One per report.

```sql
select name, joined_at
from members
```

### where

`where condition`

Keeps the rows the condition is true for.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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.

```sql
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`.
