Example: Sales Order Report

An advanced example demonstrating pandas DataFrame operations including groupby, pivot tables, numpy calculations, and multi-sheet Excel export.

What You'll Learn

┌─────────────────────────────────────────────────────────────────────────────┐
│                    ADVANCED PANDAS FEATURES                                  │
└─────────────────────────────────────────────────────────────────────────────┘

  This example covers:

  ┌─────────────────────┐    ┌─────────────────────┐    ┌─────────────────────┐
  │  pandas.groupby()   │    │  pandas.pivot_table │    │  numpy.where()      │
  │  ─────────────────  │    │  ─────────────────  │    │  ─────────────────  │
  │  • Group by column  │    │  • Cross-tabulation │    │  • Conditional calc │
  │  • Multiple aggs    │    │  • Multi-index      │    │  • Vectorized ops   │
  │  • Reset index      │    │  • Fill values      │    │  • Fast processing  │
  └─────────────────────┘    └─────────────────────┘    └─────────────────────┘

  ┌─────────────────────┐    ┌─────────────────────┐    ┌─────────────────────┐
  │  Multi-Sheet Excel  │    │  Status Filtering   │    │  Progress Bars      │
  │  ─────────────────  │    │  ─────────────────  │    │  ─────────────────  │
  │  • 6 analysis views │    │  • Dynamic SQL      │    │  • Visual feedback  │
  │  • Different layouts│    │  • Dropdown filter  │    │  • Color coding     │
  │  • Auto-formatting  │    │  • Conditional join │    │  • Percentage calc  │
  └─────────────────────┘    └─────────────────────┘    └─────────────────────┘

JSON Data Structure

Sales Order Fields

Field	Type	Description	Example
`internalid`	number	NetSuite ID	`54321`
`so_number`	string	SO transaction number	`"SO-2025-001"`
`order_date`	string	Order date	`"15/01/2025"`
`ship_date`	string	Expected ship date	`"20/01/2025"`
`customer_name`	string	Customer name	`"PT. ABC"`
`status`	string	Order status	`"Pending Fulfillment"`
`currency`	string	Currency code	`"IDR"`
`department`	string	Department name	`"Sales - Jakarta"`
`total_amount`	number	Order total	`50000000`
`ordered_qty`	number	Quantity ordered	`100`
`fulfilled_qty`	number	Quantity shipped	`75`
`billed_qty`	number	Quantity invoiced	`50`

Sample Data

[
  {
    "internalid": 54321,
    "so_number": "SO-2025-001",
    "order_date": "05/01/2025",
    "ship_date": "10/01/2025",
    "customer_name": "PT. Maju Bersama",
    "status": "Pending Fulfillment",
    "currency": "IDR",
    "department": "Sales - Jakarta",
    "total_amount": 75000000,
    "ordered_qty": 150,
    "fulfilled_qty": 0,
    "billed_qty": 0
  },
  {
    "internalid": 54322,
    "so_number": "SO-2025-002",
    "order_date": "10/01/2025",
    "ship_date": "15/01/2025",
    "customer_name": "CV. Sukses Mandiri",
    "status": "Partially Fulfilled",
    "currency": "IDR",
    "department": "Sales - Surabaya",
    "total_amount": 45000000,
    "ordered_qty": 100,
    "fulfilled_qty": 60,
    "billed_qty": 40
  }
]

Jupyter Notebook: Test Advanced Pandas

Cell 1: Setup and Load Data

import pandas as pd
import numpy as np
import json
from datetime import datetime

# Sample data (simulating what Suitelet would provide)
so_data = [
    {"so_number": "SO-001", "customer_name": "PT. A", "status": "Pending Fulfillment",
     "department": "Sales - JKT", "order_date": "05/01/2025", "total_amount": 50000000,
     "ordered_qty": 100, "fulfilled_qty": 0, "billed_qty": 0},
    {"so_number": "SO-002", "customer_name": "PT. B", "status": "Partially Fulfilled",
     "department": "Sales - SBY", "order_date": "10/01/2025", "total_amount": 30000000,
     "ordered_qty": 80, "fulfilled_qty": 50, "billed_qty": 30},
    {"so_number": "SO-003", "customer_name": "PT. A", "status": "Pending Billing",
     "department": "Sales - JKT", "order_date": "15/02/2025", "total_amount": 25000000,
     "ordered_qty": 60, "fulfilled_qty": 60, "billed_qty": 0},
    {"so_number": "SO-004", "customer_name": "PT. C", "status": "Billed",
     "department": "Sales - JKT", "order_date": "20/02/2025", "total_amount": 40000000,
     "ordered_qty": 90, "fulfilled_qty": 90, "billed_qty": 90},
]

