# Finance Analytics Dashboards — Developer & Rollout Guide

Audience: a developer extending this feature (new tile, new module) or rolling the existing
Finance Overview Dashboard out to a client beyond GB5DEMO. For the end-user-facing "how do I use
it" doc, see `FinanceDashboards-User-Guide.md`. For the detailed, gotcha-by-gotcha build recipe
this doc summarizes, see `PortletDashboardFramework/PortletDashboardFramework_ModuleRecipe.md` —
read that one before building anything new; this doc is the map, that one is the terrain.

## 1. Architecture — the pieces and how they connect

Everything here sits on GB5's existing Portlet → Page → Dashboard framework
(`PortletDashboardFramework_TechnicalGuide.md`). Finance didn't change that framework — it's the
first real tenant of it at this scale, and found/fixed a few gaps along the way (below).

### 1.1 Three ways a portlet can get its data — pick correctly, don't assume

| Path | When it applies | What it gets you |
|---|---|---|
| **"Report"-type portlet** (`MPORTLETTYPE` = `-1899999981`, code `REPORT`) | Any existing report endpoint, especially one returning a **composite/multi-section** object (not a flat array) | Full `GbreportviewerComponent` embedded in the tile — grid, export (PDF/Excel/CSV), print, mail, schedule, drilldown, all for free. **This is what all 8 Finance tiles use today.** |
| **BI Catalog / ApiService bridge** (`MBICATALOG.DATASETKIND` = ApiService) | A **flat, non-pivot** report view (`MREPORTVIEW.ISDATAPIVOT = 0`), served by **BusinessHost specifically** | A `gb5-widget` chart/KPI-card portlet bound to a generic dataset. Turned out **unusable for every Finance report so far** — see the two hard blockers below — but valid for a genuinely flat report actually served by BusinessHost. |
| **gb5-widget chart/KPI/Tab-Group portlet** (Phase 0 infra) | Built and proven with synthetic data; not yet exercised against a real Finance dataset, since every real report needed the Report-portlet path instead | Compact chart/KPI visual instead of a full grid — the right choice once a report's shape is simple enough (a handful of numbers, a trend line) |

**Two hard blockers found on the BI Catalog bridge — check before betting a report on it:**
1. **Same-host only.** `ApiDatasetDAL.FetchRowsAsync` resolves its base URL from a single global
   `MDATASOURCE` row (`DATASOURCECODE='GB5_SELF_HOST'`) hardcoded to BusinessHost's own address. A
   report served by any other host (PlatformHost, HRFinanceHost, FrameworkSL, EngagementHost) 404s
   at query time no matter how correctly it's wired.
2. **Data-pivot views are rejected outright**, before URL resolution even runs — a hard
   `BICatalogBLL.CreateFromReportView` check on `MREPORTVIEW.ISDATAPIVOT`.

Given both, **default to the "Report"-type portlet path** unless you've explicitly confirmed a
report view is (a) flat and (b) served by BusinessHost.

### 1.2 The wiring chain a "Report"-type portlet needs, end to end

```
MWEBSERVICE  (the route + SECONDURITEMPLATE — the actual URL called)
     ↑ WEBSERVICEID
MREPORT      (wraps one webservice as "a report")
     ↑ REPORTID
MMENU        (MENUTYPE=2 "Reports"; this is what the FE actually resolves to render the tile)
     ↑ MENUID
MPORTLET     (PORTLETTYPEID=-1899999981 "Report"; MENUID points up to the MMENU above)
     ↑ PORTLETID
MROLEVSPORTLET  (places the portlet on a specific Dashboard's Page, at PortletRow/Column, for a Role)

+ MROLEVSMENU   (separately: grants the MMENU itself to a Role — see 1.4, easy to miss)
```

Every arrow above is a real FK your migration/seed script must satisfy in order — `MREPORT` before
`MMENU`, `MMENU` before `MPORTLET`, etc.

**A dashboard also needs, independent of any one tile:**
- `MDASHBOARD` / `MPAGE` / `MDASHBOARDPAGE` — the dashboard and its page(s)
- `MROLEVSDASHBOARD` / `MUSERVSDASHBOARD` — who can see the *dashboard*, distinct from who can see
  each *portlet's menu* (both are checked; granting one without the other leaves it half-visible)
- **A left-nav `MMENU` entry with `MENUTYPE=5` and `PAGEID` set to the dashboard's page.** This is
  the *only* click-to-open path in the FE today — confirmed by tracing `GbMenuTreeComponent` and
  `MyDashboardsDialogComponent` (the latter only pins/reorders dashboards already reachable
  elsewhere, it does not launch them). **Miss this and the dashboard is completely unreachable**,
  no matter how correct everything else is — this was found and fixed only after everything else
  had already been built and "verified."

