Release 86

by

This release focuses on improving the structure, consistency, and reliability of Sales Pipeline and Project reporting, while maintaining full historic coverage.

Sales Pipeline Review and Refit

A full review and refit has been completed across the core Sales Pipeline domains:

  • Business Opportunities
  • Orders
  • Invoices

Views have been refactored to follow the same structural pattern, including:

  • Core details
  • Line-level values
  • Standardised date and code views

The updated model adopts a modular structure, enabling consistent querying across pipeline stages and supporting the continued development of a reusable Power BI reporting template (PBIt).

Benefits

  • Clearer separation of core details, dates, codes, and values
  • Improved reuse of logic across reports
  • Easier maintenance and extension over time
  • This alignment improves cross-object reporting and reduces complexity when combining pipeline data.

Archival of Redundant Business Opportunity Value Views

The following Business Opportunity value views have been archived:

  • busopp_meta_values
  • busopp_meta_values_estimated
  • busopp_meta_values_quoted
  • busopp_meta_values_total

Why this change was made

  • Quoted and total values were not consistently populated across Business Opportunities.
  • Estimated values already formed the practical basis of reporting.
  • Item/Line-level value views are now the preferred structure, with aggregation handled at report level.

Impact

  • No loss of reporting capability
  • Simpler and more consistent value handling across the Sales Pipeline

Historic Data Alignment

Archive views supporting historic data back to April and October 2023 have been updated to reflect the refitted structures.

This ensures consistency between historic and current datasets and avoids behavioural differences in long-running reports.

Corrections to Opportunity Date Logic

Corrections have been applied to the derivation and ordering of key Business Opportunity dates, including:

  • Estimated Sign Date
  • Closure Date

These changes improve the accuracy of forecasting and timeline-based analysis, particularly where multiple potential source dates exist.

Logic applied

  • MP8
    • ESTIMATED_SIGN_DATE
      COALESCE(e.Probable_Date_of_Order_PDO, e.day_bid_submitted, e.Project_End_Date, e.date_won_lost)
    • CLOSURE_DATE
      COALESCE(e.Project_End_Date, e.day_bid_submitted, e.date_won_lost, e.Probable_Date_of_Order_PDO)
  • MP9
    • ESTIMATED_SIGN_DATE
      COALESCE(e.Probable_Date_of_Order_PDO, e.day_bid_submitted, e.Project_End_Date)
    • CLOSURE_DATE
      e.Project_End_Date

Project Reporting Refactor

Project value reporting has been refactored to align with the Sales Pipeline approach, covering:

  • Cost
  • Revenue
  • Forecast
  • Time
  • Contingency and expenses

Value attributes have been simplified, relying on contextual metadata rather than embedded naming, improving consistency and extensibility. This further supports the META_TYPE + ‘_’ + ATTRIBUTE notation.

Metadata and Data Quality Improvements

  • Project metadata codes have been standardised to ensure all categories include clear descriptions.
  • Free-text object names are now safely escaped to prevent downstream formatting or JSON issues.
  • A stabilisation workaround has been applied for views containing very long comments, preventing query failures (infrastructure_oshrecords).

Leave a Comment