NetSuite Saved Search Library

Curated collection of SuiteQL queries and saved search examples for financial reporting, inventory management, sales analytics, and operations dashboards. These queries demonstrate best practices for NetSuite reporting and data analysis.

About FlowSync Consulting

FlowSync Consulting specializes in NetSuite customization, integration, and automation. These saved searches reflect the reporting approaches we use in client projects.

Contact us for NetSuite consulting services →

Features

• Financial Reports - P&L, balance sheets, cash flow analysis
• Inventory Analytics - Stock levels, aging, turnover, ABC analysis
• Sales Reports - Pipeline, conversion rates, customer analytics
• Operations Dashboards - Order fulfillment, procurement, vendor performance
• Custom Metrics - KPIs and calculated fields
• SuiteQL Examples - Modern SQL-based queries for advanced reporting

Repository Structure

queries/
├── financial/
│   ├── profit-loss.sql        # P&L with period comparison
│   ├── balance-sheet.sql      # Balance sheet snapshot
│   ├── cash-flow.sql          # Cash flow statement
│   └── ar-aging.sql           # Accounts receivable aging
├── inventory/
│   ├── stock-levels.sql       # Current stock by location
│   ├── aging-analysis.sql     # Slow-moving inventory
│   ├── turnover-rate.sql      # Inventory turnover metrics
│   └── reorder-points.sql     # Items below reorder point
├── sales/
│   ├── pipeline-report.sql    # Sales opportunities pipeline
│   ├── win-rate.sql           # Quote-to-order conversion
│   ├── customer-ltv.sql       # Customer lifetime value
│   └── territory-analysis.sql # Sales by territory
└── operations/
    ├── fulfillment-time.sql   # Order-to-ship timing
    ├── vendor-performance.sql # Vendor delivery metrics
    ├── backorder-report.sql   # Backorder analysis
    └── purchase-analysis.sql  # Purchase order trends

Quick Start

Option 1: Import as Saved Search

1. Navigate to Lists > Search > Saved Searches > New
2. Select appropriate record type (Transaction, Item, Customer, etc.)
3. Copy SQL from repository
4. Configure in NetSuite UI or use SuiteQL

Option 2: Use in SuiteScript

/**
 * @NApiVersion 2.1
 */
define(['N/query'], (query) => {
    const sql = `
        SELECT
            customer.companyname,
            transaction.trandate,
            SUM(transaction.amount) as total_sales
        FROM transaction
        INNER JOIN customer ON transaction.entity = customer.id
        WHERE transaction.type = 'SalesOrd'
        GROUP BY customer.companyname, transaction.trandate
    `;

    const results = query.runSuiteQL({ query: sql }).asMappedResults();
    return results;
});

Option 3: Export for External BI Tools

• Queries optimized for export to Tableau, Power BI, or Excel
• CSV export formatting included
• Date range parameterization

Example Queries

Top 10 Customers by Revenue (This Year)

SELECT
    customer.entityid AS customer_id,
    customer.companyname AS company_name,
    SUM(transactionline.netamount) AS total_revenue
FROM
    transactionline
    INNER JOIN transaction ON transactionline.transaction = transaction.id
    INNER JOIN customer ON transaction.entity = customer.id
WHERE
    transaction.type = 'CustInvc'
    AND EXTRACT(YEAR FROM transaction.trandate) = EXTRACT(YEAR FROM CURRENT_DATE)
GROUP BY
    customer.entityid,
    customer.companyname
ORDER BY
    total_revenue DESC
LIMIT 10

Inventory Turnover Rate (Last 12 Months)

SELECT
    item.itemid,
    item.displayname,
    SUM(transactionline.quantity) AS units_sold,
    AVG(inventorybalance.quantityonhand) AS avg_inventory,
    CASE
        WHEN AVG(inventorybalance.quantityonhand) > 0
        THEN SUM(transactionline.quantity) / AVG(inventorybalance.quantityonhand)
        ELSE 0
    END AS turnover_rate
FROM
    transactionline
    INNER JOIN transaction ON transactionline.transaction = transaction.id
    INNER JOIN item ON transactionline.item = item.id
    LEFT JOIN inventorybalance ON item.id = inventorybalance.item
WHERE
    transaction.type = 'SalesOrd'
    AND transaction.trandate >= ADD_MONTHS(CURRENT_DATE, -12)
GROUP BY
    item.itemid,
    item.displayname
ORDER BY
    turnover_rate DESC

Accounts Receivable Aging

SELECT
    customer.entityid AS customer_id,
    customer.companyname AS company_name,
    SUM(CASE WHEN transaction.daysopen BETWEEN 0 AND 30 THEN transaction.amountremaining ELSE 0 END) AS current,
    SUM(CASE WHEN transaction.daysopen BETWEEN 31 AND 60 THEN transaction.amountremaining ELSE 0 END) AS days_31_60,
    SUM(CASE WHEN transaction.daysopen BETWEEN 61 AND 90 THEN transaction.amountremaining ELSE 0 END) AS days_61_90,
    SUM(CASE WHEN transaction.daysopen > 90 THEN transaction.amountremaining ELSE 0 END) AS over_90,
    SUM(transaction.amountremaining) AS total_due
FROM
    transaction
    INNER JOIN customer ON transaction.entity = customer.id
WHERE
    transaction.type = 'CustInvc'
    AND transaction.status = 'CustInvc:A' -- Open invoices
GROUP BY
    customer.entityid,
    customer.companyname
HAVING
    SUM(transaction.amountremaining) > 0
ORDER BY
    total_due DESC

Query Optimization Tips

1. Use SuiteQL for Complex Queries - More powerful than saved search UI
2. Index Fields - Query on indexed fields (entityid, tranid, trandate)
3. Limit Result Sets - Use WHERE clauses to filter early
4. **Avoid SELECT *** - Specify only needed columns
5. Test Governance - Check script execution time and usage units

Use Cases

Financial Reporting

• Month-end close reports
• Budget vs actual analysis
• Multi-subsidiary consolidation
• Tax reporting and compliance

Inventory Management

• Reorder point alerts
• Dead stock identification
• Location transfer recommendations
• Serialized item tracking

Sales Analytics

• Territory performance
• Product mix analysis
• Customer segmentation
• Sales forecasting data

Operations Metrics

• Fulfillment cycle time
• Purchase order accuracy
• Vendor lead time tracking
• Backorder reduction analysis

Best Practices

1. Parameterize Date Ranges - Make queries reusable across periods
2. Document Business Logic - Explain calculation methodology
3. Test with Production Data - Verify accuracy with real datasets
4. Set Appropriate Permissions - Restrict sensitive financial data
5. Schedule Regular Exports - Automate reporting workflows

Contributing

Have a useful saved search? Submit a pull request with:

• SQL query file
• Documentation explaining use case
• Example output (sanitized data)
• Any special configuration notes

License

MIT License - see LICENSE file for details.

Contact

• Website: FlowSync Consulting
• Email: contact@flowsync-preview.internal
• GitHub: @FlowSync-Consulting

---

Status: 🚧 Work in progress - queries being added incrementally

Roadmap:

• Financial reports (10+ queries planned)
• Inventory analytics (8+ queries planned)
• Sales reporting (12+ queries planned)
• Operations dashboards (6+ queries planned)
• Custom KPI examples (planned)