### 1.3 Endpoint contract — non-negotiable for anything in this chain

Any endpoint a "Report"-type portlet points at, or a drilldown targets, **must** accept
`[FromBody] ReportCallingDTO` (`{ CriteriaDTO, MenuId }`) as its entire POST body. The generic
FE call chain (`GetMenuReport`/`picklist` for the dashboard-embed path; `dynamicdrillDowntoReport`
for the drilldown-navigate path) always sends exactly that envelope, unconditionally.

An older-style endpoint — `[FromBody] CriteriaDTO` directly, or a plain `Get()` with query params —
will **silently bind to an empty/default instance** against that envelope (not an error — this is
the single most expensive-to-diagnose failure mode in this whole feature). Fix: a thin wrapper
endpoint that accepts `ReportCallingDTO`, unpacks `req.ReportCallingDTO.CriteriaDTO`, and calls the
existing BLL method underneath. Two real examples already in the repo to copy:
- `GB5Solution/Compliance/ComplianceSL/Endpoints/ComplianceAnalytics/PostFilingStatusSummaryForDashboard.cs`
  (wraps a plain-`Get()` endpoint with query params)
- `GB5Solution/Accounts/AccountsSL/EndPoints/Voucher/LoadBRSForDrilldown.cs`
  (wraps a `[FromBody] CriteriaDTO`-direct endpoint)

### 1.4 The rights gap that silently hides everything — check this first when something's blank

`MenuBLL.GetReportCriteriaForMenu` (the SQL behind every menu resolution — dashboard-embed *and*
drilldown-navigate both call it) filters `rolevsmenu.Allow like '%0%'` for the requesting role. A
menu with **zero `MROLEVSMENU` rows for the viewing role returns an empty array — not an error,
just nothing.** A direct `curl` against the report endpoint can return perfect data while the
actual dashboard tile renders blank, because the tile never even gets that far — the menu
resolution step short-circuits first. **Verifying a report endpoint directly is not proof the
portlet renders.** Every new `MMENU` row this feature creates (portlet target, drilldown target,
left-nav dashboard entry) needs its own `MROLEVSMENU` row for every role that should see it — copy
the `ALLOW` bitmask from an existing working menu's grant, don't guess.

### 1.5 Drilldown — reuse `MDRILLDOWN`/`MDRILLDOWNDETAIL`, it already exists

`MDRILLDOWN` (`FROMTYPE`/`FROMMENUID` → `TOTYPE`/`TOMENUID`, plus `DRILLTYPE`) +
`MDRILLDOWNDETAIL` (per-field source→target criteria mapping, `SOURCEFIELD`/`ASSIGNTYPE`) is a
real, already-wired, admin-configurable registry — resolved server-side into a `DrillDownArray` on
every menu response, consumed generically by the FE's `navigateDrillDown` (keyed off
`target.ToMenuId`/`ToMenuType`, no per-report code). **Do not design a new drilldown table.** A
target with no real per-row filter to carry is a legitimate pattern (blank `SOURCEFIELD`,
navigation-only) — several production drilldowns already work exactly this way.

## 2. Extending — add a new tile, or a whole new functional area

Full detail (including every gotcha above, worked examples, and the specific IDs/patterns used) is
in `PortletDashboardFramework_ModuleRecipe.md`. Short version:

1. **Inventory existing reports first.** Most functional areas already have the report logic —
   the gap is almost always presentation wiring, not new business logic. Build a gap table (module
   → backend exists? → gap) before writing anything.
2. **Decide the rendering path** per §1.1 — default to "Report"-type portlet; only reach for the
   BI Catalog bridge after confirming flat + BusinessHost-served.
3. **Fix the endpoint contract** if needed (§1.3) — a thin wrapper, not a rewrite.
4. **Wire the chain** (§1.2) — `MWEBSERVICE → MREPORT → MMENU → MPORTLET → MROLEVSPORTLET`, plus
   `MROLEVSMENU` for every role (§1.4) — this is the step most likely to be silently skipped.
5. **Place it** on an existing dashboard's page (`MROLEVSPORTLET`), or stand up a new
   `MDASHBOARD`/`MPAGE`/`MDASHBOARDPAGE` + `MROLEVSDASHBOARD` + a left-nav `MMENU` entry if it's a
   genuinely new dashboard.
6. **Add drilldown** (§1.5) only where a real, already-existing detail view exists to drill into —
   don't build a new detail screen just to backfill a target.
7. **Verify** per §4 below before calling it done.

