# Ad-Hoc Report Builder — Feature & UI Specification

**Date:** 2026-05-27
**Ticket:** BES_P2 / TG-644 (POC → productionised UI)
**Status:** Design approved, ready for UI design
**Audience:** This document is written to be fed into a UI design tool. It describes *what the feature does* and *how the screens behave functionally*. It deliberately does **not** specify colours, typography, spacing, or any CSS — visual styling will come from the existing design system.

---

## 1. Purpose

The Ad-Hoc Report Builder is a self-service reporting tool. A user builds a query against a business subject, runs it to get a summarized and detailed result set, exports results to Excel, and can **save queries as named tags** so a report can be re-run later with a single click — no need to re-enter parameters.

This feature already exists as a working proof-of-concept (POC, branch `BES_P2/TG-644-POC`) with a complete backend and a rough Vue/Vuex UI. A separate, prettier Figma design was produced earlier from last year's requirements, but it **hardcodes the report's form fields** and does not cover the two new requirements below. The goal now is a new UI that:

1. Keeps the POC's flexible, **dynamic** field behaviour (do **not** hardcode form fields like the Figma did).
2. Adds **saved queries as tags**.
3. Adds **ranking** to the builder — sort + a "top N" limit, plus SUM totals — so ranked, leaderboard-style reports can be built. (This is **new capability beyond the POC**; the POC only counts per group.)

---

## 2. Background — what the POC already does

The POC backend is reusable and should be treated as the foundation. Summary of its behaviour:

- **Subjects to report on (internally "model types"):** `application`, `enquiry`, `employer`. Each exposes a different set of available fields.
- **Metadata-driven fields:** The list of available fields, their data types, labels, and dropdown option values are loaded from the server (`ad_hoc_field_config` table + code tables). The UI is populated from this metadata — nothing about the fields is hardcoded in the frontend.
- **Query definition** a user builds consists of: selected subject, filter conditions (WHERE), group-by fields (summarize), and display fields (the columns shown in detail results).
- **Running a query** produces three result sets on the backend: a total record count, an optional grouped/summarized set (only when group-by is used), and a detail record set (capped at a row limit).
- **Results UI** shows a summary table and a detail table, where selecting summary rows cross-filters the detail rows.
- **Excel export** produces a password-protected ZIP containing the summary and detail data.
- **No persistence of queries exists today** — query names are UI-only and are never saved. This is the gap the "saved tags" requirement fills.

---

## 3. Users & access

- Authenticated BES users who have access to the report page.
- **Saved query tags are private to the user who created them.** A user never sees another user's tags. There is no sharing or public/global tag concept in this scope.

---

## 4. Information architecture (functional regions)

The page has three functional regions. The design tool decides layout/arrangement; the regions and their behaviour are fixed.

### 4.1 Saved Queries (tag library)

- Shows the current user's list of saved query tags. Each tag displays its **name**.
- **Click a tag → the builder loads that query's full definition AND the query runs automatically**, showing results immediately. This is the core "quick query, no re-input" flow.
- Per-tag actions available to the user:
  - **Rename** the tag.
  - **Delete** the tag (permanent).
  - **Update/overwrite** — re-save the current builder state onto an existing tag.
- The list is scrollable when long. No manual reorder, no favourites/pinning in this scope.
- Empty state: when the user has no saved tags yet, show a friendly empty state inviting them to build a query and save it.

### 4.2 Query Builder (one query at a time)

The builder works on **a single query definition at a time**. (The POC's ability to stack up to 5 queries on one screen is retired; the tag library now serves the "multiple reports" role.)

**a) Report subject selector**
- Lets the user choose what they want to report on. Present in **plain language** — e.g. a prompt like "What do you want to report on?" with friendly choices such as **Applications**, **Enquiries**, **Employers**.
- **Do NOT use the term "model type" or any technical/internal name in the UI** — users do not know what that means.
- Changing the subject resets the rest of the query (filters, group-by, display fields), because the available fields differ per subject.

