# Report queries

> Write reports as short queries in RepzoQL: getting started, running queries, related records, and the full reference.

A report query describes a report in a few lines of text. The language is called **RepzoQL**. It's another way to build the same reports as the [report builder](https://workstation.repzo.com/docs/analytics/reports): every query fills the builder, and every builder report can be shown as a query. If you have used ShopifyQL or SQL, it will feel familiar.

```sql
FROM deals
SHOW count, won_value
GROUP BY owner
WHERE stage = "Proposal" OR stage = "Negotiation"
DURING this_quarter
COMPARE TO previous_quarter
```

## Getting started

Each step answers a question by adding one clause to the query before it.

**How many deals do we have?** Start with the object and what to count. See [FROM and SHOW](https://workstation.repzo.com/docs/analytics/repzoql/from-and-show).

```sql
FROM deals
SHOW count
```

**How much did we win this quarter?** Add a metric and a period. See [SINCE, UNTIL, DURING](https://workstation.repzo.com/docs/analytics/repzoql/since-until-during).

```sql
FROM deals
SHOW count, won_value
DURING this_quarter
ON won_at
```

**Who won it?** Split the numbers by a field. See [GROUP BY](https://workstation.repzo.com/docs/analytics/repzoql/group-by).

```sql
FROM deals
SHOW count, won_value
GROUP BY owner
DURING this_quarter
ON won_at
```

**Only deals from the website?** Filter the records. See [WHERE](https://workstation.repzo.com/docs/analytics/repzoql/where).

```sql
FROM deals
SHOW count, won_value
GROUP BY owner
WHERE contacts.source = "Website"
DURING this_quarter
ON won_at
```

**Better or worse than last quarter?** Compare periods and sort. See [COMPARE TO](https://workstation.repzo.com/docs/analytics/repzoql/compare-to) and [ORDER BY](https://workstation.repzo.com/docs/analytics/repzoql/order-by-and-limit).

```sql
FROM deals
SHOW count, won_value
GROUP BY owner
WHERE contacts.source = "Website"
DURING this_quarter
ON won_at
COMPARE TO previous_quarter
ORDER BY won_value DESC
```

## Running queries

### In the report page

Open **Analytics → Reports** and start or open a **Freeform** report. The query sits above the chart; while the report is empty it's just the Ask Fox box, and the arrow (or **⌘ E**) opens the editor. It updates as you change the builder on the right, and the builder follows the query when you run it with **⌘ Enter**. **Controls** at the top hides the right-hand panel to give the chart the full width.

The editor helps as you type:

- Suggestions open on a new line, after a space, and after you pick one, with a short description under each. The selected one shows faded after the cursor; **Tab** or **Enter** takes it.
- Mistakes are underlined, with a fix such as "Did you mean `count`?".
- The book icon lists the metrics and fields of each object; click a name to add it.
- The **?** icon opens this documentation.

**Cohorts** is the report page's second mode, for retention reports without writing a query. See [COHORT](https://workstation.repzo.com/docs/analytics/repzoql/cohort#in-the-report-builder).

### Ask Fox

Describe a report in the box under the query, such as "average won deal by owner, only owners with at least 3 deals". Fox writes the query, explains what it built in one sentence, and runs it. Ask again to change it, for example "split it by month" or "compare with last year". Fox in the side panel answers number questions the same way, with your own permissions and data visibility.

### Saving, sharing, and links

A report written as a query is saved with its query. When it's opened, the query runs again, so `me` means the person viewing it and `-30d` means the last 30 days from today. The same is true for dashboard widgets added from the report: on a shared dashboard each person sees their own numbers.

The address of a report keeps its query (`?q=`), so you can send the link to a colleague.

### Using queries from the API

Integrations can run a query with `POST /api/v1/reports/query` and the `reports:read` scope. See [Developer API](https://workstation.repzo.com/docs/configuration/developer-api) for keys and scopes.

```json
{ "query": "FROM deals SHOW count, won_value GROUP BY owner DURING this_quarter WITH TOTALS" }
```

The response has the compiled report, its rows, the comparison rows when the query compares periods, and the totals. A mistake returns a validation error with its line, column, and suggestions.

Queries also build saved reports and dashboard widgets (`reports:write`):

| Call | Body |
|---|---|
| `POST /api/v1/reports` | `{ "name": "Won by owner", "query": "FROM deals SHOW won_value GROUP BY owner" }` |
| `POST /api/v1/dashboards` | `{ "name": "Sales", "template": "sales" }` (or `blank`, `default`) |
| `POST /api/v1/dashboards/{id}/widgets` | `{ "title": "Revenue trend", "query": "FROM deals SHOW won_value TIMESERIES month DURING last_12_months", "ownPeriod": true }` |

A widget follows the dashboard's date range unless `ownPeriod` is set, in which case it uses the query's own `DURING` and `COMPARE TO`. `GET /api/v1/dashboards/{id}` lists each widget with its query, so you can run it with `POST /api/v1/reports/query`. The [Repzo CLI](https://workstation.repzo.com/docs/configuration/developer-api) has the same commands.

## Related records

Reports can mix objects that are linked to each other. Write the related object's name and a dot:

```sql
-- Deals by their account's industry, for contacts from the website
FROM deals
SHOW count, value
GROUP BY accounts.industry
WHERE contacts.source = "Website"
```

```sql
-- Contacts with their deals and orders side by side
FROM contacts
SHOW count, deals.count, deals.won_value, orders.total
GROUP BY source
```

A related object follows every link between the two records. To follow one specific reference, use its field name instead, for example `GROUP BY decision_maker.title` on deals.

Deals, contacts, accounts, activities, price offers, invoices, tickets, projects, appointments, requests, orders, carts, subscriptions, listings, and campaigns can be combined. Products, product variants, line items, articles, forms, and suppliers are reported on their own. Each object keeps its own permissions and [data visibility](https://workstation.repzo.com/docs/administration/departments). See [Objects](https://workstation.repzo.com/docs/analytics/repzoql/objects).

## Parts of a query

| Clause | What it does |
|---|---|
| [FROM and SHOW](https://workstation.repzo.com/docs/analytics/repzoql/from-and-show) | The object, and the numbers to show |
| [WHERE](https://workstation.repzo.com/docs/analytics/repzoql/where) | Only count matching records |
| [GROUP BY](https://workstation.repzo.com/docs/analytics/repzoql/group-by) | Split the numbers by fields |
| [TIMESERIES](https://workstation.repzo.com/docs/analytics/repzoql/timeseries) | Split by time, with no gaps |
| [SINCE, UNTIL, DURING](https://workstation.repzo.com/docs/analytics/repzoql/since-until-during) | The period, and which date it uses |
| [COMPARE TO](https://workstation.repzo.com/docs/analytics/repzoql/compare-to) | A second period to compare with |
| [WITH](https://workstation.repzo.com/docs/analytics/repzoql/with) | Totals, percent change, running totals, currency, time zone |
| [HAVING](https://workstation.repzo.com/docs/analytics/repzoql/having) | Keep only groups whose numbers match |
| [ORDER BY and LIMIT](https://workstation.repzo.com/docs/analytics/repzoql/order-by-and-limit) | Sort and page the rows |
| [COHORT](https://workstation.repzo.com/docs/analytics/repzoql/cohort) | Follow people from their first record |
| [VISUALIZE](https://workstation.repzo.com/docs/analytics/repzoql/visualize) | Pick the chart |

The [syntax overview](https://workstation.repzo.com/docs/analytics/repzoql/syntax) covers names, values, comments, and errors; [Functions](https://workstation.repzo.com/docs/analytics/repzoql/functions) covers `sum()`, math, and date functions.

## Related pages

- [Reports](https://workstation.repzo.com/docs/analytics/reports)
- [Dashboard](https://workstation.repzo.com/docs/analytics/dashboard)
- [Properties](https://workstation.repzo.com/docs/administration/properties)
- [Developer API](https://workstation.repzo.com/docs/configuration/developer-api)