Sales Order Report Documentation

Overview

The Sales Order Report module (report.sale.order) provides detailed product-level sales analytics with flexible time period comparisons. This report enables analysis of sales data by product over monthly or quarterly periods with historical trend comparison capabilities. It is essential for product managers, inventory planners, and sales analysts who need granular product performance insights.

---

Model Information

Model Name: report.sale.order Display Name: Sales Order Report Type: Transient Report Model (_transient = True)

Features

• ✅ Transient model (data generated on-demand, not persisted)
• ✅ Flexible time period selection (months or quarters)
• ✅ Historical period comparison (up to 11 previous periods)
• ✅ Product-level granularity with code and name tracking
• ✅ Dynamic column generation based on selected periods
• ✅ Monthly and quarterly aggregation modes

---

Report Purpose

Business Questions Answered

1. Product Performance: Which products are selling month-over-month or quarter-over-quarter?
2. Trend Analysis: How have product sales trended over the past 3, 6, or 12 months?
3. Seasonal Patterns: Are there seasonal variations in product sales?
4. Product Comparison: How do different products compare within the same time period?
5. Growth Tracking: Which products show growth or decline over time?

Key Metrics Calculated

• Product Sales by Period: Revenue per product per month/quarter
• Multi-Period Comparison: Side-by-side comparison across periods
• Product Code Tracking: Unique product identification
• Period-over-Period Trends: Historical comparison capabilities

---

Field Reference

Filter Parameters

Field	Type	Required	Description
order_date_from	Date	❌	Start date for report period (default: 1st of current month)
order_date_to	Date	❌	End date for report period (default: current date)
product_id	Many2One	❌	Filter by specific product
product_code	Char	❌	Filter by product code
compare_by	Selection	❌	Period type: "1" (Month) or "3" (Quarter)
compare_periods	Selection	❌	Number of previous periods to compare (1-11)

Selection Field Options

Compare By Options: - "1": Monthly comparison - "3": Quarterly comparison

Compare Periods Options: - "1" through "11": Number of previous periods to show

---

Report Generation Method

get_report_data(ids, context={})

Generates the product sales report with dynamic period columns.

Parameters: - ids (list): Report record IDs containing filter parameters - context (dict): Additional context (not currently used)

Returns: dict - Report data structure

Return Data Structure:

{
    "company_name": "Company Name",
    "order_date_from": "2026-01-01",
    "order_date_to": "2026-01-31",
    "sale_orders": [
        {
            "id": 123,
            "name": "Premium Widget",
            "code": "WIDGET-001",
            "JAN": "15000.00",    # Monthly amounts
            "FEB": "18000.00",
            "MAR": "16500.00",
            # ... or for quarterly:
            "q1_date": "25000.00",  # Quarterly amounts
            "q2_date": "28000.00",
        },
        # ... more products
    ],
    "months": {  # Month visibility flags
        "JAN": True,
        "FEB": True,
        "MAR": False,
        # ... all 12 months
    },
    "Show_flag": False,  # True for quarterly mode
    "quarters": {  # Quarter visibility and dates
        "q1": True,
        "q1_date": "2026-01-01",
        "q2": True,
        "q2_date": "2026-04-01",
        # ... up to q11
    }
}

Behavior: - Queries sale_order_line joined with product - Groups sales by product and time period - Dynamically creates columns for each period - Supports filtering by product_id and/or product_code - Validates date range when using quarterly comparison - Returns data suitable for grid/table display

---

SQL Query Patterns

Monthly Aggregation

SELECT name, code, sale_order_line.product_id,
       SUM(sale_order_line.amount),
       date_trunc('month', sale_order_line.create_time) AS month
FROM product
INNER JOIN sale_order_line ON sale_order_line.product_id = product.id
WHERE sale_order_line.create_time BETWEEN '{order_date_from}' AND '{order_date_to}'
GROUP BY sale_order_line.product_id, name, code, month

Quarterly Aggregation

SELECT name, code, sale_order_line.product_id,
       SUM(sale_order_line.amount),
       date_trunc('quarter', sale_order_line.create_time) AS quarter
FROM product
INNER JOIN sale_order_line ON sale_order_line.product_id = product.id
WHERE sale_order_line.create_time BETWEEN '{order_date_from}' AND '{order_date_to}'
GROUP BY sale_order_line.product_id, name, code, quarter
ORDER BY quarter

With Product Filters

-- Filter by product_id
WHERE (sale_order_line.product_id = '{product_id}')

-- Filter by product_code
WHERE (product.code = '{product_code}')

-- Filter by both
WHERE (sale_order_line.product_id = '{product_id}')
  AND (product.code = '{product_code}')

---

Common Use Cases

Use Case 1: Monthly Product Sales Report (Last 6 Months)

