ProjectCommitments Report Relink

What This Does

The existing SSRS ProjectCommitments report (Martin's report) was rebuilt on top of the single commitment view, so it shows the corrected figures without anyone noticing a difference in layout. To the reader it is the same report: same three coloured sections, same fonts, same parameter prompt. The only visible addition is one Unallocated column in each section, which is where misallocated invoicing now surfaces instead of quietly distorting the real lines.

Nothing about the calculation lives in the report any more. The report is now a thin reader over [BI].[Commitment_OrderLine_Base], the same view the CVR Excel template consumes. One definition, one source of truth. See the Commitment Views page for the view itself.

The Three Changes

1. Dataset repointed to the view

The dataset previously held the old quantity-based commitment SQL inline (the POD_QTYORD = POD_QTYINVD gate that hid over-invoices). That whole query was replaced with a select from the view, aliased back to the report's existing field names so every tablix cell keeps working untouched. The single @CH_CODE parameter is unchanged.

SELECT
    ProjectCode,
    ProjectName,
    ProjectManager,
    Supplier,
    SupplierCode              AS Supplier_Code,
    OrderNumber               AS Order_Number,
    ItemDescription           AS Item_Description,
    OrderValue                AS Order_Value,
    OrderValue_Labour         AS Order_Value_Labour,
    OrderValue_Plant          AS Order_Value_Plant,
    OrderValue_Materials      AS Order_Value_Materials,
    OrderValue_Subcontract    AS Order_Value_Subcontract,
    OrderValue_Unallocated    AS Order_Value_Unallocated,
    ProjectCode               AS POD_COSTHEADER,
    CostCentre                AS POD_COSTCENTRE,
    LineKey                   AS POD_PRIMARY,
    LineNumber                AS POD_LINE_NUMBER,
    PostedValue               AS Transaction_Total,
    PostedValue_Labour        AS Transaction_Labour,
    PostedValue_Plant         AS Transaction_Plant,
    PostedValue_Materials     AS Transaction_Materials,
    PostedValue_Subcontract   AS Transaction_Subcontract,
    PostedValue_Unallocated   AS Transaction_Unallocated,
    Commitment                AS Commitments_Total,
    Commitment_Labour         AS Commitments_Labour,
    Commitment_Plant          AS Commitments_Plant,
    Commitment_Materials      AS Commitments_Materials,
    Commitment_Subcontract    AS Commitments_Subcontract,
    Commitment_Unallocated    AS Commitments_Unallocated
FROM BI.Commitment_OrderLine_Base
WHERE ProjectCode = @CH_CODE

2. Report field names mapped to view columns

The report keeps its 26 original field names (so the tablix expressions never move); the aliasing above does the mapping. Three new fields were added for the Unallocated column.

The Labour / Plant / Materials / Subcontract splits map the same way (OrderValue_Labour → Order_Value_Labour, PostedValue_Labour → Transaction_Labour, Commitment_Labour → Commitments_Labour, and so on).

3. Unallocated column added to all three sections

The table has four description columns then fifteen value columns (three sections of Total / Labour / Plant / Materials / Subcontract). A new 20mm column was inserted immediately after each section's Subcontract column, giving twenty-two columns in all. Every row was updated to match:

• Section-title row — each coloured banner (Purchase Orders, Transactions, Commitments) now spans six columns instead of five.
• Header row — an Unallocated heading added after each Subcontract heading.
• Detail row — a cell bound to the matching new field after each Subcontract cell.
• Total row — =Sum(Fields!…_Unallocated.Value) after each Subcontract total.
• Blank banding rows — a matching blank cell so every row still has the same column count (an RDL will not render otherwise).

Each new cell copies the styling of the Subcontract cell beside it (border, font, padding, number format), so it looks native. The edit was applied through an XML parser rather than by hand, so column counts and cell spans stay exactly consistent across all eight rows.

Over-Invoiced Flags and Row Shading

Two flag columns were added at the far right, mirroring the CVR Excel template, plus a hidden RowType field that drives the row shading. Both flags come straight from the view.

Cell fills are taken from the workbook so report and spreadsheet match exactly:

• Over Invoiced = "Yes" → fill #E6B9B8
• Line Over Invoiced = "Yes" → fill #D99694
• The whole Unallocated row → grey #BFBFBF, overriding the green / yellow / red section colours on that row only.

Where an Unallocated row is also order-over-invoiced, the Over Invoiced cell keeps its red flag colour rather than going grey, so the flag stays visible. That matches the conditional-format priority in the Excel tab.

Line Over Invoiced Amount Column

A money column, Line Over Invoiced Amount, was added at the far right (after the Line Over Invoiced flag), bound to the view's LineOverInvoicedAmount. It shows the amount invoiced on a line above that line's value (0 otherwise), so the over-invoicing is visible on the line itself rather than only pooled on the Unallocated row. It greys on Unallocated rows like the other columns and has a Total-row sum. Per order it sums to that order's Unallocated posted value. Same column now appears on the CVR Commitments tab, so report and spreadsheet match.

What Does Not Change

• Report layout, the three coloured section bands, fonts and colours.
• The parameter prompt (single @CH_CODE, "Project Number").
• The data source (FGC_Dimensions).
• No ReviewStatus / change-detection in the report — that lives only in the Excel proc [BI].[Commitment_OrderLine], which the report does not use.

Deploy Notes

• The view [BI].[Commitment_OrderLine_Base] must exist in the database the report's FGC_Dimensions data source points at. That is the same Focalpoint database where the source tables live, so no synonym is needed on the report side (unlike the Excel path, which reaches the view across servers).
• The report server connects directly to that source, so the report is published and pointed at the correct published data source; there is no need to open it in Report Builder against the remote server first.
• Unallocated rows appear as ordinary detail lines (order value zero, the misallocated money in the Transactions Unallocated column and its negative net in the Commitments Unallocated column), so category columns still reconcile to the section totals on every order.
• Ensure every published copy of the report starts at Purchase Order, not Purchase Request — a point already raised with Finance on the Definition page.