df = pd.DataFrame(so_data)
print("DataFrame created:")
print(df)

Cell 2: Parse Dates and Add Columns

# Parse dates
def parse_date(date_str):
    if not date_str:
        return pd.NaT
    try:
        return datetime.strptime(str(date_str), '%d/%m/%Y')
    except:
        return pd.NaT

df['parsed_date'] = df['order_date'].apply(parse_date)
df['month_year'] = df['parsed_date'].apply(
    lambda x: x.strftime('%B %Y') if pd.notna(x) else 'Unknown'
)
df['month_sort'] = df['parsed_date'].apply(
    lambda x: x.strftime('%Y%m') if pd.notna(x) else '999999'
)

print("With date columns:")
print(df[['so_number', 'order_date', 'month_year', 'month_sort']])

Cell 3: Calculate Fulfillment Percentage with numpy

# numpy.where() for conditional calculation
# Syntax: np.where(condition, value_if_true, value_if_false)

df['fulfillment_pct'] = np.where(
    df['ordered_qty'] > 0,                              # condition
    (df['fulfilled_qty'] / df['ordered_qty'] * 100).round(2),  # if true
    0                                                    # if false
)

print("With fulfillment percentage:")
print(df[['so_number', 'ordered_qty', 'fulfilled_qty', 'fulfillment_pct']])

Cell 4: groupby() - Summary by Customer

# Group by customer and aggregate multiple columns
customer_summary = df.groupby('customer_name').agg({
    'so_number': 'count',      # Count orders
    'total_amount': 'sum',     # Sum amounts
    'ordered_qty': 'sum',
    'fulfilled_qty': 'sum',
    'billed_qty': 'sum'
}).reset_index()

# Rename columns for clarity
customer_summary.columns = [
    'Customer', 'Total Orders', 'Total Amount',
    'Ordered Qty', 'Fulfilled Qty', 'Billed Qty'
]

# Add calculated column
customer_summary['Fulfillment %'] = np.where(
    customer_summary['Ordered Qty'] > 0,
    (customer_summary['Fulfilled Qty'] / customer_summary['Ordered Qty'] * 100).round(2),
    0
)

# Sort by amount descending
customer_summary = customer_summary.sort_values('Total Amount', ascending=False)

print("Customer Summary:")
print(customer_summary)

Cell 5: groupby() - Summary by Month

# Group by month (with sort key)
month_summary = df.groupby(['month_sort', 'month_year']).agg({
    'so_number': 'count',
    'total_amount': 'sum',
    'ordered_qty': 'sum',
    'fulfilled_qty': 'sum'
}).reset_index()

# Clean up
month_summary = month_summary.sort_values('month_sort')
month_summary = month_summary.drop(columns=['month_sort'])
month_summary.columns = ['Month', 'Orders', 'Amount', 'Ordered', 'Fulfilled']

print("Monthly Summary:")
print(month_summary)

Cell 6: groupby() - Summary by Status

status_summary = df.groupby('status').agg({
    'so_number': 'count',
    'total_amount': 'sum'
}).reset_index()

status_summary.columns = ['Status', 'Count', 'Amount']
status_summary = status_summary.sort_values('Count', ascending=False)

print("Status Summary:")
print(status_summary)

Cell 7: pivot_table() - Cross-Tabulation

# Pivot table: Month vs Status (count of orders)
pivot_count = pd.pivot_table(
    df,
    values='so_number',           # What to aggregate
    index='month_year',           # Row labels
    columns='status',             # Column labels
    aggfunc='count',              # Aggregation function
    fill_value=0                  # Fill missing with 0
)

print("Pivot Table - Order Count by Month & Status:")
print(pivot_count)

# Pivot table: Month vs Status (sum of amounts)
pivot_amount = pd.pivot_table(
    df,
    values='total_amount',
    index='month_year',
    columns='status',
    aggfunc='sum',
    fill_value=0
)

print("\nPivot Table - Amount by Month & Status:")
print(pivot_amount)

Cell 8: Understanding pivot_table Output

# Pivot table creates a DataFrame with:
# - Index: unique values from 'index' parameter (month_year)
# - Columns: unique values from 'columns' parameter (status)
# - Values: aggregated values

