Reports

The reporting module allows organizations to automatically create reports of aggregated data for a given timespan. This can be used to track indicators and export anonymous data.

There are currently two systems available to generate reports, both using the same config documents (ReportConfig:*):

• SQL-based queries executed server-side using the SQS server
• (legacy) client-side generator using the aggregation & query syntax below

SQL Reports

This feature requires the aam-services backend. It is based on structured-query-service (SQS) (this creates a read-only copy of the data in the CouchDB and allows to run SQLite queries against it). SQS enables the possibility to create SQL-based queries in reports. The SQS image is not available as open source and needs a licence. It is pulled from a private container registry.

Follow the instructions on the aam-services repository to set up and enable the API.

Using AI agents to generate SQL Queries

LLMs like ChatGPT or Claude can generate report queries for you.

Configuration

The reports can be defined as ReportConfig:* entities.

There are different modes for ReportConfig:

sql

Requirements: SQS

Queries are based on SQLite and do not support multiple queries in one string or some SQL commands. Do not use special characters, like accents, in IDs or report column names as they cause unstable outputs.

Available tables and columns

Every entity type defined in the Config:CONFIG_ENTITY document becomes a SQL table (e.g. Child, School), with one column per configured field. Fields of dataType file are skipped and have no column.

In addition, the backend hard-wires some columns into every such entity table. These are derived from internal metadata that every entity has, so they are available without being listed in the config document:

The _ prefix marks columns that are generated from internal metadata rather than configured entity fields and avoids clashes with custom fields of the same name. For example, to count records created within the reporting period:

SELECT count(*) AS "newly registered"
FROM Child
WHERE _created_at BETWEEN $startDate AND $endDate

Human-readable labels for dropdown (configurable-enum) fields

Dropdown fields store the option id (e.g. gender = "M"), not its label. The hard-wired ConfigurableEnumOption view provides one row per dropdown option (enum_id, option_id, label), so labels can be joined in directly:

SELECT c.name, COALESCE(g.label, c.gender) AS gender
FROM Child c
LEFT JOIN ConfigurableEnumOption g
  ON g.enum_id = 'genders' AND g.option_id = c.gender

enum_id is the id of the enum definition without its ConfigurableEnum: prefix. The COALESCE keeps the raw id visible for values that have no matching option. The underlying raw ConfigurableEnum table (one row per enum, options as JSON array in values) is also available for special cases. It only has these two columns, none of the default columns listed above.

Simple SQL Report

// app/ReportConfig:test-report
{
  "_id": "ReportConfig:test-report",
  "title": "Test Report",
  "mode": "sql",
  "reportDefinition": [
    {
      "query": "SELECT c.name as name, c.dateOfBirth as dateOfBirth FROM Child c"
    }
  ]
}

SQL Report With Arguments

Use named placeholders in the SQL query (for example $startDate and $endDate) and map these args via transformations.

// app/ReportConfig:test-report
{
  "_id": "ReportConfig:test-report",
  "title": "Test Report",
  "mode": "sql",
  "transformations": {
    "startDate": ["SQL_FROM_DATE"],
    "endDate": ["SQL_TO_DATE"]
  },
  "reportDefinition": [
    {
      "query": "SELECT c.name as name, c.dateOfBirth as dateOfBirth FROM Child c WHERE _created_at BETWEEN $startDate AND $endDate"
    }
  ]
}

Nested SQL Report (Grouped)

reportDefinition supports nested groups. Each item is either:

• a query item: { "query": "SELECT ..." }
• or a group item: { "groupTitle": "...", "items": [...] }

Groups can contain queries and other groups recursively.

The hierarchical SQL view is rendered as a Name + Count table. That means each row should represent one metric value, so a query here should select a single numeric column. If a query returns multiple columns, only one value is shown as the row value and the other fields are used as row label details.

The row label comes from the column alias. A single-column query result reaches the view as { "<alias>": <value> }, and that alias is printed in the Name column. So alias each metric with the label that should appear in the report (COUNT(*) as "Male students"), not with a generic name: aliasing every query as count renders a list of rows all called "count". A groupTitle labels its group row, whose value is the sum of the rows nested below it.