# Create report parameters for 6-month comparison
report_id = get_model("report.sale.order").create({
    "order_date_from": "2025-08-01",
    "order_date_to": "2026-01-31",
    "compare_by": "1",  # Monthly
    "compare_periods": "5"  # 5 previous months + current = 6 total
})

# Generate report
report_data = get_model("report.sale.order").get_report_data([report_id])

# Display results
for product in report_data["sale_orders"]:
    print(f"{product['code']}: {product['name']}")

    # Show only visible months
    for month in ["AUG", "SEP", "OCT", "NOV", "DEC", "JAN"]:
        if report_data["months"].get(month):
            amount = product.get(month, "0.00")
            print(f"  {month}: ${amount}")

Result:

WIDGET-001: Premium Widget
  AUG: $15000.00
  SEP: $18000.00
  OCT: $16500.00
  NOV: $19200.00
  DEC: $22000.00
  JAN: $17800.00

WIDGET-002: Standard Widget
  AUG: $12000.00
  SEP: $13500.00
  ...

---

Use Case 2: Quarterly Product Performance

# Create quarterly report for last year (4 quarters)
from dateutil.relativedelta import relativedelta
from datetime import date

end_date = date.today()
start_date = end_date - relativedelta(years=1)

report_id = get_model("report.sale.order").create({
    "order_date_from": start_date.strftime("%Y-%m-%d"),
    "order_date_to": end_date.strftime("%Y-%m-%d"),
    "compare_by": "3",  # Quarterly
    "compare_periods": "3"  # 3 previous quarters + current = 4 total
})

# Generate report
report_data = get_model("report.sale.order").get_report_data([report_id])

# Validate quarterly mode
if report_data["Show_flag"]:
    print("Quarterly Report Generated")

    for product in report_data["sale_orders"]:
        print(f"\n{product['name']} ({product['code']})")

        # Show active quarters
        for q_num in range(1, 12):
            q_key = f"q{q_num}"
            if report_data["quarters"].get(q_key):
                q_date = report_data["quarters"].get(f"{q_key}_date")
                amount = product.get(f"{q_key}_date", "0.00")
                print(f"  Q{q_num} ({q_date}): ${amount}")

Result:

Quarterly Report Generated

Premium Widget (WIDGET-001)
  Q1 (2025-01-01): $52000.00
  Q2 (2025-04-01): $58000.00
  Q3 (2025-07-01): $61000.00
  Q4 (2025-10-01): $67000.00

---

Use Case 3: Single Product Deep Dive

# Analyze specific product over 12 months
product = get_model("product").search_browse([["code", "=", "WIDGET-001"]])[0]

report_id = get_model("report.sale.order").create({
    "order_date_from": "2025-02-01",
    "order_date_to": "2026-01-31",
    "product_id": product.id,
    "product_code": "WIDGET-001",
    "compare_by": "1",  # Monthly
    "compare_periods": "11"  # 12 months total
})

report_data = get_model("report.sale.order").get_report_data([report_id])

# Should return only one product
if report_data["sale_orders"]:
    product_data = report_data["sale_orders"][0]

    print(f"Product: {product_data['name']}")
    print(f"Code: {product_data['code']}\n")
    print("Monthly Sales:")

    months = ["FEB", "MAR", "APR", "MAY", "JUN", "JUL",
              "AUG", "SEP", "OCT", "NOV", "DEC", "JAN"]

    monthly_values = []
    for month in months:
        if report_data["months"].get(month):
            amount = float(product_data.get(month, "0"))
            monthly_values.append(amount)
            print(f"  {month}: ${amount:,.2f}")

    # Calculate trend
    if len(monthly_values) > 1:
        avg_amount = sum(monthly_values) / len(monthly_values)
        print(f"\nAverage Monthly Sales: ${avg_amount:,.2f}")

        # Simple trend: compare last 3 months vs first 3 months
        recent_avg = sum(monthly_values[-3:]) / 3
        early_avg = sum(monthly_values[:3]) / 3
        growth = ((recent_avg - early_avg) / early_avg * 100) if early_avg > 0 else 0
        print(f"Trend (Recent vs Early 3mo): {growth:+.1f}%")

---

Use Case 4: Product Category Comparison

# Compare all products in a category

# Get products in category
category_id = 5  # Example category
products = get_model("product").search_browse([["categ_id", "=", category_id]])

# Generate report for all products
report_id = get_model("report.sale.order").create({
    "order_date_from": "2026-01-01",
    "order_date_to": "2026-01-31",
    "compare_by": "1",
    "compare_periods": "2"  # Current + 2 previous months
})

report_data = get_model("report.sale.order").get_report_data([report_id])

# Filter to category products
product_codes = {p.code for p in products}
category_sales = [
    prod for prod in report_data["sale_orders"]
    if prod["code"] in product_codes
]

