Creating Custom Reports with NetSuite Purchase Orders

Learn how to create custom reports using purchase order data, including custom fields and calculated metrics.

Prerequisites

• NetSuite connector configured with rest resource enabled
• Purchase orders with custom fields defined
• Proper permissions for custom field access

Basic Custom Report

Create a simple purchase order report:

from abstra.connectors import run_connection_action
import pandas as pd

def create_po_summary_report(date_from, date_to):
    """Create a summary report of purchase orders."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": f"""
                SELECT 
                    po.id,
                    po.tranid as po_number,
                    po.entity as vendor_id,
                    ven.companyname as vendor_name,
                    po.trandate as po_date,
                    po.duedate as due_date,
                    po.total,
                    po.status
                FROM transaction po
                JOIN vendor ven ON po.entity = ven.id
                WHERE po.type = 'PurchOrd'
                AND po.trandate BETWEEN '{date_from}' AND '{date_to}'
                ORDER BY po.trandate DESC
            """
        }
    )
    
    if result['data']['hasMore']:
        print("Warning: More results available. Consider pagination.")
    
    return pd.DataFrame(result['data']['items'])

# Generate report
report_data = create_po_summary_report('2024-01-01', '2024-12-31')
print(f"Generated report with {len(report_data)} purchase orders")

Custom Fields Report

Include custom fields in your report:

def create_detailed_po_report():
    """Create a detailed report including custom fields."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": """
                SELECT 
                    po.id,
                    po.tranid as po_number,
                    ven.companyname as vendor_name,
                    po.total,
                    po.status,
                    -- Custom fields (adjust field IDs as needed)
                    cf1.value as department,
                    cf2.value as project_code,
                    cf3.value as approval_status
                FROM transaction po
                JOIN vendor ven ON po.entity = ven.id
                LEFT JOIN transactioncustomfield cf1 ON po.id = cf1.transaction AND cf1.customfield = 'custbody_department'
                LEFT JOIN transactioncustomfield cf2 ON po.id = cf2.transaction AND cf2.customfield = 'custbody_project'
                LEFT JOIN transactioncustomfield cf3 ON po.id = cf3.transaction AND cf3.customfield = 'custbody_approval'
                WHERE po.type = 'PurchOrd'
                AND po.trandate >= ADD_MONTHS(CURRENT_DATE, -3)
                ORDER BY po.trandate DESC
            """
        }
    )
    
    return pd.DataFrame(result['data']['items'])

# Generate detailed report
detailed_report = create_detailed_po_report()

Vendor Performance Report

Analyze vendor performance metrics:

def create_vendor_performance_report():
    """Create vendor performance analysis."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": """
                SELECT 
                    ven.companyname as vendor_name,
                    COUNT(po.id) as total_pos,
                    SUM(po.total) as total_amount,
                    AVG(po.total) as avg_po_amount,
                    COUNT(CASE WHEN po.status = 'Fully Received' THEN 1 END) as completed_pos,
                    COUNT(CASE WHEN po.duedate < CURRENT_DATE AND po.status != 'Fully Received' THEN 1 END) as overdue_pos
                FROM transaction po
                JOIN vendor ven ON po.entity = ven.id
                WHERE po.type = 'PurchOrd'
                AND po.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
                GROUP BY ven.companyname, ven.id
                HAVING COUNT(po.id) > 0
                ORDER BY total_amount DESC
            """
        }
    )
    
    df = pd.DataFrame(result['data']['items'])
    
    # Calculate completion rate
    df['completion_rate'] = (df['completed_pos'] / df['total_pos'] * 100).round(2)
    
    return df

# Generate vendor performance report
vendor_report = create_vendor_performance_report()
print("Top 5 vendors by total amount:")
print(vendor_report.head())

Monthly Trend Report

Create monthly purchase order trends:

def create_monthly_trend_report():
    """Create monthly PO trend analysis."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": """
                SELECT 
                    EXTRACT(YEAR FROM po.trandate) as year,
                    EXTRACT(MONTH FROM po.trandate) as month,
                    COUNT(po.id) as po_count,
                    SUM(po.total) as total_amount,
                    AVG(po.total) as avg_amount,
                    COUNT(DISTINCT po.entity) as unique_vendors
                FROM transaction po
                WHERE po.type = 'PurchOrd'
                AND po.trandate >= ADD_MONTHS(CURRENT_DATE, -24)
                GROUP BY EXTRACT(YEAR FROM po.trandate), EXTRACT(MONTH FROM po.trandate)
                ORDER BY year DESC, month DESC
            """
        }
    )
    
    df = pd.DataFrame(result['data']['items'])
    
    # Create month-year column for better display
    df['month_year'] = df['year'].astype(str) + '-' + df['month'].astype(str).str.zfill(2)
    
    return df

# Generate trend report
trend_report = create_monthly_trend_report()

Advanced Reporting with Pivots

Create pivot table reports:

def create_department_category_report():
    """Create a pivot report by department and item category."""
    
    result = run_connection_action(
        "netsuite",
        "suiteql",
        {
            "q": """
                SELECT 
                    cf_dept.value as department,
                    ic.name as item_category,
                    COUNT(pol.id) as line_count,
                    SUM(pol.amount) as total_amount
                FROM transactionline pol
                JOIN transaction po ON pol.transaction = po.id
                JOIN item i ON pol.item = i.id
                LEFT JOIN itemcategory ic ON i.category = ic.id
                LEFT JOIN transactioncustomfield cf_dept ON po.id = cf_dept.transaction 
                    AND cf_dept.customfield = 'custbody_department'
                WHERE po.type = 'PurchOrd'
                AND po.trandate >= ADD_MONTHS(CURRENT_DATE, -6)
                AND pol.mainline = 'F'
                GROUP BY cf_dept.value, ic.name
                ORDER BY total_amount DESC
            """
        }
    )
    
    df = pd.DataFrame(result['data']['items'])
    
    # Create pivot table
    pivot_report = df.pivot_table(
        values='total_amount',
        index='department',
        columns='item_category',
        aggfunc='sum',
        fill_value=0
    )
    
    return pivot_report

# Generate pivot report
pivot_report = create_department_category_report()

Export and Visualization

Save reports and create visualizations:

import matplotlib.pyplot as plt
import seaborn as sns
from datetime import datetime

def export_reports():
    """Export all reports to files."""
    
    # Generate all reports
    summary_report = create_po_summary_report('2024-01-01', '2024-12-31')
    vendor_report = create_vendor_performance_report()
    trend_report = create_monthly_trend_report()
    
    # Export to Excel
    timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
    with pd.ExcelWriter(f'po_reports_{timestamp}.xlsx') as writer:
        summary_report.to_excel(writer, sheet_name='PO_Summary', index=False)
        vendor_report.to_excel(writer, sheet_name='Vendor_Performance', index=False)
        trend_report.to_excel(writer, sheet_name='Monthly_Trends', index=False)
    
    # Create visualization
    plt.figure(figsize=(12, 8))
    
    # Monthly trend chart
    plt.subplot(2, 2, 1)
    plt.plot(trend_report['month_year'], trend_report['total_amount'])
    plt.title('Monthly PO Amount Trend')
    plt.xticks(rotation=45)
    
    # Vendor performance chart
    plt.subplot(2, 2, 2)
    top_vendors = vendor_report.head(10)
    plt.barh(top_vendors['vendor_name'], top_vendors['total_amount'])
    plt.title('Top 10 Vendors by Amount')
    
    # PO status distribution
    plt.subplot(2, 2, 3)
    status_counts = summary_report['status'].value_counts()
    plt.pie(status_counts.values, labels=status_counts.index, autopct='%1.1f%%')
    plt.title('PO Status Distribution')
    
    plt.tight_layout()
    plt.savefig(f'po_analytics_{timestamp}.png', dpi=300, bbox_inches='tight')
    plt.show()
    
    print(f"Reports exported with timestamp: {timestamp}")

# Export all reports
export_reports()

Scheduled Reporting

Set up automated report generation:

def setup_automated_reporting():
    """Set up automated daily/weekly reports."""
    
    from datetime import datetime, timedelta
    import schedule
    import time
    
    def daily_report():
        """Generate daily PO summary."""
        yesterday = datetime.now() - timedelta(days=1)
        date_str = yesterday.strftime('%Y-%m-%d')
        
        result = run_connection_action(
            "netsuite",
            "suiteql",
            {
                "q": f"""
                    SELECT COUNT(*) as new_pos, SUM(total) as total_amount
                    FROM transaction 
                    WHERE type = 'PurchOrd' 
                    AND DATE(trandate) = '{date_str}'
                """
            }
        )
        
        data = result['data']['items'][0]
        print(f"Daily Report ({date_str}): {data['new_pos']} POs, ${data['total_amount']:,.2f}")
    
    def weekly_report():
        """Generate weekly vendor performance."""
        vendor_report = create_vendor_performance_report()
        print(f"Weekly Report: Top vendor is {vendor_report.iloc[0]['vendor_name']}")
    
    # Schedule reports
    schedule.every().day.at("09:00").do(daily_report)
    schedule.every().monday.at("08:00").do(weekly_report)
    
    print("Scheduled reporting set up. Press Ctrl+C to stop.")
    
    while True:
        schedule.run_pending()
        time.sleep(60)

# Uncomment to start scheduled reporting
# setup_automated_reporting()

Best Practices

Performance Optimization

# Use pagination for large datasets
def get_large_dataset(query, limit=1000):
    """Handle large result sets with pagination."""
    all_data = []
    offset = 0
    
    while True:
        paginated_query = f"{query} LIMIT {limit} OFFSET {offset}"
        
        result = run_connection_action(
            "netsuite",
            "suiteql",
            {"q": paginated_query}
        )
        
        items = result['data']['items']
        if not items:
            break
            
        all_data.extend(items)
        offset += limit
        
        if not result['data']['hasMore']:
            break
    
    return pd.DataFrame(all_data)

Error Handling

def safe_report_generation(report_function, *args, **kwargs):
    """Safely generate reports with error handling."""
    try:
        return report_function(*args, **kwargs)
    except Exception as e:
        print(f"Error generating report: {e}")
        # Log error details
        # Send notification to admin
        return pd.DataFrame()  # Return empty DataFrame

Troubleshooting

Common Issues

1. Custom field not found:

   • Verify custom field internal IDs
   • Check field permissions
   • Ensure field is available on purchase orders

2. Query timeout:

   • Add date filters to limit results
   • Use pagination for large datasets
   • Consider using REST API for simple queries

3. Missing data:

   • Check data permissions
   • Verify join conditions
   • Add null checks in queries

Next Steps

• Purchase Order Line Items Analysis - Detailed line item reporting

Resources

• NetSuite SuiteQL Reference
• NetSuite Custom Fields Guide
• Abstra Connectors Reference