// app/ReportConfig:test-report-grouped
{
  "_id": "ReportConfig:test-report-grouped",
  "title": "Test Report Grouped",
  "mode": "sql",
  "reportDefinition": [
    {
      "query": "SELECT COUNT(*) as 'All children' FROM Child c"
    },
    {
      "groupTitle": "By School Type",
      "items": [
        {
          "query": "SELECT COUNT(*) as 'Public school' FROM Child c JOIN School s ON s._id = c.schoolId WHERE s.privateSchool = 0"
        },
        {
          "query": "SELECT COUNT(*) as 'Private school' FROM Child c JOIN School s ON s._id = c.schoolId WHERE s.privateSchool = 1"
        }
      ]
    }
  ]
}

In the UI, grouped SQL reports are rendered as a hierarchical report view, while single-query SQL reports are shown as a flat table.

SQL recipes

Useful patterns for common Aam Digital reporting questions.

Calculate age from date of birth

SELECT name,
  (strftime('%Y', 'now') - strftime('%Y', dateOfBirth))
    - (strftime('%m-%d', 'now') < strftime('%m-%d', dateOfBirth)) AS age
FROM Child

Conditional counting ("COUNT IF")

SELECT COUNT(CASE WHEN c.gender = 'F' THEN c._id END) AS total_female FROM Child c

Join a linked entity to show a human-readable name

A field with dataType: "entity" stores the id of the referenced entity, not its name. Join the referenced table to resolve it:

SELECT c.name AS child, s.name AS school
FROM Child c
JOIN School s ON s._id = c.schoolId

Unpack a multi-select (isArray) entity-reference field

A field with dataType: "entity" and isArray: true stores a JSON array of ids (e.g. Note.children). Unpack it with json_each() and join to resolve each id to a name:

SELECT n.subject AS note, c.name AS participant
FROM Note n, json_each(n.children) p
JOIN Child c ON c._id = p.value

Attendance status of individual participants from EventNote

childrenAttendance stores one [childId, {status, remarks}] pair per participant as a JSON array, which can be unpacked with SQLite's json_each():

SELECT json_extract(value, '$[0]') AS participant_id,
  json_extract(value, '$[1].status') AS status,
  json_extract(e.schools, '$[0]') AS team_id
FROM EventNote e, json_each(e.childrenAttendance)
JOIN Child AS c ON c._id = participant_id
JOIN School AS s ON s._id = team_id

The same pattern calculates an attendance percentage per participant, filtered by event category:

SELECT json_extract(value, '$[0]') AS participant_id, e.category,
  ROUND((SUM(CASE WHEN json_extract(value, '$[1].status') = 'PRESENT' THEN 1 ELSE 0 END) * 100.0
    / COUNT(e._id)), 2) AS attendance_percentage
FROM EventNote e, json_each(e.childrenAttendance)
JOIN Child c ON c._id = json_extract(value, '$[0]')
WHERE e.category IN ('LEARNING_GROUP', 'PIC') AND e.date BETWEEN $startDate AND $endDate
GROUP BY c._id, e.category

Group by each option of a multi-select dropdown

A multi-select (isArray) field is stored as a JSON array of option ids (e.g. ["pizza", "burger"]). To count each selected option individually (a row counts towards every option it includes):

SELECT COALESCE(opt.label, sel.value) AS option, COUNT(*) AS count
FROM Child c, json_each(c.favoriteFoods) sel
LEFT JOIN ConfigurableEnumOption opt
  ON opt.enum_id = 'favoriteFoods' AND opt.option_id = sel.value
WHERE json_valid(c.favoriteFoods)
GROUP BY option
ORDER BY count DESC, option

Combine EventNote and Note rows for a joint query

SELECT n.subject, n.date FROM (SELECT * FROM EventNote UNION SELECT * FROM Note) n

WARNING: double-check that Config:CONFIG_ENTITY lists the same attributes in the exact same order for entity:Note and entity:EventNote. Otherwise the two queries will not be merged correctly and some columns may be missing data!