# Sort by current month sales (JAN)
category_sales.sort(
    key=lambda x: float(x.get("JAN", "0")),
    reverse=True
)

print("Category Product Performance (Jan 2026):\n")
for idx, prod in enumerate(category_sales, 1):
    jan_sales = float(prod.get("JAN", "0"))
    print(f"{idx}. {prod['name']}: ${jan_sales:,.2f}")

---

Use Case 5: Seasonal Analysis

# Analyze seasonal patterns across 12 months

report_id = get_model("report.sale.order").create({
    "order_date_from": "2025-01-01",
    "order_date_to": "2025-12-31",
    "compare_by": "1",
    "compare_periods": "11"  # All 12 months
})

report_data = get_model("report.sale.order").get_report_data([report_id])

months = ["JAN", "FEB", "MAR", "APR", "MAY", "JUN",
          "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"]

# Aggregate all products by month
monthly_totals = {month: 0.0 for month in months}

for product in report_data["sale_orders"]:
    for month in months:
        if report_data["months"].get(month):
            amount = float(product.get(month, "0"))
            monthly_totals[month] += amount

# Find peak and low months
peak_month = max(monthly_totals.items(), key=lambda x: x[1])
low_month = min(monthly_totals.items(), key=lambda x: x[1])

print("2025 Seasonal Analysis:\n")
for month in months:
    total = monthly_totals[month]
    print(f"{month}: ${total:,.2f}")

print(f"\nPeak Month: {peak_month[0]} (${peak_month[1]:,.2f})")
print(f"Low Month: {low_month[0]} (${low_month[1]:,.2f})")

# Calculate seasonal index
avg_monthly = sum(monthly_totals.values()) / 12
print(f"Average Monthly Sales: ${avg_monthly:,.2f}")

print("\nSeasonal Index (100 = average):")
for month in months:
    index = (monthly_totals[month] / avg_monthly * 100) if avg_monthly > 0 else 0
    print(f"{month}: {index:.1f}")

---

Performance Tips

1. Optimize Date Ranges

# Bad: Very large date range with many products (slow)
report_id = get_model("report.sale.order").create({
    "order_date_from": "2020-01-01",  # 6 years of data!
    "order_date_to": "2026-01-31",
    "compare_by": "1",
    "compare_periods": "11"
})

# Good: Reasonable date range
report_id = get_model("report.sale.order").create({
    "order_date_from": "2025-01-01",  # 1 year
    "order_date_to": "2026-01-31",
    "compare_by": "1",
    "compare_periods": "11"
})

2. Use Product Filters for Large Datasets

# Bad: Query all products then filter in Python
report_data = get_model("report.sale.order").get_report_data([report_id])
filtered = [p for p in report_data["sale_orders"] if p["code"].startswith("WIDGET")]

# Good: Filter at database level
report_id = get_model("report.sale.order").create({
    "order_date_from": "2026-01-01",
    "order_date_to": "2026-01-31",
    "product_code": "WIDGET-%",  # Database LIKE pattern
    "compare_by": "1"
})

3. Quarterly for Long-Term Trends

# Bad: 24 months of monthly data (slow, cluttered)
report_id = get_model("report.sale.order").create({
    "order_date_from": "2024-01-01",
    "order_date_to": "2026-01-31",
    "compare_by": "1",
    "compare_periods": "11"
})

# Good: Use quarterly for 2-year view
report_id = get_model("report.sale.order").create({
    "order_date_from": "2024-01-01",
    "order_date_to": "2026-01-31",
    "compare_by": "3",  # Quarterly
    "compare_periods": "7"  # 8 quarters
})

---

Best Practices

1. Validate Date Ranges for Quarterly Reports

# The model validates that quarterly period fits in date range
from datetime import datetime
from dateutil.relativedelta import relativedelta

end_date = datetime.strptime("2026-01-31", "%Y-%m-%d")
compare_periods = 3
months_needed = compare_periods * 3  # 9 months for 3 quarters

start_date = datetime.strptime("2025-12-01", "%Y-%m-%d")

# Check if range is sufficient
months_in_range = (end_date.year - start_date.year) * 12 + \
                  (end_date.month - start_date.month)

if months_in_range < months_needed:
    print(f"ERROR: Need {months_needed} months, but date range only has {months_in_range}")
else:
    # Safe to create report
    report_id = get_model("report.sale.order").create({
        "order_date_from": start_date.strftime("%Y-%m-%d"),
        "order_date_to": end_date.strftime("%Y-%m-%d"),
        "compare_by": "3",
        "compare_periods": str(compare_periods)
    })

2. Handle Missing Data Gracefully

# Products may not have sales in all periods
report_data = get_model("report.sale.order").get_report_data([report_id])