**b) Filter Conditions ("narrow down the records")**
- Users **dynamically add and remove** filter conditions. This is a key requirement — the set of filters is **not** a fixed/hardcoded form.
- Adding a condition: the user picks a field from the available fields for the chosen subject. The field's data type then drives the appropriate input control and operators:
  - **Date** → date range (start / end) with comparison operators (`>`, `<`, `>=`, `<=`, `=`, `!=`).
  - **Option / coded value** → multi-select from the field's allowed values, with `IN` / `NOT IN`.
  - **Numeric** → numeric input with comparison operators.
  - **Text** → "contains" style matching (`LIKE`).
  - **Boolean** → yes/no toggle.
- Each condition can be removed individually.

**c) Summarize / Group-By ("group the results")**
- Users **dynamically add and remove** group-by fields. Also **not** a hardcoded form.
- Behaviour by field type:
  - **Date** → choose a granularity: daily / weekly / monthly.
  - **Numeric** → group into value buckets (ranges).
  - **Option / text** → group by the value directly.
- **Any field can be grouped** — the group-by picker offers the same fields as the filter picker (there is no separate "group-able" flag at runtime). The metadata supplies a `defaultGroup` hint that pre-fills the grouping parameter — e.g. `monthly` for date fields, or a default set of numeric buckets.
- **Aggregation:** each summary row shows a **record count** by default. For numeric fields the user can additionally choose a **total (SUM)** — e.g. total subsidy amount per group. COUNT and SUM are the supported aggregates.
- When at least one group-by field is set, results include the summary table; when none are set, results show detail records only.

**d) Display Fields ("choose the columns to show")**
- Multi-select of which fields appear as columns in the detail results, chosen from the available fields for the subject. Searchable; supports select-all / clear-all.

**e) Sort & ranking ("order and limit the results")**
- **Sort:** order the summary by a chosen column — e.g. by record count or by a SUM total — ascending or descending. Detail results may also be sortable by a chosen display column.
- **Top-N limit:** optionally restrict the summary to the **top N rows** (e.g. top 10, top 20). This turns a grouped report into a ranked "top N" report. It is separate from the 10,000-row safety cap on detail records.
- Sort + Top-N are **new** capabilities beyond the POC (the POC only counts per group, with no ordering or limit); they let users build ranked reports such as the employers with the most applications in a period.

**f) Builder actions**
- **Run** — execute the query and show results. Disabled (or validated with a clear message) when the query is not runnable (e.g. no subject chosen / nothing to display).
- **Save** — save the current builder state as a tag. **A name is mandatory** — the user is prompted for a name and cannot save without one. Saving may create a new tag or overwrite an existing one (see §5).
- **Clear / reset** — start a fresh query.

### 4.3 Results

Appears after a query runs.

- **Summary table** — shown only when group-by fields are set. Grouped rows with a record count (and optional SUM total) per group. Respects the chosen **sort** and **top-N** limit — e.g. a top-10 report shows exactly 10 ranked rows, with a sort indicator on the ranked column. Selecting one or more summary rows **cross-filters** the detail table to those groups.
- **Detail records table** — the filtered detail rows, capped at the row limit (see §7). Shows a **row-count indicator** (e.g. "showing X of Y records").
- **Export to Excel** — lives in the Results region. Produces a password-protected ZIP containing summary and detail sheets (existing POC behaviour). The user is prompted for the export password.
- Empty state: when a run returns no records, show a clear "no results" state.

---

## 5. Requirement #1 — Saved queries as tags

- **What a tag stores:** a single complete query definition — the report subject, all filter conditions, all group-by fields, and the chosen display fields — plus a user-given name.
- **Frozen snapshot:** a tag stores the **exact parameter values**, including **exact date ranges**. Clicking a tag weeks later re-runs with the same saved dates (no relative/rolling dates in this scope).
- **Private per user.**
- **Mandatory name on save.**
- **Click → load + auto-run** (the defining behaviour).
- **Management:** rename, delete, and overwrite (update an existing tag with the current builder state).
- **Duplicate names:** allowed, but when a user saves a name that already exists, warn them and let them choose to **overwrite the existing tag** or **keep both**.

**New persistent entity (conceptual):**

| Field | Meaning |
|-|-|
| id | Unique identifier |
| userId | Owner (private to this user) |
| name | User-given tag name (required) |
| subject / modelType | Which subject the query targets |
| definition | The full query definition (filters, group-by, display fields, sort, top-N) |
| createdAt / updatedAt | Timestamps |

---

## 6. Requirement #2 — Dynamic fields (no hardcoded forms)