Compare multiple records linked to one person (e.g. pre vs. post test results)

Count children who have two linked observation records where the "post" value is higher than the "pre" value:

SELECT COUNT(DISTINCT c._id) AS "children who improved"
FROM Child c
INNER JOIN HistoricalEntityData hd_pre
  ON c._id = hd_pre.relatedEntity AND hd_pre.type = 'PRE'
INNER JOIN HistoricalEntityData hd_post
  ON c._id = hd_post.relatedEntity AND hd_post.type = 'POST'
WHERE hd_post.value > hd_pre.value

Known limitations

• CONCAT is not supported; use the SQLite || operator instead (e.g. firstname || ' ' || lastname).

JSON-Query Reports (legacy)

Aggregation structure

Inside the reportDefinition an array of aggregations can be added. The following example shows the structure of an aggregation.

{
  "label": "Events",
  "query": "Event:toArray[*date >= ? & date <= ?]",
  "groupBy": ["category"],
  "aggregations": [
    {
      "query": ":getParticipantsWithAttendance(PRESENT):unique:addPrefix(Child):toEntities",
      "groupBy": ["gender", "religion"],
      "label": "Participants"
    }
  ]
}

• The label will be used to display the length of the query result in the final report.
• The query defines a valid JSON-Query.
• groupBy defines an array of properties by which the results of the query are grouped, these results will be nested in the top-level query.
• The aggregations array can be filled with further aggregations with the same structure. They will be executed on the result of the query as well as on each groupBy result.

query syntax

A full documentation can be found here. The most top-level aggregation has to start with the entity which should be queried. This can be done by selecting the entity type and chaining it with :toArray: e.g. Child:toArray. Now the array of all children is ready to be aggregated. You could for example be interested in all the children older than 10: Child:toArray[* age > 10]. The * tells the query language to select all the children and not just one. The simple age call refers to the get age() function of the child entity. If you are interested in all female children older than 10 the call would look like Child:toArray[* age>10 & gender=F]. If you are only interested in the names of these children Child:toArray[* age>10 & gender=M].name will transform the array of children into an array of strings.

To navigate between different entities a set of functions extends the query syntax. The full documentation for all the available functions can be found and extended in the QueryService. The first function you already know :toArray which creates an object into an array of values of this object. The following functions also exist:

• :unique removes all duplicates from an array
• :addPrefix(<ENTITY_TYPE>) adds the prefix <ENTITY_TYPE> to all strings in the input array. This is necessary to use the :toEntities function. The prefix will only be added if it is not set yet.
• :toEntities transforms an array of entity-ids into an array of entities. The IDs need to have the full format e.g. Child:1234-5678. Therefore :addPrefix should always be used to add the correct entity before calling :toEntities.
• :getRelated(<ENTITY_TYPE>, <PROPERTY_NAME>) is used to get the entity or entities which are mentioned through ids on a property of another entity e.g., to get all children that are part of a set of notes write Note:toArray:getRelated(Child, children)
• :getIds(<PROPERTY_NAME>) works similar to :getRelated but does not transform the ids into entities.
• :filterByObjectAttribute(<PROPERTY_NAME>, <KEY>, <VALUE>) is used to filter by an attribute which is a complex object like a configurable-enum. <PROPERTY_NAME> refers to the name of the property of the parent attribute, <KEY> refers to a key of this property and <VALUE> refers to the value(s) for which should be filtered. <VALUE> can be a string of multiple matches which are separated by |. E.g., to get all notes which are home visits or guardian talks write: Note:toArray:filterByObjectAttribute(category, id, HOME_VISIT|GUARDIAN_TALK)
• :getParticipantsWithAttendance(<ATTENDANCE_STATUS>) only returns the children with the given attendance for a set of notes. The attendance refers to the :countAs attribute. To get all present children write Note:toArray:getParticipantsWithAttendance(PRESENT)
• :addEntities(<ENTITY_TYPE>) can be used to create an array holding multiple entity types. E.g., to create an array holding notes and event notes write Note:toArray:addEntities(EventNote).