Skip to content

Reporting Assumptions

Last updated: 2026-08-27.

This page captures process-relevant reporting assumptions. These are not mutating business workflows, but they affect how process owners should interpret Power BI and exported report data.

CRM Overview Model

Current implemented assumptions:

  • CRM contacts, companies, deals, MSP plans, MSP seats, MSP plan history, and MSP seat history are loaded from middleware report endpoints.
  • Company is the primary slicer path for contacts, deals, and MSP plans.
  • MSP Seat visuals use denormalized contact details such as contact email and lead status rather than an active Power BI relationship from Contacts to MSP Seats.
  • Current MSP plan and current MSP seat exports are active-only backend exports. For current MSP active-seat counts, Billing_Status values Active and Onboarding are both included when the seat billing dates are in range and the linked plan is active.
  • Historical MSP plan and seat tables are reconstructed at refresh time from CRM billing start/end date ranges. They are not filtered by the seat's current Billing_Status, because a seat that is now Ended can still be valid for a historical month.
  • MSP plan exports include PO_User_Count as a plan-level PO user count for comparison with active seat reporting.
  • MSP plan pricing and monthly revenue are not reported from Managed Services Plan fields in the CRM overview model.
  • Empty contact company or lead source values are normalized to blank/null-style values, and empty lead status becomes Uncategorised.

Process implication:

  • Historical MSP reporting depends on billing date ranges being maintained correctly in CRM.
  • PO_User_Count is a reporting comparison value. It does not currently drive MSP invoice generation or active seat eligibility.
  • MSP revenue should not be interpreted from the MSP plan tables; revenue reporting needs invoice-line data.
  • Seat lead status in visuals is copied from the linked Contact at export time; it should be read as reporting context, not as the source of seat billing eligibility.
  • Current MSP active-seat numbers include onboarding seats for operational visibility; MSP invoice generation has its own billing-eligibility rules.

Project Timesheets Model

Current implemented assumptions:

  • Current timelogs come from the live Delivery Model timelog report.
  • Archived timelogs come from a separate archived backfill report.
  • The final Power BI timelogs table appends current and archived rows.
  • Delivery Model status in Power BI is derived from Valid_Until: a date before the refresh date is reported as completed; blank, today, and future dates are reported as active.
  • Timelogs are assigned to Delivery Models by scope and log date. Both Valid_From and Valid_Until are inclusive, and completed Delivery Models continue to own timelogs from their historical validity periods.
  • Sequential recurring Delivery Models can share a project and scope. Adjacent validity periods preserve a continuous monthly comparison while applying each model's expected labour to its own months.
  • If same-scope validity periods overlap, the model with the latest Valid_From owns timelogs in the overlap.
  • Archived backfill should not be scheduled for routine refresh after a successful import; it should be refreshed manually after historical edits.
  • User filtering should use the final users query, which combines current users with fallback users found in timelogs.

Process implication:

  • Historical project edits may require a cache reset and a manual Power BI refresh before they appear in reporting.
  • Unintended validity gaps leave timelogs in the gap unmapped; unintended overlaps resolve to the later-starting model rather than combining expected labour from both models.

Scorecard Excel Workbook

Current implemented assumptions:

  • The SharePoint-hosted scorecard workbook refreshes read-only middleware CSV routes protected by the backend Excel/Power BI Basic auth credentials.
  • The P&L report is calculated in Excel from the xero_profit_and_loss staging table. Xero remains the accounting source of truth.
  • Lead reporting uses the leads staging table from /scorecard/leads.csv. The backend fetches Zoho CRM Leads with converted=both, because converted leads are not visible in the normal CRM Leads view.
  • New lead counts use created_month_start. Converted lead counts use converted_month_start where is_converted is true.
  • Deal movement reporting uses the deals staging table. Deal creation counts use created_time; won and lost counts use closing_date filtered by the CRM stage values used for won or lost statuses.
  • Excel for the web data source credentials should be treated as user-scoped to the Microsoft account that configured them. The Basic auth credential is a shared middleware report credential, not a personal Zoho or Xero login.
  • The published workbook should not contain old pivot-cache or range sources named list_leads_import or list_deals_import. Sales pivots should use the current leads and deals tables.

Process implication:

  • A lead can be counted in two different months: once when created and once when converted.
  • Converted-lead reporting depends on the middleware export, not on a CRM Leads view.
  • Workbook viewers may see already-loaded data without being able to refresh. Users who need to refresh need the scorecard Basic auth credentials.
  • If old imported ranges remain in pivots or formulas, SharePoint refresh can fail even when the backend CSV routes and credentials are valid.

Archived Timelog Backfill

Current implemented assumptions:

  • The archived backfill discovers Delivery Model projects, keeps archived projects, fetches logs through Zoho Projects REST APIs, and caches a generated CSV.
  • Archived project discovery is bulk and paginated. Remaining legacy REST calls are paused before the Zoho rolling request limit is reached.
  • The default historical range starts at 2024-01-01 and ends at the last day of the previous month.
  • Cache reset mutates generated cache files only. It does not change CRM, Projects, Xero, or Power BI model definitions.
  • If a generation lock is fresh, reset returns a conflict and leaves the existing job alone. Locks older than 30 minutes are treated as stale.
  • If Zoho throttles cache generation, reporting remains unavailable for that archived table until the upstream cooldown expires. Refresh attempts during the cooldown return the remaining wait time and do not start another job.

References

  • CRM overview model: pbi/crm-overview/README.md
  • Project timesheets model: pbi/project-timesheets/README.md
  • Archived timelog backfill: docs/notes/2026-05-06_archived_timelog_backfill.md