Open Sales Orders Data Model

Modified on Tue, 28 Jul at 5:50 PM

Model Summary

The QQube Open Sales Orders Data Model analyzes Sales Orders that are still open when QQube synchronizes with QuickBooks. It provides open quantities, amounts, fulfillment, and expected shipping activity.

Model Type	Open sales-order analysis
Transaction Coverage	Open Sales Orders only
Historical Coverage	Current state at synchronization; no historical Sales Order versions
Primary Use	Track open quantities, values, back orders, and expected shipments

Best Used For

Reviewing open quantities and amounts.
Tracking expected shipping dates.
Analyzing back orders and initial fulfillment.
Reviewing open orders by customer, job, item, class, or sales representative.
Including assembly components in item-level analysis.

Key Capabilities

Separates original, invoiced, and open quantities and amounts.
Includes printed and base units of measure.
Includes home and foreign currency values.
Supports shipping-period analysis.
Includes linked posting and non-posting activity.

QuickBooks Data Limitations

This is an open Sales Orders model, not a historical Sales Order model.

QuickBooks does not retain historical versions of a Sales Order. The model reflects the current state available during synchronization.

Assembly Components

Use the Line is BOM Explosion field.

This Document Line Attribute identifies component rows created from an assembly item.

Quantity, Rate, and Amount Fields

Native fields come from QuickBooks Sales Order data. QQube field extensions add printed-unit, foreign-rate, and actual-rate values for analysis.

Native Fields (12)

Quantity Fields	Rate Fields	Amount Fields
Line Original Quantity Line Invoiced Quantity Line Open Quantity	Line Rate Line Rate Percent Line Exchange Rate	Line Original Amount Line Original Amount (Foreign) Line Invoiced [SO Rate] Amount Line Invoiced [SO Rate] Amount (Foreign) Line Open [SO Rate] Amount Line Open [SO Rate] Amount (Foreign)

QQube Field Extensions (9)

Quantity Fields	Rate Fields	Amount Fields
Line Original Quantity [Printed] Line Invoiced Quantity [Printed] Line Open Quantity [Printed]	Line Rate (Foreign) Line Rate [Printed]	Line Invoiced [Actual Rate] Amount Line Invoiced [Actual Rate] Amount (Foreign) Line Open [Actual Rate] Amount Line Open [Actual Rate] Amount (Foreign)

Power BI and Excel Power Pivot Measures

The prepared Open Sales Orders examples include 43 visible DAX measures in three display folders. Hidden measures are not included.

Measure Folder	Visible Measures
.Base Measures	20
Calendar Pattern Measures	12
Open SO Quantity Measures	11

Included Dimensions

Calendars

Due Date	Ship Date	Transaction Date
Job End Date	Job Projected End Date	Job Start Date
Calendar Overview

Document

Document Attributes

Line Attributes

Geography

Lists

Account	Bin Location	Class
Company	Currency	Customer
Customer Rep	Customer Message	Customer Sales Tax Code
Item	Item Sales Tax Code	Job
Job Rep	Sales Rep	Sales Tax Item
Ship Method	Site	Template
Terms	UofM

Linked Information

Sales Linked Transactions

Technical Details

Model Structure	One Open Sales Orders FACT table with related dimensions and relationships already included
Snapshot Basis	Current open Sales Order state at the time of synchronization
DAX Availability	Power BI and Excel Power Pivot examples

Expected Result

Open the prepared example and analyze current open Sales Orders by shipping period, customer, job, item, quantity, or amount.

The FACT table, dimensions, relationships, fields, and measures are already included.