CVR Template: Commitments Tab

← Back to CVR Index Commitment Definition → [BI].[Commitment_OrderLine] →

Status (11 Sep 2026): Built and working in the test template against project 446459. Grid loads from the line level proc, slicers filter it, and the @SinceDate test behaved exactly as the SSMS validation (four orders flagged Needs Review for 3 Sep). Remaining: total row, conditional formatting, Previous_Refresh_Date cell, the RefreshData merge, then rollout to the other templates.

What This Adds to a Template

A visible Commitments sheet holding a table named Commitments, one row per Purchase Order line plus the Unallocated rows, fed by [BI].[Commitment_OrderLine] (the synonym over usp_Commitment_OrderLine).
An OLEDB connection named CommitmentsConnection, same pattern as the existing ProjectsConnection (embedded credentials, command text rewritten by the button macro each refresh).
Slicers on ReviewStatus, Supplier and OverInvoiced, parked in a frozen band above the grid (the table is too wide for slicers to the right). Default view: Needs Review.
The ReviewStatus flag comes from the proc: 'Needs Review' if the order was added, or had invoices/credits posted, since the @SinceDate passed in; the macro passes the template's previous refresh stamp, delivering issue 11's "changes since last refresh" and issue 18's commitments in the CVR together.

Manual Build Steps (as proven in the test template)

Enable the legacy wizard (once per machine): File > Options > Data > tick From Data Connection Wizard (Legacy).
New visible sheet named Commitments.
Create the connection: Data > Get Data > Legacy Wizards > From Data Connection Wizard > Microsoft SQL Server. Server SQ021.Networks.local, credentials fgc_accounts_projects, database FgcAccountsLive, pick any small table (e.g. DimDate) as a placeholder.
Import Data dialog: choose Table, land at Commitments!$A$1, and UNTICK "Add this data to the Data Model". (Model-backed imports show Command type "Table Collection" and fail with "An Evaluate statement cannot return a table without columns" when swapped to a proc.)
Swap the command: Queries & Connections > Connections tab > Properties > Definition. Untick Always use connection file if greyed. Connection name CommitmentsConnection; Command type SQL; Command text:
```
SET NOCOUNT ON; EXEC [BI].[Commitment_OrderLine] @SinceDate = '2000-01-01', @CH_CODE = '446459'
```
Tick Save password. Click Yes to the "no longer identical to the external connection file" prompt (embeds the connection in the workbook, matching ProjectsConnection).
Name the table: click in the grid > Table Design > Table Name: Commitments.
Slicer band: insert rows above the table for the slicers; Table Design > Insert Slicer > ReviewStatus, Supplier, OrderOverInvoiced (add OrderLineOverInvoiced if wanted); arrange in the band; View > Freeze Panes below it. Current template layout: the band occupies the top of the sheet, table header on row 18, data from A19.
Total row: Table Design > Total Row; set Commitment (and OrderAmount, CappedInvoicedAmount, LineOverInvoicedAmount if wanted) to Sum. These are SUBTOTALs, so slicing to Needs Review re-totals to just the visible rows.
Display column set (from the proc, aliased): ProjectCode, ProjectName, ProjectManager, Supplier, SupplierCode, OrderNumber, LineNumber, RowType, ItemDescription, CostCentre, OrderAmount, CappedInvoicedAmount, Commitment, LineOverInvoicedAmount, OrderOverInvoiced, OrderLineOverInvoiced, ReviewStatus. CappedInvoicedAmount is capped at the line value; CappedInvoicedAmount + LineOverInvoicedAmount = total invoiced on the line.
Conditional formatting (fixed applies-to ranges + the Overwrite refresh setting, to stop fragmentation). Home > Conditional Formatting > New Rule > Use a formula:
- =$H18="Unallocated" → grey #BFBFBF whole row, applies to =$A$18:$Q$9998 (H = RowType)
- =$O18="Yes" → fill #E6B9B8, applies to =$O$18:$O$9998 (O = OrderOverInvoiced)
- =$P18="Yes" → fill #D99694, applies to =$P$18:$P$9998 (P = OrderLineOverInvoiced)
Money columns K, L, M, N formatted 2 dp (Comma Style). Column letters assume header row 18, confirm on the sheet.