## 3. Rolling out to another client — the honest current state

**Two different layers exist here, deployed two different ways — know which is which before
assuming a rollout is "just run the migration."**

### 3.1 Framework-layer changes — already migration-backed, roll out normally

Built during Phase 0, these are real, versioned `.sql` files under `DB/Migrations/`, both dialects,
following this repo's normal migration convention:
- Tab-Group portlet type (`20260825_TabGroup_Portlet_Type_{SqlServer,Postgres}.sql`)
- KPI portlet types/columns (`20260808_KPI_Portlet_Types_And_Columns_*.sql`)
- OU-Group / BizDimension filter criteria (`20260825_FinanceDashboards_{OUGroup,BizDimension}_Filter_*.sql`)
- `DashboardHub` SignalR infrastructure (code, not schema — deploys with `FrameworkSL`)

These roll out to a new client the same way any other GB5 migration does — via the normal
migration deploy path, or through SqlWorkbench's dev-branch → release-package flow per this repo's
`SqlWorkbench — Dev vs. Live Database Practice` convention (`GB5Solution/CLAUDE.md`) for a
controlled, tracked rollout instead of an ad hoc run.

### 3.2 The actual Finance Overview Dashboard's 8 tiles — now migration-backed

Every `MWEBSERVICE`/`MREPORT`/`MMENU`/`MPORTLET`/`MROLEVSPORTLET`/`MROLEVSMENU`/`MDRILLDOWN`/
`MDRILLDOWNDETAIL`/`MROLEVSDASHBOARD` row for the 8 live tiles was originally created as hand-run,
ad hoc SQL directly against GB5DEMO, for iteration speed while the wiring pattern itself was still
being worked out. That gap is now closed:
`DB/Migrations/20260829_FinanceOverviewDashboard_Full_{SqlServer,Postgres}.sql` converts all of it
into an idempotent migration, using this repo's own `MAUTONUMBER`-reservation pattern
(`Docs/AutoNumber-Seed-Pattern.md`) for every ID — **never** GB5DEMO's own literal ID values, which
were only ever valid on that one database.

That migration's structure, worth knowing before extending it:
- **Dashboard + Page + left-nav menu entry, Bank Reconciliation Dashboard + its drilldown target,
  and both Compliance tiles are created outright** (guarded by natural-key existence checks) — these
  didn't exist anywhere before this project.
- **Ratio Analysis / P&L / Budget vs Actual / Cash Flow / Receivable are *discovered* by their
  report's route or webservice name pattern, not assumed present.** If a target client doesn't have
  one of these reports migrated yet, that specific tile is skipped with a `PRINT`/`RAISE NOTICE`
  warning — the rest of the script still runs. Check for these warnings after every run.
- The SQL Server version was smoke-tested against GB5DEMO (safe, since GB5DEMO already has every
  natural key the script guards on): it converged to a clean, fully idempotent zero-error run, after
  a few retries needed only because GB5DEMO's own `MAUTONUMBER(ROLEVSMENU)` counter was itself stale
  from earlier ad hoc work in this project — a genuinely fresh target with a properly-maintained
  counter should hit none of that. The retries also surfaded a real, previously-unnoticed gap: two
  of the legacy P&L/Budget/Cash Flow menus were missing a rights grant for one of the two target
  roles, which the migration correctly detected and fixed.
- The Postgres version is a faithful line-for-line mirror of the same logic but has **not** been
  execution-tested against a real PostgreSQL instance — dry-run it before trusting it on a real
  client.
- **Also run end-to-end against a genuinely different real client database (`elkayem`) — this is
  the test that actually matters, more than the GB5DEMO smoke test.** It surfaced something the
  §3.1 "confirm framework migrations are applied" check understates: `elkayem` wasn't just missing
  one migration, it was missing the *entire chain* — `MDASHBOARD`/`MDASHBOARDPAGE`/
  `MROLEVSDASHBOARD` didn't exist as tables at all (not just unseeded), and applying that revealed
  further missing pieces one at a time (filter-tier columns, KPI portlet columns,
  `MPORTLET.FILECONTENT`, then `MPORTLET.BIVIEWID`) — each fixed migration exposing the next gap,
  because this client had simply never received several months of this framework's evolution.
  **Don't assume "the framework migrations" means one file — walk the full chronological list in
  `DB/Migrations/` for `MDashboard`/`Portlet`/`KPI_Portlet`/`FileContent`/`FilterTiers` and check
  each one's prerequisites individually exist before assuming a target is ready.**
