> For the complete documentation index, see [llms.txt](https://api-docs.realfinity.io/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://api-docs.realfinity.io/mcp/tool-reference/reporting.md). # Reporting {% hint style="info" %} **Four of the six tools on this page are scoped to the calling user** — they return only the loans the authenticated user may see, with scope resolved from SQL on every request from the caller's roles, never from anything in the request itself. The two Data Warehouse-backed tools, `mlo_pipeline_report` and `warehouse_loan_milestone_report`, are the exception: they are **org-wide**, and are restricted to Admin, Processor, and ConciergeKey instead. Check each tool's **Backing store** and **Roles** before reasoning about what a caller can see. {% endhint %} **Scope by role — applies to the four caller-scoped tools only** | Role | Scope | | ------------------------- | --------------------------------------------------------------- | | Admin | All loans | | Processor | All loans in the user's company | | ConciergeKey | Loans of managed agents | | Agent / DualLicensedAgent | Loans they originate (self, assisted, ConciergeKey) or referred | | Pre-licensed | Referred loans only | Officer, processor, and referrer **name** fields require Admin, Processor, or ConciergeKey. An empty result is normally scoping, not an error. The reporting tools answer aggregate questions — counts, sums, funnel flow, stage distribution — rather than returning individual loans. For one row per loan, use `query_loans` or `search_loan_applications` on [Loan Lookup](/mcp/tool-reference/loan-lookup.md). **These six tools do not all share one backing store.** `report_loans` and `loan_pipeline_report` read the denormalized Cosmos loan snapshot; `report_loan_milestones` reads persisted LOS status history from SQL; `get_loan_reporting_metadata` reads nothing at all. All four are scoped to the calling user. The remaining two — `mlo_pipeline_report` and `warehouse_loan_milestone_report` — read the Data Warehouse and are **org-wide, not caller-scoped**. They appear on this page because they answer reporting questions, not because they share a data source. Check each tool's **Backing store** line before you reason about scope or freshness. Two more things worth knowing before you pick a tool: * **Current state vs. historical flow.** `report_loans` and `loan_pipeline_report` describe where loans sit **now**. Loans do not rest at a terminal status — `Loan Closed` and `Loan Funded` move on to `Loan Sold`, `Loan Purchased`, `Loan Archived` — so a current-status filter badly undercounts "how many loans closed last quarter". Use `report_loan_milestones`, which reads persisted status crossings, for any closed/funded/reached-stage-in-period question. * **Loan type is filterable here.** The Cosmos-backed reporting tools can filter on loan **type** (`filter.loanTypes`); the SQL-backed `search_loan_applications` cannot. Send type-based questions to `report_loans` or `query_loans`. ### report\_loans Aggregate loans into buckets by a `groupBy` dimension and a metric; each bucket returns count plus sum, average, min, and max. * **Backing store:** Cosmos loan snapshot * **Roles:** Admin, Processor, DualLicensedAgent, ConciergeKey, Agent **Prerequisites:** call `get_loan_reporting_metadata` first for valid dimensions and metrics. **Parameters** | Name | Type | Required | Description | | ------------- | ------ | -------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `filter` | object | No | Loan filter applied before aggregation. All fields optional; results are always scoped to loans you can access. See **The `filter` object** below. | | `groupBy` | enum | No | Dimension to group by: `None` (single total), `Status`, `LoanType`, `Purpose`, `LoanOfficer`, `ReferringAgent`, `Month`, `Quarter`, `Year`. `Month`/`Quarter`/`Year` bucket on `dateField`. Default `None`. | | `metric` | enum | No | Aggregate function for the primary metric: `Count`, `Sum`, `Average`, `Min`, `Max`. All five are returned per bucket regardless; this records which one you asked for. Default `Count`. | | `metricField` | enum | No | Numeric field for Sum/Average/Min/Max: `LoanAmount` (default), `LockedCompensation`, `EstimatedCompensation`. Loans without a numeric value are ignored. | | `dateField` | enum | No | Date used when `groupBy` is `Month`/`Quarter`/`Year`: `StatusDate` (default), `ClosingDate`, `Updated`. | The valid values for `groupBy`, `metric`, `metricField`, and `dateField` are exactly the `groupByDimensions`, `metricFunctions`, `metricFields`, and `dateFields` arrays returned by `get_loan_reporting_metadata` — see that tool's example response for the live lists. **Returns** A `LoanAggregationResponse`: the echoed `groupBy`/`metric`/`function`, `totalCount` across all buckets, `bucketCount`, a `truncated` flag set when a high-cardinality group-by was capped, and a `buckets` array. Each bucket carries `key` (status name, `2026-06`, `2026-Q2`, `2026`, an officer name, or `all`), an optional `keyId` GUID when grouping by `LoanOfficer` or `ReferringAgent`, `count`, and `sum`/`average`/`min`/`max`.
Example response ```json { "groupBy": "Status", "metric": "LoanAmount", "function": "Sum", "totalCount": 3, "bucketCount": 2, "truncated": false, "buckets": [ { "key": "Loan Processing", "keyId": null, "count": 2, "sum": 750000.00, "average": 375000.00, "min": 325000.00, "max": 425000.00 }, { "key": "Loan Funded", "keyId": null, "count": 1, "sum": 410000.00, "average": 410000.00, "min": 410000.00, "max": 410000.00 } ] } ```
**Notes** * This is a point-in-time aggregate over loans' **current** attributes. It cannot answer "how much did we close/fund in period X" — use `report_loan_milestones`. * `Month`/`Quarter`/`Year` grouping silently drops loans missing the chosen `dateField`. * A bucket omits `sum`/`average`/`min` when some of its loans lack a numeric value. * For one row per loan instead of buckets, use `query_loans`. #### The `filter` object `report_loans`, `loan_pipeline_report`, and `report_loan_milestones` all take the same optional `filter`. Every field is optional; omit a field to leave it unconstrained. The filter can only narrow what your role already permits. | Name | Type | Description | | ----------------------------------- | --------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `searchText` | string | Case-insensitive match against the loan number and borrower first name, last name, and email. Whitespace-separated tokens must all match. | | `statuses` | string\[] | Loan statuses to include (exact MLM status names, OR'd together). Call `get_loan_reporting_metadata` for the full list. Note `Loan Closed` and `Loan Clear To Close` are different statuses. Matches **current** status only. | | `loanTypes` | string\[] | Loan types to include (exact names), e.g. `Conventional`, `FHA`, `VA`, `USDA/Rural Housing`. | | `purposes` | string\[] | Loan purposes to include (exact names), e.g. `Purchase`, `Refinance`, `Refinance Cashout`. | | `statusDateFrom` / `statusDateTo` | date-time | Include loans whose **current** status was set on/after or on/before this ISO 8601 date. This is when the loan last changed status, not when it closed or funded. | | `closingDateFrom` / `closingDateTo` | date-time | Include loans with a closing date on/after or on/before this ISO 8601 date. Sparsely populated (it is an *estimated* closing date, unset on most loans), so a zero-loan result usually means missing data. | | `updatedFrom` / `updatedTo` | date-time | Include loans last updated on/after or on/before this ISO 8601 date. | | `loanOfficerIds` | GUID\[] | Restrict to these loan-officer user IDs. Always intersected with your access scope — you can narrow but never widen what you see. Obtain IDs from `search_users`. | | `referringAgentId` | GUID | Restrict to loans referred by this user ID. | | `processorId` | GUID | Restrict to loans handled by this processor user ID. | | `loanAmountMin` / `loanAmountMax` | decimal | Loan amount range in USD (inclusive). | | `commissionMin` / `commissionMax` | decimal | Locked-compensation (commission) range in USD (inclusive). | | `propertyState` | string | Two-letter subject-property state code, e.g. `FL`. | {% hint style="warning" %} Do **not** use `statuses`, `statusDateFrom`/`statusDateTo`, or `closingDateFrom`/`closingDateTo` to count loans that closed or funded in a period. A closed loan's current status is usually a later one such as `Loan Sold` or `Loan Archived`, and `closingDate` is mostly unset. Use `report_loan_milestones`. {% endhint %} ### loan\_pipeline\_report Current pipeline snapshot: how many loans are now at each macro stage, with total amount, plus a monthly trend over the look-back window. * **Backing store:** Cosmos loan snapshot * **Roles:** Admin, Processor, DualLicensedAgent, ConciergeKey, Agent **Parameters** | Name | Type | Required | Description | | ---------------- | ------- | -------- | ------------------------------------------------------------------------------------------------ | | `filter` | object | No | Loan filter applied to the pipeline. Same shape as `report_loans` — see **The `filter` object**. | | `lookbackMonths` | integer | No | Number of months of monthly trend to include. Default `3`. | **Returns** A `LoanPipelineReportResponse`. `currentPipelineByStage` is the snapshot distribution across the ordered macro stages — Lead → Application → Processing → Underwriting → Approved → Clear to Close → Docs → Funded, with terminal stages last — each row carrying `stage`, `order`, `isTerminal`, `count`, `totalLoanAmount`, and the raw MLM `statuses` rolled up into that stage. `monthlyTrend` gives loan activity by status date per month over the look-back window. `activeLoanCount` counts loans in non-terminal stages, and `note` carries any caveat the service attached.
Example response ```json { "currentPipelineByStage": [ { "stage": "Processing", "order": 3, "isTerminal": false, "count": 2, "totalLoanAmount": 750000.00, "statuses": ["Loan Processing", "Loan Pre-Processing"] }, { "stage": "Funded", "order": 8, "isTerminal": true, "count": 1, "totalLoanAmount": 410000.00, "statuses": ["Loan Funded"] } ], "monthlyTrend": [ { "period": "2026-06", "count": 2, "totalLoanAmount": 750000.00 }, { "period": "2026-07", "count": 1, "totalLoanAmount": 410000.00 } ], "activeLoanCount": 2, "note": "Snapshot of current loan statuses." } ```
**Notes** * Answers "what is in the pipeline now" and "where are loans piling up or dropping off". * For how many loans **crossed** each stage over a period (funnel flow rather than a current snapshot), use `report_loan_milestones`. ### report\_loan\_milestones Funnel flow over time: counts distinct loans that **reached** each milestone per week or month, rather than where they sit now. * **Backing store:** SQL (persisted LOS loan status history) * **Roles:** Admin, Processor, DualLicensedAgent, ConciergeKey, Agent **Parameters** | Name | Type | Required | Description | | ------------------ | --------- | -------- | ---------------------------------------------------------------------------------------------------------------------------------- | | `filter` | object | No | Optional loan filter, same shape as `report_loans`. See **The `filter` object**. | | `granularity` | string | No | Time bucket: `Week` (ISO, Monday start) or `Month`. Default `Month`. Unrecognized values fall back to `Month`. | | `lookback` | integer | No | How many buckets back from today to include (e.g. `4` = last 4 weeks or 4 months). Default `4`; values of zero or less become `4`. | | `groupByNorthstar` | boolean | No | Group milestones into the ordered Northstar funnel stages. Default `true`. `false` groups by raw milestone name. | | `excludeStages` | string\[] | No | Northstar stage labels to exclude, e.g. `["Other", "5. Closed & Funded"]` to show only in-flight stages. Default none. | The Northstar stage labels and the accepted granularities come from `get_loan_reporting_metadata` (`northstarStages`, `milestoneGranularities`). **Returns** A `LoanMilestoneReportResponse`: the echoed `granularity` and `groupBy`, a `truncated` flag, a `note`, and a `buckets` array where each entry is `{ period, stage, stageOrder, loanCount, loanAmount }`.
Example response ```json { "granularity": "Month", "groupBy": "Northstar", "truncated": false, "note": "Built from persisted LOS status history; counts are distinct loans per period.", "buckets": [ { "period": "2026-06", "stage": "1. Opened", "stageOrder": 1, "loanCount": 12, "loanAmount": 4210000.00 }, { "period": "2026-06", "stage": "5. Closed & Funded", "stageOrder": 5, "loanCount": 4, "loanAmount": 1480000.00 }, { "period": "2026-07", "stage": "1. Opened", "stageOrder": 1, "loanCount": 9, "loanAmount": 3125000.00 }, { "period": "2026-07", "stage": "5. Closed & Funded", "stageOrder": 5, "loanCount": 6, "loanAmount": 2260000.00 } ] } ```
**Notes** * This is **the** tool for "how many loans, or how much volume, did we close or fund last month/quarter/year" and for any "loans that went through status X in timeframe Y" question. It reads persisted historical status crossings, so it still counts loans whose current status has since moved on to `Loan Sold`, `Loan Purchased`, or `Loan Archived`. * Built from persisted LOS status history, so only loans synced to the LOS are covered. * Counts are distinct **per period** — a loan that re-crosses a stage in two periods appears in both. * Statuses outside the five Northstar stages fall into `Other`, and the stage mapping is provisional. Pass `groupByNorthstar: false` to see the underlying milestones. ### mlo\_pipeline\_report MLO onboarding pipeline cohort report: for deals created in the Sales or Onboarding pipeline during a date range, a breakdown by current pipeline stage per time period. * **Backing store:** Data Warehouse (`[HubSpot].[DealsRenamed]`) * **Roles:** Admin, Processor, ConciergeKey **Parameters** | Name | Type | Required | Description | | ---------- | ------------------- | -------- | --------------------------------------------------------------------------- | | `fromDate` | date (`YYYY-MM-DD`) | No | Start of the date range (inclusive). Omit to include all history. | | `toDate` | date (`YYYY-MM-DD`) | No | End of the date range (inclusive). Omit to include up to today. | | `bucket` | enum | No | Time bucket: `Month` (default, `YYYY-MM`) or `Week` (`YYYY-Wnn`, ISO week). | **Returns** An `MloPipelineReportResponse`: the echoed `bucket`, a `rowCount`, and `rows` where each entry is `{ period, stage, displayOrder, isTerminal, entryCount }`. `period` is the deal **creation** date bucket — when the MLO candidate first entered HubSpot — so each period is a cohort. `stage` is the candidate's **current** stage (1–15, `Fully Onboarded`, `Closed Won`/`Closed Lost`, and so on). `isTerminal` is true for Fully Onboarded, License Not Approved, Exam Failed, and App Cancelled. `entryCount` is the number of distinct deals created in that period currently at that stage.
Example response ```json { "bucket": "Month", "rowCount": 3, "rows": [ { "period": "2026-05", "stage": "Fully Onboarded", "displayOrder": 16, "isTerminal": true, "entryCount": 7 }, { "period": "2026-06", "stage": "4. Exam Scheduled", "displayOrder": 4, "isTerminal": false, "entryCount": 3 }, { "period": "2026-06", "stage": "App Cancelled", "displayOrder": 19, "isTerminal": true, "entryCount": 1 } ] } ```
**Notes** * This tool is **not** about loans — it tracks MLO recruiting and licensing candidates. It lives on this page because it answers a reporting question of the same shape as the loan funnel reports. * Use it for "how many MLO candidates who started in each month are currently at each stage", "where is the drop-off", and "how many were fully onboarded". * Because `period` is the creation date and `stage` is the current stage, a row is a cohort-to-date figure, not a transition count. * **Org-wide, not caller-scoped.** The warehouse tables carry no Realfinity loan-officer or company column, so results cannot be filtered to the caller's own records. Access is restricted to the roles trusted with broad visibility; plain Agent and DualLicensedAgent are excluded. ### warehouse\_loan\_milestone\_report Org-wide loan pipeline funnel from the Data Warehouse: for a date range, how many distinct loans reached each milestone per time period. * **Backing store:** Data Warehouse (`[MeridianLink].[Loans]`) * **Roles:** Admin, Processor, ConciergeKey **Parameters** | Name | Type | Required | Description | | ---------- | ------------------- | -------- | ---------------------------------------------------------------------------------------- | | `fromDate` | date (`YYYY-MM-DD`) | Yes | Start of the date range (inclusive). | | `toDate` | date (`YYYY-MM-DD`) | Yes | End of the date range (inclusive). Must be greater than or equal to `fromDate`. | | `bucket` | enum | No | Time bucket for grouping: `Month` (default, `YYYY-MM`) or `Week` (`YYYY-Wnn`, ISO week). | **Returns** A `DwLoanMilestoneReportResponse`: the echoed `bucket`, `fromDate`, and `toDate`, a `rowCount`, and `rows` where each entry is `{ period, milestone, stage, stageOrder, loanCount }`. Milestones covered are Opened, Submitted, Underwriting, Approved, Clear to Close, Docs, Funded/Closed, plus terminal milestones; `stage` is the Northstar pipeline stage that milestone maps to.
Example response ```json { "bucket": "Month", "fromDate": "2026-05-01", "toDate": "2026-07-31", "rowCount": 3, "rows": [ { "period": "2026-05", "milestone": "Opened", "stage": "1. Opened", "stageOrder": 1, "loanCount": 148 }, { "period": "2026-06", "milestone": "Approved", "stage": "4. Approved", "stageOrder": 4, "loanCount": 96 }, { "period": "2026-06", "milestone": "Funded", "stage": "5. Closed & Funded", "stageOrder": 5, "loanCount": 71 } ] } ```
**Notes** * **Org-wide, not caller-scoped** — the same warehouse limitation described under `mlo_pipeline_report`. The scoping callout and role table at the top of this page do **not** apply to this tool. * For access-scoped, per-loan-officer milestone reporting — the right default for most questions — prefer `report_loan_milestones`. Reach for this warehouse variant only when you genuinely want org-wide totals. * Passing a `toDate` earlier than `fromDate` is rejected as a bad request. * For ad-hoc queries against the same warehouse table, use `query_data_warehouse` and `get_data_warehouse_fields` on [Data Warehouse](/mcp/tool-reference/data-warehouse.md). ### get\_loan\_reporting\_metadata Return the valid vocabulary for the loan reporting tools: loan statuses, loan types, purposes, group-by dimensions, metric functions, metric fields, date fields, order-by options, plus the Northstar stages and milestone granularities. * **Backing store:** none — static server-side vocabulary * **Roles:** Admin, Processor, DualLicensedAgent, ConciergeKey, Agent **Parameters** This tool takes no parameters. **Returns** A `LoanReportingMetadataResponse` — ten string arrays of accepted values. Call it first if you are unsure which exact values the reporting tools accept. It returns no loan or user data, so it is safe to call at any time.
Example response This is the live production response, verbatim. ```json { "statuses": [ "App Started", "Loan Officer Review", "Loan Open", "Loan Prequal", "Loan Preapproval", "Loan Submitted", "Loan Approved", "Loan Docs", "Loan Funded", "Loan On Hold", "Loan Suspended", "Loan Canceled", "Loan Denied", "Loan Closed", "Loan Underwriting", "Loan Other", "Loan Recorded", "Loan Clear To Close", "Loan Processing", "Loan Final Underwriting", "Loan Docs Back", "Loan Funding Conditions", "Loan Final Docs", "Loan Sold", "Loan Pre-Processing", "Loan Document Check", "Loan Document Check Failed", "Loan Pre-Underwriting", "Loan Condition Review", "Loan Pre-Doc QC", "Loan Docs Ordered", "Loan Docs Drawn", "Loan Investor Conditions", "Loan Investor Conditions Sent", "Loan Ready For Sale", "Loan Submitted For Purchase Review", "Loan In Purchase Review", "Loan Pre-Purchase Conditions", "Loan Submitted For Final Purchase Review", "Loan In Final Purchase Review", "Loan Clear To Purchase", "Loan Purchased", "Loan Counter Offer", "Loan Withdrawn", "Loan Archived" ], "loanTypes": ["Conventional", "FHA", "VA", "USDA/Rural Housing", "Other"], "purposes": [ "Purchase", "Refinance", "Refinance Cashout", "Construction", "Construction Perm", "Other", "FHA Streamline Refi", "VA IRRRL", "Home Equity" ], "groupByDimensions": [ "None", "Status", "LoanType", "Purpose", "LoanOfficer", "ReferringAgent", "Month", "Quarter", "Year" ], "metricFunctions": ["Count", "Sum", "Average", "Min", "Max"], "metricFields": ["LoanAmount", "LockedCompensation", "EstimatedCompensation"], "dateFields": ["StatusDate", "ClosingDate", "Updated"], "orderByOptions": ["updated", "statusDate", "amount", "closingDate", "commission"], "northstarStages": [ "1. Opened", "2. Pre-Approved", "3. Rate Locked", "4. Approved", "5. Closed & Funded" ], "milestoneGranularities": ["Week", "Month"] } ```
**Notes** * `groupByDimensions`, `metricFunctions`, `metricFields`, and `dateFields` are exactly the accepted values for `report_loans`' `groupBy`, `metric`, `metricField`, and `dateField`. * `statuses`, `loanTypes`, and `purposes` are the accepted values for `filter.statuses`, `filter.loanTypes`, and `filter.purposes` on every tool that takes a `filter`. * `northstarStages` and `milestoneGranularities` supply `report_loan_milestones`' `excludeStages` and `granularity`. * `orderByOptions` applies to the loan-listing tools on [Loan Lookup](/mcp/tool-reference/loan-lookup.md), not to the aggregate reports. * This vocabulary does **not** cover the Data Warehouse tools. For those, call `get_data_warehouse_fields` — see [Data Warehouse](/mcp/tool-reference/data-warehouse.md). ## Related pages * [Loan Lookup](/mcp/tool-reference/loan-lookup.md) — per-loan retrieval and search * [Tasks & Conditions](/mcp/tool-reference/tasks-and-conditions.md) — task and underwriting-condition reporting * [Authentication](/mcp/connecting/authentication.md) — how the calling user is identified * [Troubleshooting](/mcp/connecting/troubleshooting.md) — empty results, error labels, scoping surprises * [Overview](/mcp/overview.md) — the full tool catalog