When the column set changes (rename or new column), REBUILD the table, don't refresh. A query table keeps its existing column layout on refresh, it won't reorder or reliably insert columns. To pick up a changed proc: delete CommitmentsConnection, clear the old table area, re-run SetupCommitmentsTable (it reuses the existing sheet, so the top text, named cells and description boxes survive), then re-insert the slicers and re-apply formats/CF (slicers and CF are bound to the table).

Gotchas met and solved: the Data Model import (step 4) is the one that bites; the placeholder table trick avoids wiring a real table; SET NOCOUNT ON; stops proc row-count chatter confusing Excel; a connection cannot be deleted while the Data Model still references its table (clean via Power Pivot > Manage, or leave the corpse and use a fresh name); conditional formatting rules tied to the table body can fragment across many query refreshes (same rule splintering into slivers in Manage Rules), which the fixed A20:O10000 applies-to range avoids; and the credentials are embedded in the file, the established (if unlovely) pattern in these templates.

Division Templates (the rollout set)

The 17 division templates were pulled from the CVR SharePoint site (one document library per division, the file is named Template.xlsm in each) into CVR\WorkingCVRs\<division-number>.xlsm. Divisions: 105, 110, 115, 130, 150, 200, 300, 301, 305, 403, 406, 416, 501, 504, 600, 602, 604. All 17 validated: valid workbook, VBA macros present, ProjectsConnection present.

Two are non-standard (they carry extra division-specific sheets on top of the standard 14, so the rollout macro must sit alongside them, not assume a 14-sheet layout):

406 — 21 sheets: adds WARNING FLAGS, Commercial, Costs to Date, Committed Cost analysis, Margin Movement, Cable Install Costs, Risks & Opportunities.
604 — 21 sheets: same extras but "Walk" in place of "Commercial".

The other 15 are the standard 14-sheet template. SetupCommitmentsTable only adds its own Commitments sheet, so the extra sheets on 406/604 are no problem, just don't assume sheet positions. Also note: opening these in the browser re-saves and re-stamps them (Excel-for-web recalc + AutoSave), so always Download, never open in place.

Automation for Rollout (run once per template)

For the other templates, this macro does steps 2 to 6 in one go, cloning the connection string (and credentials) from the template's existing ProjectsConnection. Slicers, total row and formatting remain manual (or a follow-up macro if the template count justifies it).

Sub SetupCommitmentsTable()

    Dim ws As Worksheet
    Dim ConnStr As String
    Dim lo As ListObject

    ' Reuse the existing connection string (server, database, credentials)
    ConnStr = ActiveWorkbook.Connections("ProjectsConnection").OLEDBConnection.Connection

    ' Create the Commitments sheet if it does not exist
    On Error Resume Next
    Set ws = ActiveWorkbook.Worksheets("Commitments")
    On Error GoTo 0
    If ws Is Nothing Then
        Set ws = ActiveWorkbook.Worksheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count))
        ws.Name = "Commitments"
    End If

    ' Create the table fed by an OLEDB query (same structure as Data extract)
    Set lo = ws.ListObjects.Add( _
        SourceType:=xlSrcExternal, _
        Source:=ConnStr, _
        Destination:=ws.Range("A19"))  ' header row 19, data from A20 - matches the test template layout

    lo.Name = "Commitments"

    With lo.QueryTable
        .CommandType = xlCmdSql
        .CommandText = "SET NOCOUNT ON; EXEC [BI].[Commitment_OrderLine] " & _
                       "@SinceDate = '2000-01-01', @CH_CODE = '" & Range("CVR!D6").Value & "'"
        .RefreshStyle = xlOverwriteCells
        .BackgroundQuery = False
        .Refresh
    End With

    ' Name the workbook connection so RefreshData can find it
    lo.QueryTable.WorkbookConnection.Name = "CommitmentsConnection"

    MsgBox "Commitments table created and loaded.", vbInformation