print("Pivot table structure:")
print(f"Index (rows): {pivot_count.index.tolist()}")
print(f"Columns: {pivot_count.columns.tolist()}")

# Access specific values
print(f"\nJanuary 2025, Pending Fulfillment: {pivot_count.loc['January 2025', 'Pending Fulfillment']}")

# Reset index to convert back to regular DataFrame
pivot_reset = pivot_count.reset_index()
print("\nAfter reset_index:")
print(pivot_reset)

Suitelet: Status Filter Implementation

/**
 * Get Sales Order status options for dropdown
 */
function getStatusOptions() {
    return [
        { id: '', name: '-- All Status --' },
        { id: 'SalesOrd:A', name: 'Pending Approval' },
        { id: 'SalesOrd:B', name: 'Pending Fulfillment' },
        { id: 'SalesOrd:D', name: 'Partially Fulfilled' },
        { id: 'SalesOrd:E', name: 'Pending Billing/Partially Fulfilled' },
        { id: 'SalesOrd:F', name: 'Pending Billing' },
        { id: 'SalesOrd:G', name: 'Billed' },
        { id: 'SalesOrd:H', name: 'Closed' }
    ];
}

// Add to form
const fldStatus = form.addField({
    id: 'custpage_status',
    type: serverWidget.FieldType.SELECT,
    label: 'Status',
    container: 'fld_grp_param'
});

// Populate options
const statusOptions = getStatusOptions();
statusOptions.forEach(opt => {
    fldStatus.addSelectOption({ value: opt.id, text: opt.name });
});

Dynamic SQL Condition

function getSalesOrders(fromDate, toDate, status) {
    // Build dynamic status condition
    let statusCondition = '';
    if (status) {
        statusCondition = `AND t.status = '${status}'`;
    }

    const sqlQuery = `
        SELECT
            t.id AS internalid,
            t.tranid AS so_number,
            -- ... other fields ...
        FROM transaction t
        INNER JOIN transactionline tl
            ON tl.transaction = t.id
            AND tl.mainline = 'F'
        WHERE t.recordtype = 'salesorder'
            AND t.trandate BETWEEN TO_DATE('${fromDate}', 'DD/MM/YYYY')
                               AND TO_DATE('${toDate}', 'DD/MM/YYYY')
            ${statusCondition}   /* <-- Dynamic condition inserted here */
        GROUP BY ...
    `;

    // Execute query...
}

HTML: Progress Bar Component

Visual Fulfillment Progress

/**
 * Create a visual progress bar based on fulfillment percentage
 * @param {number} ordered - Ordered quantity
 * @param {number} fulfilled - Fulfilled quantity
 * @returns {string} HTML for progress bar
 */
function getProgressBar(ordered, fulfilled) {
    // Calculate percentage (avoid division by zero)
    var percent = ordered > 0
        ? Math.min((fulfilled / ordered) * 100, 100)
        : 0;

    // Color coding based on progress
    var color;
    if (percent >= 100) {
        color = '#059669';  // Green - complete
    } else if (percent >= 50) {
        color = '#d97706';  // Orange - partial
    } else {
        color = '#dc2626';  // Red - low
    }

    // Return HTML
    return `
        <div class="progress-bar">
            <div class="progress-bar-fill"
                 style="width: ${percent}%; background: ${color};">
            </div>
        </div>
        <small style="font-size: 10px; color: #6b7280;">
            ${percent.toFixed(0)}%
        </small>
    `;
}

// CSS for progress bar
/*
.progress-bar {
    width: 100%;
    height: 8px;
    background: #e5e7eb;
    border-radius: 4px;
    overflow: hidden;
}

.progress-bar-fill {
    height: 100%;
    border-radius: 4px;
    transition: width 0.3s ease;
}
*/

Use in Tabulator

var table = new Tabulator("#so-table", {
    data: soData,
    columns: [
        // ... other columns ...
        {
            title: "Progress",
            headerHozAlign: "center",
            width: 100,
            formatter: function(cell) {
                var row = cell.getRow().getData();
                return getProgressBar(row.ordered_qty, row.fulfilled_qty);
            }
        }
    ]
});

Pyodide: Multi-Sheet Excel Generation

Excel Structure

