Building Custom Reports

The report builder answers questions the prebuilt dashboards don't cover. "How many students in Biology 101 dropped out in the last 30 days?" "Which quizzes have the lowest pass rates?" "What's the monthly trend of certificates issued across my top 5 courses?"

Build a report once, save it, run it anytime. Share it with your institution. Schedule it as a weekly email. Export it as CSV, Excel, or PDF. Set threshold alerts so you get notified when a number crosses a line.

Getting There

Open Reports in the Insights sidebar group, or go to /analytics/reports directly. Click New Report to start from a blank slate, or open any saved report and click Edit to modify it. Editing lands you in the same builder with the saved definition pre-loaded.

The Three Panels

Unlike a step-through wizard, the builder is a single full-height screen with three panels:

┌──────────────────────────────────────────────────────────────────────┐
│  × │ Report Name │ Entity ▼ │ Preview │ Save As │ Save               │
├──────────────┬──────────────────────────────┬──────────────────────┤
│  Fields      │  Live preview table          │  Outline │ Filters    │
│              │                              │                      │
│  🔍 Search   │  #  First Name  Last Name…   │  Group Rows (up to 2)│
│  ☐ First Name│  1  Nidhi       Hemanth      │  Aggregations         │
│  ☑ Last Name │  2  Rahul       Kumar        │  Columns (5)          │
│  ☑ Student…  │  3  Anjali      Sharma       │  Chart Type           │
│  ☐ Gender    │                              │                      │
│  ☑ DoB       │  Showing 3 of 3 rows · 12ms  │                      │
│              │                              │                      │
└──────────────┴──────────────────────────────┴──────────────────────┘

Everything updates live. Check a field on the left, a new column appears in the center. Add a filter on the right, rows drop. Change the chart type, the preview switches between table and chart. There's no "apply" button — every edit is immediately reflected in the preview.

Top bar

× (Close)

Returns to the saved reports list. Unsaved changes are discarded; the browser doesn't warn you, so save before clicking away if you want to keep the changes.

Report name

Click and type. The name shows in the save dialog and becomes the filename when you export. Max 200 characters.

Entity dropdown

The data source. Every field and filter in the rest of the builder is scoped to this entity. Switching entities wipes your fields, filters, groupings, and aggregations — they're entity-specific. The builder seeds a new blank definition when you switch, so you'll see the first few fields of the new entity auto-selected.

Available entities:

Preview button

Forces a re-run. The center panel already updates on every change, so you usually don't need this — use it after external data changes (a colleague just enrolled 50 students, a cron just ran, etc.).

Save As / Save

• Save — commits the current definition to the open report. If you're in a new report, it opens the save dialog first.
• Save As — always opens the save dialog. Lets you create a new report from an existing one (the original stays unchanged).

The save dialog asks for:

• Name (required) — e.g. "Biology dropouts last 30 days"
• Description (optional) — when/why you'd use this
• Visibility — Private (only you) or Institution (everyone in your institution)

---

Left panel — Field picker

A searchable checkbox list of every field the current entity supports.

Type badges

Each field shows a small type badge on the right:

The badge tells you which operators work for that field in the Filters tab, and which aggregations apply. Numeric fields can be summed and averaged; enums can't. Dates can be grouped by day/week/month; strings can't.

Search

Type any substring of the field name or label in the search box to filter the list. Case-insensitive.

Selection = visible columns

Checking a field adds it to definition.fields, which directly drives:

• The columns in the preview table (rows mode)
• The columns in the exported CSV/XLSX/PDF
• The column order — the field appears in the same order as your checks, though you can reorder in the Outline tab's Columns section

Unchecking a field removes it from all three. The list on the left shows a blue highlight on every selected field so you can see at a glance which fields are in the current report.

PII fields

Fields marked as personally-identifying (student names, emails, internal IDs) are hidden from non-admin viewers. If you're a teacher and you don't see Student Name in the list, that's why — the API filters them out before the builder ever sees them.