End Sub

The Button Macro (replaces Module1.RefreshData)

The existing button macro rewrites ProjectsConnection's command with the project number from CVR!D6, refreshes, and stamps Now into CVR!R7. The replacement keeps all of that and adds the commitments refresh, passing the previous R7 stamp as @SinceDate. Requires a cell on the CVR sheet named Previous_Refresh_Date.

Sub RefreshData()

    Dim PrevRefresh As Variant

    ' Capture the PREVIOUS refresh stamp BEFORE anything refreshes.
    ' CVR!R7 is the existing "last refreshed" stamp. Its old value is the
    ' @SinceDate the commitments proc needs. First ever run: fall back to
    ' 2000-01-01 so everything arrives flagged Needs Review.
    PrevRefresh = Range("CVR!R7").Value
    If Not IsDate(PrevRefresh) Then PrevRefresh = DateSerial(2000, 1, 1)

    ' Show the previous refresh date on the front sheet (Issue 11)
    Range("Previous_Refresh_Date").Value = PrevRefresh

    ' Existing behaviour: project summary for the project in CVR!D6
    With ActiveWorkbook.Connections("ProjectsConnection").OLEDBConnection
        .CommandText = "EXECUTE dbo.FGC_ProjectSummary '" & Range("CVR!D6").Value & "'"
    End With
    ActiveWorkbook.Connections("ProjectsConnection").Refresh

    ' NEW: line level commitments for the same project
    With ActiveWorkbook.Connections("CommitmentsConnection").OLEDBConnection
        .CommandText = "SET NOCOUNT ON; EXEC [BI].[Commitment_OrderLine] " & _
                       "@SinceDate = '" & Format(PrevRefresh, "yyyy-mm-dd hh:nn:ss") & "', " & _
                       "@CH_CODE = '" & Range("CVR!D6").Value & "'"
    End With
    ActiveWorkbook.Connections("CommitmentsConnection").Refresh

    ' Default the ReviewStatus slicer to 'Needs Review' after each refresh
    On Error Resume Next
    With ActiveWorkbook.SlicerCaches("Slicer_ReviewStatus")
        .SlicerItems("Needs Review").Selected = True
        .SlicerItems("Previously Reviewed").Selected = False
    End With
    On Error GoTo 0

    ' Existing behaviour: stamp this refresh. Done LAST, after both refreshes
    ' succeeded, so a failed refresh does not move the stamp.
    Range("CVR!R7").Value = Now

End Sub

Before pasting into a template: open its real Module1 first and check the existing RefreshData for anything beyond the CommandText rewrite, refresh and R7 stamp (it was reconstructed from compressed VBA fragments); merge rather than blindly replace. Keep UploadData untouched.

Server Side Prerequisites

The procs [BI].[usp_Commitment_Order] and [BI].[usp_Commitment_OrderLine] deployed (see the view pages), with the synonym [BI].[Commitment_OrderLine] on the Excel-facing server where applicable. Synonyms over remote procs work (EXEC through the synonym); there is no ALTER SYNONYM, drop and recreate; the linked server needs RPC Out enabled.
GRANT EXECUTE ON [BI].[Commitment_OrderLine] TO [fgc_accounts_projects], plus the linked server security mapping covering the remote end.

Next Steps

Finish the test template: total row, conditional formatting, Previous_Refresh_Date named cell, RefreshData merge, end-to-end button test.
Rollout: run SetupCommitmentsTable in each remaining template, apply the manual cosmetics, merge the macro.
User verification with Elena and the QSs against live projects (issue 15's sign-off step).
Later phases: replace the CVR front sheet's commitment figures (currently FGC_ProjectSummary's CC_*CommittedCost columns via the LabourUnAllocated / MaterialsUnAllocated / SubContractUnAllocated names) with the order level view, once Finance bless the definition.