┌─────────────────────────────────────────────────────────────────────────────┐
│                    MULTI-SHEET EXCEL STRUCTURE                               │
└─────────────────────────────────────────────────────────────────────────────┘

  Sheet 1: Detail Data
  ┌─────────────────────────────────────────────────────────────────────────┐
  │ SO #  │ Customer     │ Date       │ Status    │ Amount    │ Progress   │
  ├───────┼──────────────┼────────────┼───────────┼───────────┼────────────┤
  │ SO-001│ PT. ABC      │ 05/01/2025 │ Pending   │ 50,000,000│ 0%         │
  │ SO-002│ CV. XYZ      │ 10/01/2025 │ Partial   │ 30,000,000│ 62.5%      │
  └─────────────────────────────────────────────────────────────────────────┘

  Sheet 2: By Customer
  ┌─────────────────────────────────────────────────────────────────────────┐
  │ Customer      │ Orders │ Total Amount │ Ordered │ Fulfilled │ Fill %   │
  ├───────────────┼────────┼──────────────┼─────────┼───────────┼──────────┤
  │ PT. ABC       │ 5      │ 250,000,000  │ 500     │ 350       │ 70%      │
  │ CV. XYZ       │ 3      │ 120,000,000  │ 200     │ 200       │ 100%     │
  └─────────────────────────────────────────────────────────────────────────┘

  Sheet 3: By Month
  Sheet 4: By Status
  Sheet 5: By Department
  Sheet 6: Pivot Analysis (Month × Status)

Python Code for Multi-Sheet

import io
import base64
import pandas as pd
import numpy as np
import xlsxwriter
import json
from datetime import datetime

from js import array_so, report_from_date, report_to_date

# Load data
so_data = json.loads(array_so)
df = pd.DataFrame(so_data)

if len(df) == 0:
    raise Exception("No data to export")

# Prepare data (parse dates, calculate percentages, etc.)
# ... (as shown in Jupyter examples above)

# Create workbook
bio = io.BytesIO()
workbook = xlsxwriter.Workbook(bio, {'nan_inf_to_errors': True})

# Define formats
title_fmt = workbook.add_format({
    'bold': True, 'font_size': 16,
    'font_color': '#0369a1', 'align': 'center'
})
header_fmt = workbook.add_format({
    'bold': True, 'font_color': 'white',
    'bg_color': '#0369a1', 'border': 1
})
data_fmt = workbook.add_format({'border': 1})
money_fmt = workbook.add_format({
    'border': 1, 'num_format': '#,##0.00'
})
pct_fmt = workbook.add_format({
    'border': 1, 'num_format': '0.00"%"'
})

# ============================================
# SHEET 1: Detail Data
# ============================================
ws1 = workbook.add_worksheet('Detail Data')

# Set column widths
ws1.set_column(0, 0, 15)   # SO #
ws1.set_column(1, 1, 30)   # Customer
ws1.set_column(2, 6, 15)   # Other columns

# Write title
ws1.merge_range(0, 0, 0, 6, 'SALES ORDER REPORT - DETAIL', title_fmt)

# Write headers
headers = ['SO #', 'Customer', 'Date', 'Status', 'Amount', 'Ordered', 'Fulfilled']
for col, header in enumerate(headers):
    ws1.write(2, col, header, header_fmt)

# Write data
for row_idx, row in df.iterrows():
    ws1.write(row_idx + 3, 0, row['so_number'], data_fmt)
    ws1.write(row_idx + 3, 1, row['customer_name'], data_fmt)
    ws1.write(row_idx + 3, 2, row['order_date'], data_fmt)
    ws1.write(row_idx + 3, 3, row['status'], data_fmt)
    ws1.write(row_idx + 3, 4, float(row['total_amount']), money_fmt)
    ws1.write(row_idx + 3, 5, int(row['ordered_qty']), data_fmt)
    ws1.write(row_idx + 3, 6, int(row['fulfilled_qty']), data_fmt)

# ============================================
# SHEET 2: By Customer (using groupby)
# ============================================
ws2 = workbook.add_worksheet('By Customer')

customer_summary = df.groupby('customer_name').agg({
    'so_number': 'count',
    'total_amount': 'sum',
    'ordered_qty': 'sum',
    'fulfilled_qty': 'sum'
}).reset_index()

# Write to sheet...

# ============================================
# SHEET 6: Pivot Analysis
# ============================================
ws6 = workbook.add_worksheet('Pivot Analysis')

# Create pivot table
pivot = pd.pivot_table(
    df,
    values='so_number',
    index='month_year',
    columns='status',
    aggfunc='count',
    fill_value=0
)

# Reset index to get regular DataFrame
pivot_reset = pivot.reset_index()

# Write headers
for col, header in enumerate(pivot_reset.columns):
    ws6.write(0, col, header, header_fmt)

