Project Transactions (Issue 26)

← Back to CVR Index Commitment Views → Commitments Relink → CVR Commitments Tab →

Status (18 Sep 2026): View built and validated against the report output. Transactions report relinked to the view and published as v2. Proc with ReviewStatus built and the change-detection fence validated. CVR tab built in the master template, and the tab's actuals reconcile to the main CVR sheet (see the reconciliation section below). View redeployed 18 Sep with ROUND(…, 2) on the three money columns and the posted-only batch filter ISNULL(gl.DET_BATCH_FLAG, 0) = 0; verified on 446459 (totals unchanged, timesheets/journals intact).

What This Does

Brings the project's actual transactions (the Project Transactions SSRS report) into the CVR the same way commitments were done: the logic lives once in a view, the report reads off it, and the CVR will read off it too. One definition, one source of truth (issue 26).

The report is the existing Project Transactions report and Finance are happy with it as it stands, so nothing about its layout changes. It is simply repointed at the view so it is no longer a separate copy of the query.

The Objects

[BI].[ProjectTransactions_Base]

Grain: one row per cost / sales / journal transaction. The Project Transactions report query lifted verbatim (built on CST_DETAIL joined to the cost header, ledger, PL / SL transactions and price recs), with the @Project filter removed. Adds two hidden columns, PeriodYearCode and PeriodNumber, so the proc can build the change-detection period fence without re-joining CST_DETAIL. Single source of truth for both the report and the CVR.

[BI].[ProjectTransactions]

Proc, params @SinceDate, @CH_CODE. Selects the display columns from the view for one project and layers on the ReviewStatus column. Same mechanism and same comments as [BI].[Commitment_OrderLine]: StateAtSince / Threshold / PostingActivity CTEs. This is what the CVR button will call.

Both objects are in the Notepad file _temp\cmd-ngbailey-20260917-131833.txt.

Change Detection (ReviewStatus)

Identical to the commitments tab. Focalpoint records no posting timestamp, so a transaction is flagged Needs Review if it sits in a period that was still open at @SinceDate (the period fence via [BI].[PERIOD_CHANGE] + SYS_YEAR), otherwise Previously Reviewed. The slicer will default to Needs Review after each refresh.

Validated. The fence joins SYS_YEAR on YEAR_PURCHASE_CODE = CT_YEAR (the cost detail carries the same purchase-year letter as the commitments ledger, not the nominal one). Proven with a 2025-12-01 cut-off (threshold key 202509): periods L5–L8 came back Previously Reviewed, and L9–L12 plus all 2026 (C) periods came back Needs Review, exactly as expected. Two fixes were needed on the way: the join column (nominal → purchase), and a default of 0 for the threshold when @SinceDate predates the PERIOD_CHANGE history, so the first-ever run's 2000-01-01 fallback flags everything.

Report Relink (v2)

The report's field names already equal the view's column names, so the relink was just a dataset swap, no tablix or field changes, zero visual difference.

Published: http://sql-reports/reports/report/Freedom/IT/Project%20Transactions-v2
New file: CVR\_output\Project Transactions-v2.rdl (original untouched at _context\SSRS Transactions\Project Transactions.rdl).
Dataset repointed to SELECT … FROM [BI].[ProjectTransactions_Base] WHERE CH_CODE LIKE @Project.
@Project parameter preserved, all 22 fields and the tablix untouched. RDL is the 2016 schema; root namespaces retained.
Prerequisite to render: the view must exist in the DB behind the report's FGC_Dims_Live data source.

Validation So Far

The view output was compared against the original report output for project 446459. Every original column matches exactly, in the same order; the only difference is the two added period columns (PeriodYearCode, PeriodNumber), which correctly split CT_Period (e.g. "L8" → L + 8).

Reconciliation to the Main CVR Sheet (18 Sep 2026, project 446459)

The main CVR sheet's actuals are populated by dbo.FGC_ProjectSummary (the ProjectsConnection query). That proc does not sum transactions: it reads SUM(cc_actual_cost) / SUM(cc_actual_sales) from CST_COSTCENTRE, Focalpoint's maintained running totals per cost centre, split LAB% / SUB% / everything-else-as-Materials (so Plant sits inside the Materials figure). The front sheet reads the result through named ranges (LAB_ACT, SUB_ACT, MAT_ACT, SALES_ACT) over Table_ProjectConnection on the Data extract sheet.

Result: the tab reconciles. On 446459 the tab's data rows sum to ActualCosts 1,269,332.85 vs the main sheet's 1,269,332.88 (float dust only, gone once the ROUND change deploys), and sales tie exactly at 2,021,086.53. The running totals in CST_COSTCENTRE agree with CST_DETAIL per cost centre on this project, and it has no batched invoices (all DET_BATCH_FLAG = 0).

The trap that produced the phantom £488,558.75 gap: the table's Total row uses SUBTOTAL, which (a) only counts slicer-visible rows and (b) saves its last cached value in the file. RefreshData defaults the ReviewStatus slicer to Needs Review, so the cached total was the Needs Review subtotal (−488,558.72: seventeen current-period rows dominated by two nominal credit journals, JCA August 2026 −518,256.00 and a PTC credit transfer −37,094.05). Summing the ActualCosts column including the Total row gave 1,269,332.85 − 488,558.72 = 780,774.13, while the sales column appeared fine because its cached subtotal was 0. When reconciling: clear the slicers and read the Total row, or sum the data rows only.

Next Steps

Run the view + proc; validate the ReviewStatus fence (exec #3) and confirm the nominal vs purchase code question above.
Deploy Project Transactions-v2 and confirm it renders identically to the current report.
Build the Transactions tab in the CVR template, same pattern as the Commitments tab (OLEDB connection over the proc, table, slicers, total row, formatting, RefreshData merge).
Confirm the display column set for the CVR tab.