SQL Objects
Remote Server: SQL-DIMENSIONS.BAILEY.PRI
These objects live on SQL-DIMENSIONS.BAILEY.PRI, the Focalpoint database server where the source tables live. All transactions logic is defined here, once, and every consumer reads it.
| Object | Type | Purpose | Consumed By |
|---|---|---|---|
| [BI].[ProjectTransactions_Base] | View | The transactions logic, defined once. | Project Transactions-v2 report [BI].[ProjectTransactions] |
| [BI].[ProjectTransactions] | Stored procedure | Consumes [BI].[ProjectTransactions_Base] and adds ReviewStatus based on the CVR's last refresh date parameter. |
CVR Excel Workbooks |
Local Server: SQL021
The server the CVR workbooks connect to (SQ021.Networks.local, database FgcAccountsLive). It holds no transactions logic of its own; it passes the workbook's call through to the remote server.
| Object | Type | Purpose | Consumed By |
|---|---|---|---|
| [BI].[ProjectTransactions] | Synonym | Consumes [BI].[ProjectTransactions] on the remote server and exposes it locally for the CVR to execute. | CVR Excel Workbooks |
SSRS Report
The report now reads the [BI].[ProjectTransactions_Base] view. Nothing else about it has changed.
Excel CVR Template Updates
Each CVR template gains a Transactions worksheet, refreshed by the existing front-sheet button alongside the project summary. Nothing about the existing sheets changes.
| Component | Type | Purpose |
|---|---|---|
| Transactions worksheet | Worksheet | Holds the Transactions table and its slicers. |
| Transactions Connection | OLEDB connection | Executes the [BI].[ProjectTransactions] synonym with the project number and the last refresh date parameter. |
| Slicers | Table slicers | Filter the grid by Review Status, Transaction Type and Cost Centre Description. After every refresh the Review Status slicer defaults to Needs Review, so by default the QS only sees what changed since the last review. |
| RefreshData macro | VBA (Module1) | Extends the existing button: captures the previous refresh stamp, passes it to the proc as the since-date, refreshes the connections, resets the slicer and stamps the new refresh time last, so a failed refresh does not move the stamp. |
How a Line is Marked for Review
Every row on the Transactions worksheet carries a ReviewStatus of Needs Review or Previously Reviewed. When the refresh button is pressed, the CVR passes its last refresh date to the proc, which works out what could have changed since:
| Condition | ReviewStatus |
|---|---|
| The transaction sits in an accounting period that was still open at the last refresh. | Needs Review |
| Everything else: no change possible since the last refresh. | Previously Reviewed |
Why accounting periods rather than dates:
- Focalpoint does not record when a transaction posted, so "what posted since date X" cannot be asked directly.
- A posting can only ever land in an open accounting period. So anything posted after the last refresh must sit in a period that was still open at that moment.
- [BI].[PERIOD_CHANGE] is a dated log of period rolls. From it the proc finds the period that was open at the last refresh; that period and every later one counts as "could have changed", and every earlier period was already sealed.
- The rule is deliberately cautious: everything in a still-open period stays flagged Needs Review, even if it was looked at last time. A change can never be missed; at worst a row is flagged twice.
Changes to Transactions Logic from Project Transactions Report v1
The Project Transactions report logic was carried over as it stands. There are three changes:
| Change | Report v1 | New Logic |
|---|---|---|
| Where the logic lives | The calculation was SQL embedded in the report itself. | Defined once in [BI].[ProjectTransactions_Base]; the report and the CVR read the same logic. |
| Batch-flagged invoices | No batch filter. As a rule this does not matter because a batch-flagged (matched but unposted) invoice never reaches the project transactions. The cost row is only written when the invoice posts. A database-wide check on 18 Sep 2026 found a small number of rogue rows that break the rule, almost all journals from 2014. | Posted only, guaranteed. Rows linked to a batch-flagged ledger entry are excluded, matching the commitment rule. |
| Rounding | Raw float values (e.g. 46.665000000000003). | Money columns rounded to 2 decimal places. |