# Write data
for row_idx, row in pivot_reset.iterrows():
    for col_idx, value in enumerate(row):
        if col_idx == 0:
            ws6.write(row_idx + 1, col_idx, value, data_fmt)
        else:
            ws6.write(row_idx + 1, col_idx, int(value), data_fmt)

# ============================================
# Close and return
# ============================================
workbook.close()
bio.seek(0)
base64.b64encode(bio.getvalue()).decode()

Download Complete Files

Download Suitelet (.js)

Download HTML Template

Comparison: Basic vs Advanced

Feature	Unpaid Invoices (Basic)	Sales Order (Advanced)
Query	Single table	JOIN with transactionline
Filters	Date range	Date range + Status dropdown
Aggregation	Server-side only	Line-level with GROUP BY
Visual	Overdue badges	Progress bars
Excel	1 sheet, subtotals	6 sheets, pivot tables
Pandas	Basic groupby	groupby, pivot_table, numpy

Key Takeaways

┌─────────────────────────────────────────────────────────────────────────────┐
│                         KEY PANDAS PATTERNS                                  │
└─────────────────────────────────────────────────────────────────────────────┘

  GROUPBY PATTERN:
  ────────────────
  summary = df.groupby('column').agg({
      'count_col': 'count',
      'sum_col': 'sum',
      'avg_col': 'mean'
  }).reset_index()


  PIVOT TABLE PATTERN:
  ────────────────────
  pivot = pd.pivot_table(
      df,
      values='measure',      # What to aggregate
      index='row_dimension', # Row labels
      columns='col_dimension', # Column labels
      aggfunc='sum',         # count, sum, mean, etc.
      fill_value=0           # Handle missing
  )


  NUMPY CONDITIONAL PATTERN:
  ──────────────────────────
  df['new_col'] = np.where(
      df['col'] > 0,         # Condition
      df['a'] / df['b'],     # Value if True
      0                      # Value if False
  )

Deployment Checklist

□ 1. Upload files to File Cabinet
     SuiteScripts/Reports/
     ├── sales_order_report_sl.js
     └── sales_order_report.html

□ 2. Create Script Record
     - Name: Sales Order Report
     - Script File: sales_order_report_sl.js

□ 3. Create Deployment
     - Status: Released
     - Audience: Select roles

□ 4. Test thoroughly
     - Test with different status filters
     - Verify all 6 Excel sheets
     - Check pivot table calculations

Next Steps

Example: Unpaid Invoices - Basic workflow
Pandas Guide - Complete reference
XlsxWriter Guide - Excel formatting

What You'll Learn​

JSON Data Structure​

Sales Order Fields​

Sample Data​

Jupyter Notebook: Test Advanced Pandas​

Cell 1: Setup and Load Data​

Cell 2: Parse Dates and Add Columns​

Cell 3: Calculate Fulfillment Percentage with numpy​

Cell 4: groupby() - Summary by Customer​

Cell 5: groupby() - Summary by Month​

Cell 6: groupby() - Summary by Status​

Cell 7: pivot_table() - Cross-Tabulation​

Cell 8: Understanding pivot_table Output​

Suitelet: Status Filter Implementation​

Dynamic Status Dropdown​

Dynamic SQL Condition​

HTML: Progress Bar Component​

Visual Fulfillment Progress​

Use in Tabulator​

Pyodide: Multi-Sheet Excel Generation​

Excel Structure​

Python Code for Multi-Sheet​

Download Complete Files​

Comparison: Basic vs Advanced​

Key Takeaways​

Deployment Checklist​

Next Steps​

What You'll Learn

JSON Data Structure

Sales Order Fields

Sample Data

Jupyter Notebook: Test Advanced Pandas

Cell 1: Setup and Load Data

Cell 2: Parse Dates and Add Columns

Cell 3: Calculate Fulfillment Percentage with numpy

Cell 4: groupby() - Summary by Customer

Cell 5: groupby() - Summary by Month

Cell 6: groupby() - Summary by Status

Cell 7: pivot_table() - Cross-Tabulation

Cell 8: Understanding pivot_table Output

Suitelet: Status Filter Implementation

Dynamic Status Dropdown

Dynamic SQL Condition

HTML: Progress Bar Component

Visual Fulfillment Progress

Use in Tabulator

Pyodide: Multi-Sheet Excel Generation

Excel Structure

Python Code for Multi-Sheet

Download Complete Files

Comparison: Basic vs Advanced

Key Takeaways

Deployment Checklist

Next Steps