- `MPORTLET.BIVIEWID` specifically depends on the full BI Catalog/Analytics schema
  (`MBICATALOG`/`MBIVIEW`, from `20260806_BIView_Schema_*`) — a separate, much larger subsystem.
  Since this migration never populates that column (every Finance tile is a "Report"-type portlet,
  always `NULL` there), pulling in the whole Analytics schema just to satisfy one unused column
  isn't proportionate — add the bare nullable column instead (no FK) if a target is missing it and
  doesn't otherwise need the Analytics engine. Don't reach for the full BI Catalog migration as a
  reflex fix for this one column.
- One existing older migration (`20260825_FinanceDashboards_OUGroup_Filter`) turned out to contain
  its own hardcoded GB5DEMO-specific literal IDs (its `UPDATE` statements silently affected 0 rows
  on `elkayem`) — a real example of exactly the mistake this doc's own migration works to avoid.
  Worth revisiting that file with the same AutoNumber-Seed-Pattern discipline at some point.

**Rollout steps for a new client:**
1. Confirm the §3.1 framework migrations are already applied on the target — a quick existence
   check (`SELECT 1 FROM MPORTLETTYPE WHERE PORTLETTYPEID = -1399999764` for Tab-Group, etc.) before
   assuming so.
2. Set `@TargetRoleId1`/`@TargetRoleId2` (SQL Server) or `v_role_1`/`v_role_2` (Postgres) at the top
   of the migration to the role(s) that should see this dashboard on the target — GB5DEMO's values
   are placeholders, not universal constants.
3. Run the migration on a test/dev instance of the target first (or against `uscdevsys` itself,
   depending on your team's SqlWorkbench branch setup), and read every `PRINT`/`RAISE NOTICE` line
   in the output — a "SKIPPED" line means that tile's underlying report isn't migrated to this
   client yet and needs handling before the dashboard is complete there.
4. Confirm the deployed backend already includes the two-and-only wrapper endpoints
   (`PostFilingStatusSummaryForDashboard`, `PostActiveAlertsForDashboard`,
   `LoadBRSForDrilldown`) and the new BRS Dashboard query (`AccountBRSDashboardReport`) — these are
   real code, already committed to `dev`, and deploy the normal way (build → deploy → restart →
   `DEPLOY_LOG.md` entry per `GB5Solution/CLAUDE.md`'s deployment convention). No client-specific
   variation needed here — same binary for every client.
5. Apply the migration to the target client's DB via SqlWorkbench's dev-branch → release
   path, not a direct ad hoc run — this is a live client database, not a shared demo box.
6. Verify per §4 below, on that client's own data — don't assume GB5DEMO's verification transfers;
   different clients will have different OUs, different real report data (or none), different role
   structures.

## 4. Verification checklist — every time, on every environment

- [ ] `dotnet build` clean on every touched project.
- [ ] Live HTTP call to every new/changed endpoint, directly — proves the endpoint itself works,
      **but this alone is not sufficient** (see §1.4).
- [ ] `GET /Menu/ReportMenuDetailsForMenu?MenuId=<id>&UserId=<real-user>&IsFromScreen=0` for every
      new menu (portlet target, drilldown target, dashboard left-nav entry) — confirms the row
      resolves at all (proves `MROLEVSMENU` rights are correct) and, for a source menu, that
      `DrillDownArray` contains the expected `ToMenuId` if a drilldown was wired.
- [ ] `GET /Portlet/GetPortlet?PortletId=<id>` — confirms the portlet row resolves and chains
      correctly through to the right webservice URL.
- [ ] Dashboard still editable afterward: add/remove/reorder portlets through the existing admin
      screens on the actual dashboard — proves composability wasn't lost.
- [ ] A real browser click-through: open the dashboard from the left nav, confirm every tile
      renders real data, click the drilldown, confirm Export/Print work. **This step was not done
      for the current GB5DEMO build** — every check above was done at the API level; treat a real
      click-through as the final gate before calling any rollout complete, on GB5DEMO or elsewhere.

## 5. Known gaps, carried forward honestly

- Currency-aware Consolidated View: deferred — GB5DEMO's real OUs are single-currency, so there's
  nothing to build/test against yet. Revisit when a genuinely multi-currency client needs it.
- Only one drilldown is wired (BRS → pending instrument list) — the mechanism is proven; extending
  it to P&L→ledger, Receivable ageing→bill detail, etc. is the same pattern, not new design work.
- No self-service "assign this dashboard to a role" screen — done via direct `MROLEVSDASHBOARD`
  inserts today, same as everything else in this doc.
- The bigger "package multiple dashboards/reports into a scheduled, shareable review bundle"
  concept is explicitly out of scope here — a separate, larger initiative for its own plan.