for product in report_data["sale_orders"]:
    print(f"\n{product['name']}:")

    for month in ["JAN", "FEB", "MAR", "APR"]:
        if report_data["months"].get(month):
            # Use .get() with default to handle missing months
            amount = product.get(month, "0.00")
            amount_float = float(amount) if amount else 0.0

            if amount_float == 0:
                print(f"  {month}: No sales")
            else:
                print(f"  {month}: ${amount_float:,.2f}")

3. Export to Excel with Proper Formatting

import xlsxwriter
from datetime import datetime

def export_product_sales_report(report_id, filename):
    """Export report to Excel with formatting"""

    report_data = get_model("report.sale.order").get_report_data([report_id])

    workbook = xlsxwriter.Workbook(filename)
    worksheet = workbook.add_worksheet("Product Sales")

    # Formats
    header_format = workbook.add_format({
        'bold': True,
        'bg_color': '#4472C4',
        'font_color': 'white',
        'border': 1
    })

    currency_format = workbook.add_format({
        'num_format': '$#,##0.00',
        'border': 1
    })

    # Headers
    col = 0
    worksheet.write(0, col, "Product Code", header_format)
    col += 1
    worksheet.write(0, col, "Product Name", header_format)
    col += 1

    # Period headers (monthly or quarterly)
    if report_data.get("Show_flag"):  # Quarterly
        for q_num in range(1, 12):
            q_key = f"q{q_num}"
            if report_data["quarters"].get(q_key):
                q_date = report_data["quarters"].get(f"{q_key}_date", "")
                worksheet.write(0, col, f"Q{q_num} ({q_date})", header_format)
                col += 1
    else:  # Monthly
        for month in ["JAN", "FEB", "MAR", "APR", "MAY", "JUN",
                      "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"]:
            if report_data["months"].get(month):
                worksheet.write(0, col, month, header_format)
                col += 1

    # Data rows
    row = 1
    for product in report_data["sale_orders"]:
        col = 0
        worksheet.write(row, col, product["code"])
        col += 1
        worksheet.write(row, col, product["name"])
        col += 1

        if report_data.get("Show_flag"):  # Quarterly
            for q_num in range(1, 12):
                q_key = f"q{q_num}"
                if report_data["quarters"].get(q_key):
                    amount = float(product.get(f"{q_key}_date", "0"))
                    worksheet.write(row, col, amount, currency_format)
                    col += 1
        else:  # Monthly
            for month in ["JAN", "FEB", "MAR", "APR", "MAY", "JUN",
                          "JUL", "AUG", "SEP", "OCT", "NOV", "DEC"]:
                if report_data["months"].get(month):
                    amount = float(product.get(month, "0"))
                    worksheet.write(row, col, amount, currency_format)
                    col += 1

        row += 1

    # Auto-adjust column widths
    worksheet.set_column(0, 0, 15)  # Code
    worksheet.set_column(1, 1, 30)  # Name
    worksheet.set_column(2, col, 12)  # Periods

    workbook.close()
    print(f"Report exported to {filename}")

# Use it
export_product_sales_report(report_id, "product_sales_2026.xlsx")

---

Related Models

Model	Relationship	Description
sale.order.line	Data Source	Line items containing product sales
product	Data Source	Product master data
sale.order	Indirect	Order headers linked to lines
company	Reference	Company information for report header
settings	Configuration	System settings
report.sale	Related Report	Master sales dashboard
report.sale.profit	Related Report	Profitability by product

---

Troubleshooting

"Quarter period is greater than your provided date range"

Cause: The number of quarters requested exceeds the date range provided.

Solution: Ensure date range covers all periods:

# For 3 quarters, need at least 9 months
# End: 2026-01-31, Start must be 2025-05-01 or earlier
compare_periods = 3
months_needed = compare_periods * 3

start_date = end_date - relativedelta(months=months_needed)

"No data returned in report"

Cause: No sales order lines exist in the date range, or product filters exclude all data.

Solution: Verify data exists:

# Check if any sales exist in period
cond = [
    ["create_time", ">=", "2026-01-01"],
    ["create_time", "<=", "2026-01-31"]
]
lines = get_model("sale.order.line").search_browse(cond)
print(f"Found {len(lines)} order lines in period")

"Month columns not showing in output"

Cause: The months dictionary controls column visibility based on compare_periods.

Solution: Check month visibility flags:

report_data = get_model("report.sale.order").get_report_data([report_id])

print("Visible months:")
for month, visible in report_data["months"].items():
    if visible:
        print(f"  {month}")

---

Version History

Last Updated: 2026-01-05 Model Version: report_sale_order.py Framework: Netforce

---

Additional Resources

• Sales Order Line Documentation: sale.order.line
• Product Documentation: product
• Sales Dashboard: report.sale
• Profit Analysis: report.sale.profit

---

This documentation is generated for developer onboarding and reference purposes.