The filter-conditions section, the group-by section, **and** the display-fields section are all populated from server **metadata** and let the user **add fields dynamically**. The new UI must preserve this — it must **not** revert to the fixed/hardcoded form fields shown in the earlier Figma design. The available fields are always driven by the selected report subject and the server metadata, so new fields can be added on the backend (config/metadata) without UI changes.

---

## 7. Data model & backend (context for implementation)

> The frontend terms above map to these internal names. The UI must keep these technical names hidden.

**Query definition (per query):**
```
{
  modelType: "application" | "enquiry" | "employer",   // UI: "report subject"
  queryName: string,
  whereClauses: [ { key, type, operation, startDate?, endDate?, option?[], value? } ],
  groupBy:     [ { key, type, parameter?, aggregate? } ], // aggregate: "count" (default) | "sum"; parameter: date granularity or numeric buckets
  selectClause: [ field, ... ],                           // display columns
  orderBy?:    { key, direction: "asc" | "desc" },        // NEW — ranking / sort
  topN?:       number                                     // NEW — limit summary to top N rows (e.g. 5)
}
```

**Existing backend endpoints (reused):**
- `GET_AD_HOC_REPORT_METADATA` → returns available fields (`meta`) and dropdown option values (`options`).
- `GET_AD_HOC_REPORT` → accepts the query definition, returns per query: count result, optional grouped result, and detail records.

**New backend work in `SQLQueryGenerator`:** `ORDER BY`, `LIMIT N` (top-N), and `SUM` aggregation — none of these exist in the POC generator and must be added to support ranked reports.

**New backend endpoints (to be added for saved tags):**
- List the current user's saved tags.
- Create a tag.
- Rename a tag.
- Update/overwrite a tag.
- Delete a tag.

**Limits:**
- Detail results capped at **10,000 rows** (carried over from the POC).
- The POC's "max 5 concurrent queries" limit is **dropped** — no longer relevant with the single-query builder.

---

## 8. Key user flows

1. **Build & run:** choose subject → add filters → (optionally) add group-by → pick display fields → Run → view summary + detail → export to Excel.
2. **Save a report:** build a query → Save → enter a name (required) → tag appears in the library.
3. **Quick re-run:** click a tag in the library → query loads and **runs automatically** → results shown.
4. **Manage tags:** rename, delete, or overwrite an existing tag with the current builder state.

---

## 9. States & edge cases

- **Invalid/empty query:** Run is disabled or shows a clear validation message (e.g. no subject selected, or nothing to display).
- **No results:** show a clear empty state in the Results region.
- **Row limit reached:** indicate that results are capped at 10,000 rows.
- **Save with blank name:** blocked; prompt for a name.
- **Duplicate tag name:** warn and offer overwrite-or-keep-both.
- **Metadata load failure / query error:** show a clear error message; don't leave the user on a blank screen.
- **Long tag list:** scrollable library.
- **Subject change with existing work:** changing subject resets the query (since fields differ); warn before discarding a populated builder.

---

## 10. Out of scope / non-goals

So the design does not over-build:

- **No visual styling decisions** — colours, typography, spacing, and CSS come from the existing design system, not this spec.
- **No relative/rolling date ranges** (tags use exact saved dates).
- **No shared/public/global tags** (private per user only).
- **No reorder / favourite / pin** of tags.
- **No multiple queries displayed on one screen** (single-query builder + tag library replaces it).
- **No server-side pagination** of detail results in this scope. (Sorting and top-N ranking ARE in scope — see §4.2e.)
- **No reproduction of stored-procedure reports** (e.g. the existing Top 5 Employers Statistics Report). Those encode joins/dedup/business rules the ad-hoc builder is not meant to express; they remain separate canned reports.

---

## 11. Glossary (UI wording guidance)

| Internal term | What to call it in the UI |
|-|-|
| model type | "report subject" / "what do you want to report on?" (Applications, Enquiries, Employers) |
| where clause | "filter" / "condition" |
| group by | "summarize" / "group" |
| select clause | "columns to show" / "display fields" |
| saved query | "saved report" / "tag" |

---

## Appendix A — Field catalogue (live runtime metadata)