Related columns (no more raw IDs)

Many fields on one entity are actually sourced from a related entity. For example, on the Enrollments entity you'll see Course, Course Status, Course Level, Category, Student Name, Student Email, Student Code — none of these are actually columns on the Enrollment table. The engine joins to the Course and Student tables (via Prisma or an OptiCRM lookup) and flattens the values into the result row.

You see the real course title, not cm3abc123xyz. You see "Nidhi Hemanth", not a studentId UUID. Raw ID fields (id, courseId, studentId) still exist at the bottom of the field list if you actually need the opaque identifier, but the default 5-column selection always picks human-readable ones.

---

Center panel — Live preview

The main view. Shows whatever the current definition computes right now.

Rows mode (no grouping, no aggregation)

A table with one row per matching record. First column is # (row number); the rest are whatever fields you selected in the left panel. Up to 100 rows are shown in the preview; the footer says Showing first 100 of 12,345 rows when there's more.

The table header uses the field label (e.g. "Enrolled At"), not the raw field name (createdAt). Dates are formatted with your browser's locale. Booleans show as Yes / No. Numbers show with locale-appropriate thousands separators.

Buckets mode (grouping or aggregation)

When you add a grouping or an aggregation, the preview flips to a bucket view:

• Chart type = table or kpi — a compact summary table (or one big number for a single KPI)
• Chart type = line / area / bar / stacked_bar / pie / funnel — an actual Recharts chart

The meta line at the top shows row count and execution time: 1,234 rows in 47ms · cached. The cached indicator appears when the result was served from Redis — see the Analytics Overview page for the cache semantics.

Error state

If the current definition is invalid (missing field, bad operator, etc.), the preview shows a red error banner instead of a chart. The error message is the same one the save endpoint returns, so you can see the issue before you try to save.

Empty state

If you've unselected every field and haven't added any aggregations, the preview shows a "No columns selected" message. Pick something on the left to bring it back.

---

Right panel — Outline and Filters tabs

Switch between the two tabs at the top of the panel. A small badge on the Filters tab shows how many filters are active.

Outline tab

Four sections, top to bottom:

Group Rows (up to 2)

A grouping breaks your data into buckets. In v1 you can have one grouping; the UI has a 2-slot layout ready for two-dim grouping when the engine supports it.

Click the dropdown, pick a field to group by. If you pick a date field, the grouping defaults to day granularity. If you pick a categorical field (enum, string), each unique value becomes a bucket.

Adding a grouping:

• Auto-sets the chart type to something sensible (line for time groupings, bar for categorical)
• Seeds a count aggregation if you don't have one — grouped reports need at least one aggregation

Click the × on a grouping to remove it.

Aggregations

