Building Datasets
A dataset is your workbook's data source. Get this right, and everything else flows naturally.
The 60-Second Concept
┌─────────────────────────────────────────────────────────────────┐
│ DATASET = YOUR QUERY │
├─────────────────────────────────────────────────────────────────┤
│ │
│ ┌─────────────┐ ┌─────────────┐ ┌─────────────┐ │
│ │ RECORDS │ + │ FIELDS │ + │ FILTERS │ = DATA │
│ │ (Tables) │ │ (Columns) │ │ (WHERE) │ │
│ └─────────────┘ └─────────────┘ └─────────────┘ │
│ │
│ Transaction Date, Amount, This Month, │
│ + Customer Customer Name, Status = Open │
│ + Item Item SKU │
│ │
└─────────────────────────────────────────────────────────────────┘
Think of it this way: If workbooks are the report, datasets are the SQL query behind it — but you build it visually.
Building Your First Dataset: Step-by-Step
Scenario: Monthly Sales Report
Goal: Show all invoices from this month with customer and item details.
┌─────────────────────────────────────────────────────────────────┐
│ STEP 1: CREATE NEW DATASET │
├─────────────────────────────────────────────────────────────────┤
│ │
│ Analytics Tab → Dataset → New Dataset │
│ │
│ Select Root Record: [ Sales (Invoiced) ▼ ] │
│ ↑ │
│ Use analytical records for │
│ transaction reports (faster!) │
│ │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ STEP 2: ADD FIELDS │
├─────────────────────────────────────────────────────────────────┤
│ │
│ Records Panel Fields Panel Data Grid │
│ ┌──────────────┐ ┌──────────────┐ ┌──────────────┐ │
│ │ Transaction │ → │ □ Date │ → │ Date │ │
│ │ └─Customer │ │ □ Doc Number │ │ Doc Number │ │
│ │ └─Item │ │ □ Amount │ │ Customer │ │
│ │ └─Line │ │ □ Status │ │ Item │ │
│ └──────────────┘ └──────────────┘ │ Amount │ │
│ └──────────────┘ │
│ │
│ Drag fields or double-click to add │
│ │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ STEP 3: ADD FILTERS │
├─────────────────────────────────────────────────────────────────┤
│ │
│ Drag "Date" field to Criteria Builder: │
│ │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ CRITERIA BUILDER │ │
│ │ │ │
│ │ Date [ is within ▼ ] [ This Month ▼ ] │ │
│ │ │ │
│ │ [+ Add Another Filter] │ │
│ └─────────────────────────────────────────────────────────┘ │
│ │
│ This filter affects ALL visualizations using this dataset │
│ │
└─────────────────────────────────────────────────────────────────┘
┌─────────────────────────────────────────────────────────────────┐
│ STEP 4: SAVE │
├─────────────────────────────────────────────────────────────────┤
│ │
│ Name: "Monthly Invoices - Current Month" │
│ Description: "All invoiced sales for reporting" │
│ │
│ [Save] [Save & Close] │
│ │
└─────────────────────────────────────────────────────────────────┘
Real-World Dataset Examples
Example 1: Customer Revenue Analysis
Business Need: Sales team wants to see revenue by customer with YoY comparison.
DATASET STRUCTURE
─────────────────────────────────────────────────────────────────
Root Record: Sales (Invoiced)
Fields Added:
├── Customer → Company Name
├── Customer → Customer Category
├── Transaction → Date
├── Transaction → Amount
└── Transaction → Subsidiary
Filters:
├── Date is within "This Fiscal Year"
└── Type is "Invoice"
Formula Fields:
├── "This Year Amount" → (see formulas page)
└── "Last Year Amount" → (see formulas page)
Result: Dataset ready for customer revenue pivot with YoY comparison.
Example 2: Open Orders Aging
Business Need: Operations needs visibility on unfulfilled orders and how long they've been open.
DATASET STRUCTURE
─────────────────────────────────────────────────────────────────
Root Record: Transaction
Fields Added:
├── Document Number
├── Date
├── Customer → Company Name
├── Status
├── Amount
└── Ship Date
Filters:
├── Type is "Sales Order"
├── Status is any of ["Pending Fulfillment", "Partially Fulfilled"]
└── Main Line is true
Formula Fields:
└── "Days Open" = CURRENT_DATE - {trandate}
└── "Aging Bucket" = CASE WHEN... (see below)
Aging Bucket Formula:
CASE
WHEN CURRENT_DATE - {trandate} <= 7 THEN '0-7 Days'
WHEN CURRENT_DATE - {trandate} <= 14 THEN '8-14 Days'
WHEN CURRENT_DATE - {trandate} <= 30 THEN '15-30 Days'
ELSE '30+ Days'
END
Example 3: Vendor Spend Analysis
Business Need: Finance wants to track spending by vendor and category.
DATASET STRUCTURE
─────────────────────────────────────────────────────────────────
Root Record: Transaction
Fields Added:
├── Vendor → Company Name
├── Vendor → Category
├── Transaction Line → Item
├── Transaction Line → Amount
├── Transaction → Date
└── Transaction → Subsidiary
Filters:
├── Type is any of ["Bill", "Bill Credit"]
├── Date is within "This Fiscal Year"
└── Main Line is false (get line details)
Formula Fields:
└── "Spend Category" = {item.expenseaccount} or custom logic
Example 4: Inventory Health Dashboard
Business Need: Warehouse manager needs to monitor stock levels and turnover.
DATASET STRUCTURE
─────────────────────────────────────────────────────────────────
Root Record: Item
Fields Added:
├── Name / SKU
├── Display Name
├── Quantity On Hand
├── Quantity Available
├── Reorder Point
├── Preferred Stock Level
├── Location → Name
└── Item Category
Filters:
├── Type is any of ["Inventory Item", "Assembly"]
├── Is Inactive is false
└── Location is not empty
Formula Fields:
├── "Stock Status" = (see below)
├── "Days of Stock" = {quantityonhand} / AVG daily usage
└── "Reorder Qty" = {preferredstocklevel} - {quantityonhand}
Stock Status Formula:
CASE
WHEN {quantityonhand} <= 0 THEN 'OUT OF STOCK'
WHEN {quantityonhand} < {reorderpoint} THEN 'REORDER NOW'
WHEN {quantityonhand} < {preferredstocklevel} THEN 'LOW STOCK'
ELSE 'IN STOCK'
END
Joining Records: The Visual Way
Workbooks handle joins automatically when you navigate the record tree.
HOW JOINING WORKS
─────────────────────────────────────────────────────────────────
Instead of writing SQL:
┌─────────────────────────────────────────────────────────────┐
│ SELECT t.trandate, c.companyname, i.itemid │
│ FROM transaction t │
│ JOIN customer c ON t.entity = c.id │
│ JOIN transactionline tl ON t.id = tl.transaction │
│ JOIN item i ON tl.item = i.id │
└─────────────────────────────────────────────────────────────┘
You just click through the tree:
┌─────────────────────────────────────────────────────────────┐
│ Records Panel │
│ ───────────── │
│ ▼ Transaction (root) ← Click to see Transaction fields │
│ ├─► Customer ← Click to see Customer fields │
│ ├─► Transaction Line ← Click to see Line fields │
│ │ └─► Item ← Click to see Item fields │
│ └─► Subsidiary │
└─────────────────────────────────────────────────────────────┘
When you add a field from Customer, the join happens automatically!
Join Performance Rules
| Joins | Performance | Recommendation |
|---|---|---|
| 1-5 | ✅ Fast | Go for it |
| 6-10 | ⚠️ Moderate | Add filters to compensate |
| 10+ | 🔴 Slow | Rethink your approach |
Choosing the Right Root Record
The root record determines everything. Choose wisely.
RECORD SELECTION GUIDE
─────────────────────────────────────────────────────────────────
What are you analyzing?
│
▼
┌───────────────────┐ ┌─────────────────────────┐
│ Sales/Revenue? │────▶│ Sales (Invoiced) │
│ │ │ - Pre-joined for speed │
│ │ │ - Revenue-focused │
└───────────────────┘ └─────────────────────────┘
│
▼
┌───────────────────┐ ┌─────────────────────────┐
│ Orders/Bookings? │────▶│ Sales (Ordered) │
│ │ │ - Order-based metrics │
│ │ │ - Pipeline analysis │
└───────────────────┘ └─────────────────────────┘
│
▼
┌───────────────────┐ ┌─────────────────────────┐
│ Multiple Txn │────▶│ Transaction │
│ Types? │ │ - Most flexible │
│ │ │ - Filter by type │
└───────────────────┘ └─────────────────────────┘
│
▼
┌───────────────────┐ ┌─────────────────────────┐
│ Customer Focus? │────▶│ Customer │
│ │ │ - Entity attributes │
│ │ │ - Join to transactions │
└───────────────────┘ └─────────────────────────┘
│
▼
┌───────────────────┐ ┌─────────────────────────┐
│ Inventory? │────▶│ Item │
│ │ │ - Stock levels │
│ │ │ - Item attributes │
└───────────────────┘ └─────────────────────────┘
Analytical vs Standard Records
| Record Type | When to Use |
|---|---|
| Sales (Invoiced) | Revenue reports, AR analysis |
| Sales (Ordered) | Order volume, bookings |
| Transaction | Multi-type analysis, custom needs |
| Customer/Vendor/Item | Master data analysis |
Pro tip: Analytical records (Sales Invoiced/Ordered) are pre-optimized. Use them for transaction analysis whenever possible.
Filters: Your Performance Best Friend
Filter Types
CRITERIA BUILDER OPTIONS
─────────────────────────────────────────────────────────────────
Text Fields:
├── is / is not
├── contains / does not contain
├── starts with / ends with
└── is empty / is not empty
Number Fields:
├── equals / not equal to
├── greater than / less than
├── between
└── is empty / is not empty
Date Fields:
├── is / is not
├── is before / is after
├── is within (This Month, Last Quarter, etc.)
├── is between
└── is empty / is not empty
List Fields:
├── is any of
├── is none of
└── is empty / is not empty
Filter Patterns for Common Scenarios
| Scenario | Filter Pattern |
|---|---|
| Current month data | Date is within "This Month" |
| Open transactions | Status is any of ["Open", "Pending"] |
| Exclude internal | Customer Category is not "Internal" |
| Main line only | Main Line is true |
| Line details | Main Line is false |
| Active items | Is Inactive is false |
| Specific subsidiary | Subsidiary is "US Operations" |
Combining Filters (AND/OR)
FILTER LOGIC
─────────────────────────────────────────────────────────────────
Default: All filters use AND
Example: Show open invoices over $10K from this quarter
┌─────────────────────────────────────────────────────────────┐
│ Type is "Invoice" │
│ AND │
│ Status is "Open" │
│ AND │
│ Amount is greater than 10000 │
│ AND │
│ Date is within "This Quarter" │
└─────────────────────────────────────────────────────────────┘
For OR logic: Group filters
┌─────────────────────────────────────────────────────────────┐
│ ( Status is "Open" OR Status is "Pending Approval" ) │
│ AND │
│ Amount is greater than 10000 │
└─────────────────────────────────────────────────────────────┘
Templates: Start Here, Customize Later
Don't build from scratch. Use NetSuite's templates.
AVAILABLE TEMPLATES
─────────────────────────────────────────────────────────────────
Analytics → Standard Dataset (or Standard Workbook)
┌─────────────────┬───────────────────────────────────────────┐
│ Template │ Pre-built For │
├─────────────────┼───────────────────────────────────────────┤
│ Sales (Invoiced)│ Revenue, AR, customer spend │
│ Sales (Ordered) │ Orders, bookings, pipeline │
│ Procurement │ Vendor spend, PO analysis │
│ Inventory │ Stock levels, item analysis │
│ Manufacturing │ Production costs, work orders │
└─────────────────┴───────────────────────────────────────────┘
HOW TO USE:
1. Find template in Standard Dataset
2. Click to preview
3. Click "Save As" to create your copy
4. Customize fields, add filters, add formulas
5. Build your visualizations
Sharing & Reusing Datasets
One Dataset, Many Workbooks
DATASET REUSE PATTERN
─────────────────────────────────────────────────────────────────
┌─────────────────────┐
│ "Sales Dataset" │
│ - All invoices │
│ - This fiscal year │
└─────────┬───────────┘
│
┌─────────────────────┼─────────────────────┐
▼ ▼ ▼
┌───────────────┐ ┌───────────────┐ ┌───────────────┐
│ CEO Dashboard │ │ Sales Report │ │ AR Analysis │
│ (Pivot) │ │ (Table) │ │ (Chart) │
└───────────────┘ └───────────────┘ └───────────────┘
Benefits:
✓ Consistent metrics across all reports
✓ Update once, reflects everywhere
✓ Better performance (shared caching)
Sharing Permissions
| Share With | What They Can Do |
|---|---|
| Users | View, use in their workbooks, save copy |
| Roles | All users with that role get access |
| Everyone | Requires Analytics Administrator permission |
Important: Shared users can't edit your original dataset. They can only save their own copy.
Quick Reference: Dataset Checklist
Before saving your dataset, verify:
| Check | Why It Matters |
|---|---|
| ✅ Right root record? | Determines available joins and performance |
| ✅ Only needed fields? | Extra fields slow queries |
| ✅ Filters applied? | No filter = all data = slow |
| ✅ Date range set? | Most reports need time boundaries |
| ✅ Joins under 10? | More joins = slower performance |
| ✅ Descriptive name? | Others need to find and understand it |
What's Next
| Goal | Go To |
|---|---|
| Build visualizations | Tables, Pivots & Charts → |
| Create calculated fields | Formula Fields → |
| Optimize for speed | Best Practices → |