This is the **authoritative** field metadata as served at runtime by `AdHocReportManager.GetMetaData()` — fields from the `ad_hoc_field_config` table and option values from `codeTable` (queried live from the dev DB on 2026-05-28). It supersedes the static `AdHocReportConfig.json` file, which is an out-of-date snapshot (e.g. it left `appType` with no options). Use these values so the mockups show real field pickers, filter rows, and dropdowns.

Legend: **Type** drives the input control — date = range picker; option = multi-select dropdown; numeric = number + comparator; string = "contains" text; bool = yes/no. The "Internal field" column is for developers; the **label** is what the UI shows.

Note on grouping: at runtime the group-by picker offers the **same** fields as the filter picker (there is no separate "group-able" flag). Grouping behaviour is driven purely by field type — date → granularity, numeric → buckets, option/string → direct value.

### A.1 Applications

| Field (label shown to user) | Internal field | Type |
|-|-|-|
| Application No. | appNo | string |
| Application Status | appStatus | option |
| Application Type | appType | option |
| Application Received Date | submittedTime | date |
| Application Conclude Date | concludeDt | date |
| Commencement Date | comDate | date |
| Date of all required info/doc received | comDocDate | date |
| Termination Date | termDate | date |
| Termination Reason | termReason | option |
| Type of Payment | paymentType | option |
| Amount of Pre-transition | paymentAmtPre | numeric |
| Amount of Post-transition | paymentAmtPost | numeric |
| Net Amount of Post-transition | netPaymentAmtPost | numeric |
| Subsidy amount | subsidyAmt | numeric |
| Employment Size | emplSize | numeric |
| Name of Employee (English) | eeNameEng | string |
| Name of Employee (Chinese) | eeNameChi | string |
| Name of Employer (English) | erNameEng | string |
| Name of Employer (Chinese) | erNameChi | string |
| Employer No. | erNo | string |
| Employer Type | erType | option |
| Employer Status | erStatus | option |
| Trade | tradeType | option |

*(23 fields — the complete current set for Applications.)*

### A.2 Employers

| Field (label) | Internal field | Type |
|-|-|-|
| Employer No. | erNo | string |
| Employer Name (Eng) | erNameEng | string |
| Employer Name (Chi) | erNameChi | string |
| Employer Version | erVer | string |
| Employer Type | erType | option |
| Employer Status | erStatus | option |
| Trade | tradeType | option |
| Employment Size | emplSize | numeric |
| Submit Type | subType | option |
| Registered Number | regNo | string |
| Ordinance Governing the Registration | regOrdinance | option |
| Certificate of Incorporation/Registration No. | ubi | string |
| Licence No. | licenseNo | string |
| Employer Submitted Date | submittedTime | date |
| Created Time | createdTime | date |
| Updated Time | lastUpdatedTime | date |

### A.3 Enquiries

| Field (label) | Internal field | Type |
|-|-|-|
| Enquiry No. | enqNo | string |
| Enquiry Type | enqType | option |
| Status | status | option |
| Receipt Channel | receiptChannel | option |
| Other Receipt Channel | otherReceiptChannel | string |
| Enquiry Source | enqSource | option |
| Submit Type | subType | option |
| Other Submit Type | otherSubType | string |
| Name (Eng) | enqNameEng | string |
| Name (Chi) | enqNameChi | string |
| Enquiry Email | enqEmail | string |
| Enquiry Tel. No | enqTelNo | string |
| Enquiry Address | enqAddress | string |
| Enquiry Detail | enqDetail | string |
| Special Request | specialReq | string |
| Is Anonymous | isAnonymous | bool |
| Case Number | caseNo | string |
| Handling Officer | handlingOfficer | string |
| Approving Officer | approvingOfficer | string |
| Endorsing Officer | endorsingOfficer | string |
| Case Assigned Officer | caseAssignOfficer | string |
| Complicated Case Handling Officer | complicatedCaseHandlingOfficer | string |
| Complicated Case Approving Officer | complicatedCaseApprovingOfficer | string |
| Created By | createdBy | string |
| Last Updated By | lastUpdatedBy | string |
| Created Time | createdTime | date |
| Last Updated Time | lastUpdatedTime | date |
| Received Time | receivedTime | date |
| Final Deadline | finalDeadline | date |
| First Reply Date | firstReplyDate | date |
| Final Reply Date | finalReplyDate | date |
| Final Reply Issued Date | finalReplyIssuedDate | date |
| Close Date | closedDate | date |
| Close Case Reason | closeCaseReason | string |
| Close Case Remark | closeCaseRemark | string |
| Verbal Or Voice Flag | verbalOrVoiceFlag | string |