The numbers computed per bucket (or for the whole set if there's no grouping).

Click the dropdown, pick either:

• Count (rows) — simple row count; no field needed
• A specific field — picks the first supported aggregation function for that field's type (sum for numbers, count for everything else)

Each aggregation shows as count, sum(price), avg(finalScore), etc. Click × to remove. You can add as many as you want; each becomes a separate data series in the chart.

Columns (N)

A list of the fields you've checked in the left panel, in export order. Each row has:

• A drag handle on the left (grip icon)
• The field label
• An × to remove

Drag the grip handle up and down to reorder columns. Column order matters for CSV/XLSX/PDF exports — the exporter walks this list in order. Arrow keys work too after you press Space on the handle (for keyboard-only users).

Removing a column here unchecks it in the left panel — the state is shared.

Chart Type

A dropdown with the chart types allowed for the current entity:

The builder auto-picks a default when you add your first grouping or aggregation; you can override anytime.

Filters tab

A list of filter rows plus an Add filter button.

Each filter row has:

1. A numbered badge — the filter's 1-based index. You'll use this if you write a filter logic expression.
2. Field dropdown — pick the field to filter on. The list only shows filterable fields (materialized resolver fields like studentName can't be filtered because they don't live in Postgres; studentId can).
3. Operator dropdown — only operators valid for that field type.
4. Value input — adapts to the operator:
   • Single input for equals, contains, greater than, etc.
   • Two inputs for between
   • Number-with-suffix for last N days
   • Comma-separated list for in list / not in list
   • Dropdown for enum values
   • No input for is empty / is not empty
5. × to remove

Operator availability by field type:

Filter logic

When you have two or more filters, a "Filter Logic (optional)" text field appears at the bottom of the tab. By default, filters are ANDed — every condition must match. If you want OR, type a logic expression:

(1 AND 2) OR 3

Numbers are the 1-based indices of the filter rows, top to bottom. The parser supports parentheses, AND, OR, and nesting. Invalid expressions silently fall back to implicit AND.

Example — "students who completed Biology OR Chemistry, but only if they finished in 2026":

1. courseId equals "bio101" (filter 1)
2. courseId equals "chem101" (filter 2)
3. completedAt after 2026-01-01 (filter 3)

Filter logic: (1 OR 2) AND 3

---

Filtering by related columns

You can filter by related-entity columns the same way you project them. On the Enrollments entity:

• Course contains "React" — finds enrollments in any course whose title contains "React"
• Category equals "Science" — filters by course category name
• Course Level equals "BEGINNER" — filters by the related Course's level enum

The engine translates these to nested Prisma where clauses ({ course: { title: { contains: "React" } } }) so the database does the join efficiently. No post-filtering in memory.

Student-related columns sourced from OptiCRM (Student Name, Student Email, Student Code) are projection-only — they show up in the preview but can't be used in filters. Use Student ID (the raw FK) if you need to filter to a specific student.

---

Saving

Click Save in the top-right. In a new report, a dialog asks for name, description, and visibility (private or institution). In an existing report, clicking Save overwrites without a dialog — use Save As if you want to branch off into a new copy.

Saving lands you on the viewer page at /analytics/reports/[id], where you can Export, set up Schedules, and configure Alerts. See the Saved Reports guide for what the viewer does.

---

Tips

• Start broad, filter down. Add the 5 most important fields first, look at the data, then add filters to narrow. Iterating on filters is faster than iterating on field selection.
• Use the drag handles. The column order in the Outline tab is the order the exported file will use. Drag the most important column to the top.
• Check the cache. If a number looks wrong, hit Preview. If it still looks wrong, check if the meta line says cached — the cache is invalidated automatically on writes, but Redis can lag by up to 60 seconds for live queries.
• Preview before saving. The save dialog isn't the place to discover a bad query. The live preview is running against real data the whole time you're building; trust it.
• Describe the use case. The description field is where future-you reads it and remembers why this report exists. "Used in the Monday staff meeting" beats "Monthly report".

---

Troubleshooting

"Aggregation 'X' requires a field for fn=sum" — you added a Sum/Avg/Min/Max without picking a field. Switch it to count or add a numeric field.

"Field 'Y' is a related-entity column and can't be used in filters yet" — you're trying to filter by a resolver field (like studentName). Use the underlying FK field (studentId) instead.

"Operator X is not supported on field Y" — the operator doesn't apply to that field's type (e.g. contains on a boolean). Change one or the other.

"Time granularity 'day' requires a date field" — you tried to group by day on a non-date field. Pick a date field or change the granularity to field.

Preview shows nothing but no error — filters are probably too restrictive. Remove them one at a time to find the one that eliminates every row.

Preview shows a cached label and the data looks stale — a mutation just happened that should have invalidated the cache but Redis may be lagging. Click Preview explicitly to force a re-run; that should bump through to the live path.

countDistinct warning — the engine fell back to a non-null count because the query path can't do a true SQL COUNT(DISTINCT ...). The number is still correct for your filter set. If you need exact distinct counts, group by a time granularity — the time-bucketed path handles DISTINCT natively.