# NQL, the query language for key figures

> Every key figure tile in nexxus is an NQL query. This page explains how a query is built, which sources and fields there are, how to label values, what the answer contains and why nobody sees more through NQL than they may see.

**NQL** is the query language behind every key figure in nexxus: behind the tiles on the [dashboard](/use/dashboard/#add-a-key-figure), on the [boards](/use/analytics/) and behind the AI Assistant's answers to questions about numbers. A query is a small JSON object. It names a source, the figures that are counted or summed, the fields to group by, and filters.

```json
{
  "source": "project",
  "measures":   [{"name": "anzahl", "as": "anzahl"}],
  "dimensions": [{"field": "dateCreated", "grain": "month", "as": "monat"}],
  "filters":    [{"field": "isActive", "op": "eq", "value": true}],
  "orderBy":    [{"as": "monat", "dir": "asc"}]
}
```

This query counts the active projects per month in which they were created, sorted by month.

NQL is deliberately not SQL. A query cannot name a table or a column freely. It **selects** from what a source has published: fields, figures, operators and time grains from fixed lists. nexxus refuses anything else and names what would have been valid.

You don't have to be able to write NQL. The AI Assistant and the tile dialog write the query for you. This page helps when you want to read a query, adjust it by hand or send one from a script.

The names of sources, fields and figures come from the server and are German in every language, such as `anzahl` (count) or „Angelegt am“ (created on). The tables below give the English meaning where it helps.

## Where you meet NQL

- **In the tile dialog.** Under the preview, <Ui>Advanced: query as JSON</Ui> opens the <Ui>NQL query</Ui> of the tile. You edit it there directly; it <Ui>Runs only when you press "Preview".</Ui>
- **In the AI Assistant's tools and in AI clients.** The built-in connector "Reports" brings the tools `nx_nql_schema` (what you may evaluate) and `nx_nql_query` (run a query). They run with the rights of the person in the conversation, also in a [connected client like Claude Code](/mcp/connect/).
- **Through the API.** `GET /api/nql/schema` returns the sources you may read, with their fields and figures; `POST /api/nql/query` runs a query. A script authenticates with a [personal API token](/setup/security/#personal-api-tokens) and then has the same rights as you.

## Who sees what

NQL rests on one rule: **nobody gets figures they could not have fetched one by one.** A tile runs for the person looking at it, not for the one who created it. That is why the same tile shows different people different numbers, and why a shared board hands out no data.

<Flow
  steps={[
    { icon: 'key-round', title: 'Permission "Reports"', text: 'Without core.analytics.read there is no query.' },
    { icon: 'database', title: 'Read permission of the source', text: 'For example project.project.read for projects. Some sources are for administrators only.', tone: 'cyan' },
    { icon: 'list-checks', title: 'Published fields', text: 'Only fields and figures the source offers.', tone: 'lavender' },
    { icon: 'building-2', title: 'Tenant and rows', text: 'Only your tenant, only records that are not deleted, plus the row filter of the source.' },
  ]}
  caption="The server checks every step on every query, even for a tile that has been on a board for months."
/>

| Step | What happens on a no |
|---|---|
| Permission **Reports** (`core.analytics.read`) | The query fails with 403. Without the permission there are neither key figure tiles nor boards. |
| Read permission of the source | The source is missing from the person's schema, and a query on it fails with 403. The tile shows <Ui>No permission for this data</Ui>. |
| Published fields | The query fails with 400 and names the valid fields. |
| Tenant and deletion | Rows of other tenants and deleted records are simply not there. |
| Row filter of the source | Rows the person may not see are simply not there (for AI sessions other people's, for absences all except the own ones and those of the teams the person leads). |

Administrators pass every permission check, but not the separation of tenants. There is no `.write` permission for reports, because NQL writes nothing.

## How a query is built

| Field | Required | Form | Effect |
|---|---|---|---|
| `source` | yes | key, e.g. `"project"` | The source. The list is under [The sources](#the-sources). |
| `measures` | one of the two | `[{name, as?, percentOfTotal?}]` or `["anzahl"]` | The figures. `name` is a figure of the source, `as` the column name in the answer (default: `name`). |
| `dimensions` | one of the two | `[{field, grain?, as?, format?, template?, labels?, ids?}]` or `["status"]` | The fields to group by. `grain` for date fields only, the labelling under [Label values](#label-values). |
| `filters` | no | `[{field, op, value}]`, also with groups | Narrow the rows **before** they are counted. `op` defaults to `eq`. Groups with OR and NOT under [Filters](#filters). |
| `having` | no | `[{as, op, value}]`, also with groups | Narrow the groups **after** they were counted. `as` is a figure column. |
| `orderBy` | no | `[{as, dir}]` | Sort by a column of the answer, `dir` is `asc` (default) or `desc`. |
| `limit` | no | number | At most this many rows. Default 1000, at most 5000. |
| `topN` | no | `{by?, limit?, otherLabel?, within?}` | Keep the strongest groups, put the rest together. |
| `totals` | no | `true` or `{grandTotal, subtotalsFor}` | Grand total and subtotals beside the rows. |
| `split` | no | column name of a field | The values of this field become columns. |

### Figures

A figure is a fixed calculation of the source: `anzahl` counts projects, `stunden` sums the hours of time entries, `kunden` counts distinct customers. The query picks the figure by name, not the calculation. So no query can sum a column the source has not released for it.

Every figure has a function: `count`, `count_distinct`, `sum`, `avg`, `min` or `max`. The schema names it under `fn`, together with `"additive": true` or `false`. **Additive** are `count` and `sum`: two partial sums add up to the total. Averages, maximums and counting distinct values cannot be put together like that. nexxus therefore refuses them wherever values would have to be combined: next to a reference field, with `topN` and a remainder, with `totals` and with `percentOfTotal`. A refusal is better than a plausible wrong number.

`percentOfTotal: true` on a figure adds a column `<as>Anteil` with its share of the whole in percent, with one decimal. The shares add up to exactly 100.

### Fields and time grains

A field (dimension) is something you group and filter by: the status, the billing model, the creation date. The schema states its type:

| Type | Example | Notes |
|---|---|---|
| `string` | project name, model | text |
| `number` | impact of a risk (1 to 4) | number |
| `boolean` | active, billable | The tiles show <Ui>Yes</Ui> and <Ui>No</Ui> |
| `enum` | status, type | Fixed values; the schema lists them under `values` and their names under `valueLabels` (`OPEN` is „Offen“) |
| `date` | created on, date | Takes a time grain `grain`: `day`, `week`, `month`, `quarter` or `year`. A value is the start of its period |
| reference (`"reference": true`) | customer of a project, project of a time entry | Points into another module. The answer holds the name, a filter needs the id |

A reference field is turned into names only after counting. If two customers have the same name, they end up in one row; the answer says so with `meta.merged`. That is why a reference only takes additive figures.

With `"ids": true` on the field the answer keeps the id instead: the rows hold ids, the column's `valueLabels` their names, and two customers of the same name stay two rows. That is the form a filter needs, because a filter names the id.

### Filters

A filter is a list of conditions, and a list means all of its entries:

```json
"filters": [
  {"field": "status", "op": "in", "value": ["OPEN", "MITIGATED"]},
  {"field": "dateCreated", "op": "gte", "value": "2026-01-01"},
  {"field": "reviewDate", "op": "isNull"}
]
```

An entry can also be a group, which is how OR and NOT get into a query:

| Group | Holds when |
|---|---|
| `{"all": [...]}` | every entry holds |
| `{"any": [...]}` | at least one entry holds |
| `{"not": ...}` | the entry inside does not hold |

Open risks that are serious or have no review date yet:

```json
"filters": [
  {"field": "status", "op": "eq", "value": "OPEN"},
  {"any": [
    {"field": "impact", "op": "gte", "value": 3},
    {"not": {"field": "reviewDate", "op": "isNotNull"}}
  ]}
]
```

Groups nest up to 5 levels deep (a `not` is no level of its own). One condition holds at most 50 comparisons, one comparison at most 500 values. `having` takes the same groups, with `as` in place of `field`.

| Operator | Meaning | `value` |
|---|---|---|
| `eq`, `ne` | equal, not equal | one value |
| `gt`, `gte`, `lt`, `lte` | greater, greater or equal, less, less or equal | one value |
| `between`, `notBetween` | inside or outside two bounds; the bounds count as inside | exactly two values |
| `in`, `notIn` | one of the values, none of the values | a list |
| `contains`, `notContains` | contains, does not contain | a text |
| `startsWith`, `endsWith` | starts with, ends with | a text |
| `isNull`, `isNotNull` | empty, not empty | none |
| `inLast`, `inNext` | from that long ago up to today, from today up to that far ahead | `{"amount": 7, "unit": "day"}`; `unit` is `day`, `week`, `month`, `quarter` or `year` |
| `inPeriod` | in a calendar period around today; weeks start on Monday | one of `yesterday`, `today`, `tomorrow`, or `last`, `this` or `next` with `Week`, `Month`, `Quarter`, `Year`, e.g. `lastMonth` |

Not every operator fits every field. Every field takes `eq`, `ne`, `in`, `notIn`, `isNull` and `isNotNull`. Numbers, dates and text also take `gt`, `gte`, `lt`, `lte`, `between` and `notBetween`. `contains`, `notContains`, `startsWith` and `endsWith` belong to text, `inLast`, `inNext` and `inPeriod` to dates. Anything else fails with 400.

Write values the way the field's type asks for:

| Type | Value |
|---|---|
| `date` | `"2026-01-01"` (that whole day), a point in time like `"2026-01-01T00:00:00Z"`, milliseconds since 1970, or `{"relativeDays": -7}` |
| `number` | a number; decimals are kept |
| `boolean` | `true` or `false` |
| `enum` | one of the raw values from `values`, e.g. `"OPEN"` |
| `string` | a text |
| reference | the id of the record, not its name; for a field that points at a person also `{"currentUser": true}`, the person asking |

**A day means the whole day**, in the tenant's time zone, on a point in time as well: `eq "2026-03-31"` takes all of that day, `lte "2026-03-31"` includes its evening, `gt "2026-03-31"` starts on 1 April.

**Relative values** are worked out on every run, so a stored query stays right tomorrow. `{"relativeDays": 0}` is today, `-7` a week ago, `3` in three days. `inLast`, `inNext` and `inPeriod` are read the same way, and always in whole days.

**Another field as the value:** `{"field": "dateCreated"}` compares two fields of a row, with `eq`, `ne`, `gt`, `gte`, `lt` and `lte`. The two must have the same type, and two references must point at the same kind of record. A day against a point in time looks at the day of that point in the tenant's time zone.

```json
{"field": "followUpDate", "op": "lt", "value": {"field": "dateCreated"}}
```

**Empty fields:** `ne`, `notIn`, `notContains`, `notBetween` and everything under `not` also find rows where the field is empty, because an empty field is not „Open“. The ordering comparisons find none, so `{"not": {"field": "impact", "op": "gt", "value": 3}}` is not the same as `lte 3`: it also finds risks without an impact.

**Text** ignores case with `eq`, `ne`, `in`, `notIn` and the operators for parts of a text.

**`filters` or `having`?** "Projects from 2026" is a filter: it narrows the rows before they are counted. "Customers with more than five projects" is a `having`: it narrows the groups after they were counted.

```json
{"source": "project", "measures": ["anzahl"], "dimensions": ["kunde"],
 "having": [{"as": "anzahl", "op": "gt", "value": 5}]}
```

### The strongest N and the rest

`topN` keeps the strongest groups and puts the rest together in one row. Nobody reads a chart with forty series; this way the rest stays visible as an honest block instead of quietly missing.

```json
"topN": {"by": "stunden", "limit": 5, "otherLabel": "Sonstige", "within": ["monat"]}
```

- `by`: the figure to rank by. Defaults to the first figure.
- `limit`: how many groups stay. Default 5.
- `otherLabel`: the name of the remainder row, default „Sonstige“ (other). `null` drops the remainder.
- `within`: fields within which to rank. `["monat"]` means "the strongest five per month". If the query has a date besides the ranked field, it must be listed here; otherwise „Sonstige“ would be written into the date column, and nexxus refuses the query.

### Totals and columns

`totals` returns sums **beside** the rows, not between them. A chart that got a "total" row among its data would draw it as one giant bar.

```json
"totals": {"grandTotal": true, "subtotalsFor": ["kategorie"]}
```

`totals: true` is short for the grand total. The answer then holds `grandTotal` (`{column: sum}`) and `subtotals` (per field the groups with their sums).

`split` turns a field into columns. `monat | status | anzahl` becomes `monat | Offen | Behandelt`, the shape a chart with several series needs. At most 50 columns; with more values apply `topN` first.

### The order

After counting, the stages always run in this order: `having`, `topN`, `percentOfTotal`, `totals`, `orderBy`, labels, `split`. A query cannot ask for another order, and the result does not depend on the order of the fields in the JSON.

## Label values

Some values look technical in the data: the object type in the audit log is `app.modules.core.migration.MigrationStatus`, a type of absence is called `SPECIAL_LEAVE` internally. Three options on a field decide how its values read in legends, axes, tables, filter lists and the PDF. They don't change the data: the answer still holds the raw value, and a filter still names the raw value.

```json
"dimensions": [
  {"field": "className", "as": "typ", "format": ["class", "words"]},
  {"field": "impact", "as": "auswirkung", "template": "Level {value}"},
  {"field": "isActive", "as": "aktiv", "labels": {"true": "Active", "false": "Inactive"}}
]
```

| Option | Form | Effect |
|---|---|---|
| `format` | one format or a list of at most four, applied in order | Reshapes the value, see the table below |
| `template` | text with `{value}`, at most 80 characters | Puts text around the value: `3` becomes "Level 3". `""` removes the source's template |
| `labels` | `{raw value: name}`, at most 200, each at most 80 characters | Fixed names for single values. For an `enum` only its values, for yes/no only `true` and `false` |

| Format | From | Becomes |
|---|---|---|
| `raw` | | the value as stored; switches off the source's default |
| `class` | `app.modules.core.migration.MigrationStatus` | `MigrationStatus` (the part after the last dot) |
| `words` | `MigrationStatus`, `SPECIAL_LEAVE` | `Migration Status`, `Special leave` |
| `upper` | `Offen` | `OFFEN` |
| `lower` | `Offen` | `offen` |
| `capitalize` | `offen` | `Offen` (first letter only) |

This is how a name comes about: if the value is in `labels`, that name applies. Otherwise nexxus starts from the name the source knows for the value (for an `enum`, for example, „Offen“ for `OPEN`) or from the value itself, applies the formats in order and puts the result into the template.

A source can set a default format. That is why the audit log's object type shows as `MigrationStatus` without any option; the schema shows the default under `format`. The query replaces the source's `format` and `template`, and `"format": "raw"` switches the default off.

Limits:

- **Date fields** take none of the three options. A tile writes a date by its time grain ("Oct 26", "Q3 2026").
- **Yes/no fields** take `labels` only.
- Nothing labels the remainder of `topN` („Sonstige“).
- `split` names its columns by the labels; each column's key (`as`) stays the raw value. A sort order or a series colour therefore does not move when you change the labels.
- If two raw values end up with the same name, charts show them together.

## The answer

```json
{
  "columns": [
    {"as": "typ", "role": "dimension", "type": "string", "label": "Objekttyp", "grain": null,
     "valueLabels": {"app.modules.communication.email.Email": "Email"}},
    {"as": "anzahl", "role": "measure", "type": "number", "label": "Anzahl Änderungen", "grain": null, "valueLabels": null}
  ],
  "rows": [["app.modules.communication.email.Email", 1800], ["Sonstige", 412]],
  "meta": {"rowCount": 2, "truncated": false, "durationMs": 37, "merged": false, "foldedIntoOther": true, "split": null},
  "subtotals": [],
  "grandTotal": null
}
```

- `columns` describes the columns once, `rows` holds the rows as lists in that order. A chart needs the columns once, not in every row.
- `label` is the column's name, `valueLabels` the labels of a field's values: raw value and name, for the values in the rows whose name differs from the raw value. Whole numbers are text without decimals (`"3"`). With `ids` on a reference it holds the name of every id in the rows. Otherwise `null`.
- **Date values always come as milliseconds since 1970**, whatever the time grain.
- `meta.truncated: true` means the row limit cut rows off. Whoever passes the answer on should say so instead of pretending it is complete.
- `meta.merged`: reference values with the same name were put together. `meta.foldedIntoOther`: `topN` formed a remainder. `meta.split`: the field that became columns.

## Errors

| Status | When | Example |
|---|---|---|
| 403 | The permission for reports is missing, or the read permission of the source; the message names the key | |
| 400 | Unknown source, field, figure, operator or time grain, an invalid enum value, a reference to a column the query does not have, a non-additive figure where values would be added, a label option the field does not take | "Unknown dimension 'kundenname' for source 'project'. Available: billable, billingModel, dateCreated, designation, isActive, isInternal, kunde, projektgruppe" |

The messages are written for people and for language models: they list what would have been valid. The AI Assistant uses them to correct its own query before you get to see a tile.

## The sources

Which sources a person sees depends on their rights. The list below is an administrator's. Fields of type `date` offer every time grain. The first column gives the source's key, its name and the read permission it asks for.

| Source | Fields | Figures |
|---|---|---|
| `project`<br/>Projekte (projects)<br/>`project.project.read` | `designation` Projekt, `kunde` Kunde (customer, reference), `projektgruppe` Projektgruppe (project group, reference), `isActive` Aktiv, `isInternal` Intern, `billable` Abrechenbar, `billingModel` Abrechnungsart (billing model), `dateCreated` Angelegt am | `anzahl` Anzahl Projekte, `kunden` Kunden (distinct customers) |
| `timesheetEntry`<br/>Zeiteinträge (time entries)<br/>`timesheet.entry.read` | `entryDate` Datum, `projekt` Projekt (reference), `source` Herkunft (origin), `billable` Abrechenbar | `eintraege` Einträge (entries), `stunden` Stunden (hours) |
| `vacationRequest`<br/>Abwesenheiten (absences)<br/>`absence.vacationRequest.read` | `mitarbeiter` Mitarbeiter (employee, reference), `type` Art, `status` Status, `fromDate` Beginn (start) | `antraege` Anträge (requests), `tage` Tage (days) |
| `partner`<br/>Kunden und Lieferanten (customers and suppliers)<br/>`partner.partner.read` | `name` Name, `partnerType` Art, `isActive` Aktiv, `isInternal` Intern, `currency` Währung, `dateCreated` Angelegt am | `anzahl` Anzahl |
| `ismsRisk`<br/>ISMS-Risiken (ISMS risks)<br/>`isms.risk.read` | `status` Status, `likelihood` Eintrittswahrscheinlichkeit, `impact` Auswirkung, `reviewDate` Nächste Prüfung, `dateCreated` Angelegt am | `anzahl` Anzahl Risiken, `wahrscheinlichkeitSchnitt` and `auswirkungSchnitt` (averages) |
| `ismsMeasure`<br/>ISMS-Maßnahmen (ISMS measures)<br/>`isms.measure.read` | `category` TOM-Kategorie, `measureType` Art, `status` Status, `recurrence` Turnus, `approvalRequired` Freigabe nötig, `reviewDate` Erste Fälligkeit, `dateCreated` Angelegt am | `anzahl` Anzahl Maßnahmen |
| `form`<br/>Formulare (forms)<br/>`formBuilder.read` | `name` Formular, `type` Typ, `status` Status, `version` Version, `dateCreated` Angelegt am | `anzahl` Anzahl Formulare, `hoechsteVersion` Höchste Version |
| `user`<br/>Benutzerkonten (user accounts)<br/>`authentication.user.read` | `username` Benutzername, `accountType` Kontotyp, `invitationPending` Einladung offen, `lastLoginAt` Letzte Anmeldung, `dateCreated` Angelegt am | `anzahl` Anzahl Konten |
| `aiSession`<br/>KI-Sitzungen (AI sessions)<br/>`ai.session.use` | `dateCreated` Begonnen am, `model` Modell, `status` Status, `origin` Herkunft, `createdById` Benutzer-ID | `sitzungen` Sitzungen, `nachrichten` Nachrichten, `nachrichtenSchnitt` Ø Nachrichten je Sitzung |
| `aiScheduleRun`<br/>Schedule-Läufe (schedule runs)<br/>`aiAssistant.schedule.read` | `status` Status, `trigger` Auslöser, `scheduleId` Schedule-ID, `model` Modell, `scheduledFor` Solltermin, `dateCreated` Angelegt am | `laeufe` Läufe, `dauerMs` Ø Dauer (ms), `werkzeugaufrufe` Werkzeugaufrufe, `werkzeugfehler` Werkzeugfehler |
| `auditLog`<br/>Protokoll (Änderungen) (audit log)<br/>*administrators only* | `className` Objekttyp (format `class`), `eventName` Vorgang, `actor` Person, `dateCreated` Zeitpunkt | `anzahl` Anzahl Änderungen, `personen` Aktive Personen (distinct) |

Some sources are deliberately thin: risks come without title and description, partners without contact and bank details, the audit log without old and new values, user accounts without personal details. What a source does not publish, nobody can evaluate.

Two sources limit rows per person: **AI sessions** show each person only their own, administrators all; **absences** show your own and those of the teams you lead, like the list of absences.

### Sources from packages

Every entity of an active package module is also a source of its own, with the key `<package>:<entity>`. The list above leaves them out, because they depend on what your tenant has installed. The package does not have to do anything for this.

| What | Rule |
|---|---|
| Name | The plural of the entity, followed by the package in brackets, for example <Ui>Kunden (Vorgänge)</Ui>. |
| Permission | The entity's read permission. A record has no rights of its own: whoever may read the entity sees all of its rows. |
| Fields | The entity's indexed fields and every reference, plus `id`, `createdAt`, `updatedAt` and `createdBy`. A field without an index is missing here too. |
| Figures | `anzahl`, plus `summe_<field>`, `schnitt_<field>`, `min_<field>` and `max_<field>` for each number field. |

Three things behave differently from the sources above:

- **Fields with several values:** a field with `multiple` lists a record under each of its values. A total over such groups counts it once per value.
- **Text:** comparisons ignore case and reach as far as the 500th character of a field.
- **Switched off:** while a package is deactivated, its sources are missing. Tiles on them show <Ui>This area is switched off</Ui>, and notification rules read <Ui>cannot check</Ui>. After activating the package, both work as before.

## Limits

- **One source per query.** NQL joins no sources. Values from other modules come in only as a reference field, such as a project's customer.
- **No second Y axis.** Two quantities of different scale are two tiles.
- **Rows:** 1000 by default, at most 5000; before `topN` and merging, at most 5000 groups from the database. `split` at most 50 columns.

## For developers: offer a source

The entities of a package become sources by themselves, see [Sources from packages](#sources-from-packages). All other sources are made in the nexxus code.

Usually an annotation makes an entity queryable, exactly as far as it lists:

```kotlin
@NqlExposed(
  label = "Protokoll (Änderungen)",
  permission = "core.auditLog.read",
  adminOnly = true,
  description = "Änderungen im System: Anzahl nach Objekttyp, Vorgang, Person und Zeit.",
  dimensions = [
    NqlDim(field = "className", label = "Objekttyp", format = ["class"]),
    NqlDim(field = "eventName", label = "Vorgang"),
    NqlDim(field = "dateCreated", label = "Zeitpunkt"),
  ],
  measures = [
    NqlMeas(fn = "count", name = "anzahl", label = "Anzahl Änderungen"),
  ])
@Entity
open class AuditLog { ... }
```

- **An allow-list, on purpose.** Only what is listed under `dimensions` and `measures` can be queried. Otherwise every new field would be queryable at once, which at the next `passwordHash` would be a data leak by omission.
- **`permission` is required** and the same key the module's list asks for. `adminOnly = true` opens a source to administrators only.
- **The type** is read from the field. Values get names through `valueLabels = ["OPEN=Offen", ...]`; if an enum has a `label` property, that one applies. `format` and `template` set the labelling as long as a query says nothing else.
- **The key** of the source is the class name with a lower-case first letter, unless `key` says otherwise.

If a source needs a row filter below the tenant (own records only) or a field from another module, write it as a `@Component` implementing `NqlSource`. Fields from other modules group by the id and are labelled afterwards through an `NqlRefResolver`, with one query per result:

```kotlin
NqlDimension.ref("kunde", "partnerId", NqlEntityLabelResolver(entityManager, Partner::class.java) { it.name }).label("Kunde")
```

No source takes care of the tenant and deletion itself. nexxus sets both on every query. Without a tenant, a query returns nothing rather than everything.

## Next steps

<CardGrid>
  <LinkCard title="Reports" description="Create, filter, share and download boards." href="/use/analytics/" />
  <LinkCard title="Build your dashboard" description="Set up a key figure on the start page." href="/use/dashboard/#add-a-key-figure" />
  <LinkCard title="Permissions" description="Which permission opens which source." href="/reference/permissions/" />
</CardGrid>