### A.4 Coded value sets (complete, for option-type dropdowns)

These are the **full** option lists from `codeTable`, in display order. Use them verbatim when mocking up the multi-select dropdowns.

- **Application Status** (`appStatus`, 21): Draft · Submitted · Dual Input 1 · Dual Input 2 · Vetting · Approving · Advising · QA In Progress · Pending Clarification · Approve In Principle · Reject In Principle · Approved · Rejected · Deemed Withdrawn · Withdrawn · Deleted · Temp · LD Approving · Result confirmed · Case Finished · Case Unfinished
- **Application Type** (`appType`, 9): Fresh · Review · Appeal · Internal Review · Internal Review (Review) · Internal Review (Appeal) · Internal Audit · Internal Audit (Review) · Internal Audit (Appeal)
- **Employer Type** (`erType`, 5): Limited Company / Body Corporate / Limited Partnership · Sole Proprietor / General Partnership · Individual · Employer with Exemption from Registration but required to obtain a Licence for its business · Registered Organisation
- **Employer Status** (`erStatus`, 10): Temp · Unvetted · Delete · Vetting · Vetted · Inactive · Dual Input · Pending Clarification · Advicing · Rejected
- **Trade** (`tradeType`, 21): Arts, entertainment and recreation · Catering · Construction · Education services · Finance · Health care services · Hotels and accommodation activities · Import and export trades · Information and communications · Insurance · Manufacturing · Professional services, administrative and other business support services · Real estate · Retail · Social work activities · Storage · Transportation, postal and courier services · Wholesale · Work activities within domestic households · Other personal services · Others not elsewhere classified
- **Type of Payment** (`paymentType`, 2): Serverance Payment · Long Service Payment
- **Termination Reason** (`termReason`, 9): Redundancy · Dismissal (for reason other than redundancy) · Employee resigned at the age of 65 or above · Fixed-term contract expired · Employee retired at the age specified in employment contract · Employee died during employment · Employee terminated contract of employment without notice (per the Employment Ordinance) · Employee terminated his/her contract on the ground of being certified permanently unfit · Other reasons
- **Ordinance Governing the Registration** (`ordinance`, 5, Employers): Society (Societies Ordinance, Cap. 151) · School (Education Ordinance, Cap. 279) · Trade union (Trade Unions Ordinance, Cap. 332) · Owners' corporation (Building Management Ordinance, Cap. 344) · Others
- **Submit Type** (`subType`, 2): Online · Paper
- **Enquiry Type** (`enqType`, 6): SSA - General Enquiries · SSA - Application Enquiries · SSA - General Complaint · SSA - Application Complaint · Shortfall Enquiries · Shortfall Complaints
- **Enquiry Source / Receipt Channel** (`enqSource`, 15 — Receipt Channel reuses this set): SSA thematic website · Fax · Email · Post · Phone · Phone (voice mail) · In Person · 1823 (verbal) · 1823 (written) · EBSD of LD (verbal) · EBSD of LD (written) · Other division of LD · Other parties (please specify) · 1823 · EBSD of LD
- **Enquiry Status** (`enquiryStatus`, 14): Created · In progress · Pending for Approval · Interim Reply issued · Final Reply issued · Pending for Endorsement · Pending Endorsing Reply Creation · Pending for Endorse Reply Approval · Pending for Re-assignment · Pending Seeking Advice from LD · Pending for LD reply · LD replied · Closed · Edit Closed Enquiry
- **Enquiry Submit Type** (`enqSubType`, 53): a long list of enquiry/complaint sub-reasons, namespaced by enquiry type (General Enquiry, Application Enquiry, General Complaint, Application Complaint, Shortfall Enquiry, Shortfall Complaint). Examples: "Eligibility of employer", "Calculation rules of employer's subsidy", "Application result", "Progress of application", "Payment arrangement", "Refund of overpaid subsidy", "Complaint against PA handling officer", "Others (please specify)". Mock as a searchable dropdown.

---

## Appendix B — Worked example for mockups

A concrete scenario the design tool can render end-to-end.

### B.1 Example saved tags (library)

A user might have these tags saved:
- "Approved cases – this quarter"
- "Rejected applications by trade"
- "Catering employers – vetted"
- "Open complaints"

### B.2 Example populated query (subject = Applications)

- **Filters:**
  - Application Status **is any of** [Approved, Case Finished]
  - Application Received Date **between** 2026-01-01 and 2026-03-31
  - Trade **is any of** [Catering, Retail]
- **Summarize / Group-By:**
  - Application Received Date → **monthly**
  - Trade
- **Display fields:** Application No., Name of Employer (English), Application Status, Application Received Date, Net Amount of Post-transition

### B.3 Example results

**Summary table** (grouped by month + trade type; row selection filters the detail table):

| Received (month) | Trade | Records |
|-|-|-|
| 2026-01 | Catering | 128 |
| 2026-01 | Retail | 64 |
| 2026-02 | Catering | 141 |
| 2026-03 | Retail | 73 |

**Detail records table** (showing 406 of 406 records, capped at 10,000):

| Application No. | Name of Employer | Status | Received | Net (Post-transition) |
|-|-|-|-|-|
| APP-2026-000128 | ABC Catering Ltd | Approved | 2026-01-04 | 18,500 |
| APP-2026-000131 | Tasty Foods Co | Case Finished | 2026-01-06 | 9,200 |
| APP-2026-000147 | Star Retail Group | Approved | 2026-01-09 | 24,750 |

### B.4 Example query — subject = Employers

*(Summary counts and detail rows below are real data pulled from the dev database on 2026-05-27.)*

- **Filters:**
  - Employer Status **is** [Vetted]
  - Trade **is any of** [Education services, Construction, Catering]
- **Summarize / Group-By:**
  - Trade
  - Submit Type (Online / Paper)
- **Display fields:** Employer No., Employer Name (Eng), Employer Type, Employer Status, Employment Size, Trade

**Summary table:**

| Trade | Submit Type | Records |
|-|-|-|
| Education services | Online | 61 |
| Construction | Online | 50 |
| Catering | Online | 25 |

**Detail records table** (showing 136 of 136 records):

| Employer No. | Employer Name (Eng) | Type | Status | Size | Trade |
|-|-|-|-|-|-|
| ER-2605-0000126 | kwancloud | Limited Company | Vetted | 11 | Education services |
| ER-2603-0000079 | kwancloud | Limited Company | Vetted | 11 | Construction |
| ER-2603-0000083 | 2123123 | Sole Proprietor | Vetted | 23 | Catering |

### B.5 Example query — subject = Enquiries

*(Real data from the dev database on 2026-05-27.)*

- **Filters:**
  - Created Time **between** 2025-08-01 and 2026-05-31
  - Enquiry Type **is any of** [SSA - General Enquiries, Shortfall Enquiries]
- **Summarize / Group-By:**
  - Created Time → **monthly**
  - Enquiry Type
- **Display fields:** Enquiry No., Enquiry Type, Status, Handling Officer, Created Time, Close Date

**Summary table:**

| Created (month) | Enquiry Type | Records |
|-|-|-|
| 2025-08 | SSA - General Enquiries | 18 |
| 2025-09 | SSA - General Enquiries | 22 |
| 2026-03 | SSA - General Enquiries | 14 |
| 2026-03 | Shortfall Enquiries | 12 |
| 2026-05 | SSA - General Enquiries | 7 |

**Detail records table:**

| Enquiry No. | Type | Status | Handling Officer | Created | Close Date |
|-|-|-|-|-|-|
| EQ-GEN-260000040 | SSA - General Enquiries | Closed | — | 2026-05-14 | 2026-05-14 |
| EQ-GEN-260000037 | SSA - General Enquiries | In progress | HEI HEI | 2026-05-13 | — |
| EQ-GEN-260000036 | SSA - General Enquiries | In progress | CHAN HIN TAT | 2026-05-05 | — |

### B.6 Empty / edge states to mock

- **No saved tags yet** — empty library inviting the user to build and save a query.
- **No results** — query ran, zero records.
- **Row-limit notice** — "showing first 10,000 of N records".
- **Save dialog** — name field is required; show the duplicate-name warning ("overwrite / keep both").