Using Pivot Tables: Live, Refreshable Reports from your NetSuite Transactions

Overview

Solution 7 pivot tables pull NetSuite data straight into a refreshable Excel pivot table. They can replicate your accounting reports and most NetSuite saved searches, as long as you pick the pivot table that matches how the saved search is set up.

Flattened or summary pivot table?

Every pivot table comes in two formats. Both are standard Excel pivot tables with the same menu options; only the layout differs.

Tip: on a flattened pivot table, use the Choose Columns menu to change the layout. The Excel PivotTable Fields pane is for the summary pivot table.

Available pivot tables

Most pivot tables need a subsidiary, an account and an accounting period, just like the Solution 7 formulas. Everything else on the parameter screen is an optional filter.

The Choose Columns menu

Choose Columns is where you shape any Solution 7 pivot table: it controls which NetSuite fields appear, how they are named and the column order. It can be opened from the right-click menu or the main pivot table list arguments menu.

The menu layout

• Available Columns (left-hand side): every NetSuite ODBC database column made available for that pivot table.
• Selected Columns (top right-hand side): every database column selected for the pivot table output and the column order.
• Under "More information": linked (joined) tables, such as Item, Customer or Location. The joins available depend on the pivot table type and will differ depending on the one you choose.
• Filter Columns (bottom right-hand side): database columns selected to filter the pivot table results.

What you can do

Choose Columns is available on both pivot table types, but has different features:

• Flattened pivot table: Choose Columns controls the whole layout, including column order, grouping and sums.
• Summary pivot table: Choose Columns controls which NetSuite fields are pulled in. You then arrange, group and summarise them with the Excel PivotTable Fields pane.

Column order rules (flattened pivot tables)

Grouped columns must sit at the top of the list and summed columns at the bottom. If a summed column is above a grouped one, Solution 7 shows an error when you click OK. This is how Excel pivot tables work, so it can't be changed.

If a field is missing

Most fields on a NetSuite report or saved search are available in Choose Columns. A field can only be shown if NetSuite holds it in the database and the ODBC connection can reach it. If you can't find a field, contact support, who can check whether the development team can add it to the choose columns menu.

Transactions - Posted pivot table

The Transactions - Posted pivot table returns every posted transaction, so it can replicate your accounting reports and many saved searches. It can also show the currency each transaction was posted in alongside the translated amount, which helps with multi-currency reporting.

Example: item receipts by item

This example builds an item receipt report showing the quantity received for each item. It's a common request from companies that report on SKUs.

1. Insert a Transactions - Posted pivot table (flattened is easiest).

   

2. Use the Lookup button to select the Subsidiary (for example, Consolidated), the Account (for example, your Inventory Asset account) and the From Period (the beginning accounting period you would like to pull transactions from).

   

   

   

3. Scroll down the menu to the Transaction Type filter. Use the lookup button to select Item Receipt.

   

4. Click OK. The pivot table returns both sides of each item receipt posted to that account.

5. Right-click the pivot table and go to Solution 7 table > Choose Columns.

   

6. Add Quantity from the transaction line fields by dragging the field to the right-hand menu.

   

7. Under More information > Item, add Item Name/Number from available columns on the left to the selected columns area on the right and move it to the top of the list so it becomes the first column.

   

8. Double-click Item Name/Number, rename it to Item Name, and choose Group - Show Detail.

   

9. Double-click Quantity and choose Sum from the properties menu. Make sure it sits at the bottom of the list with the other summed columns.

   

10. Click OK. The table reformats with quantities and amounts grouped by item.

Use the pivot tables for your P&L reports!

You can replicate your GL reports using the pivot tables. To build a profit/loss, choose the "Pivot Table" format (the non-flattened version) your P&L accounts and the period, then click OK. The figures should match a P&L built with the Solution 7 functions.

Transactions - Aged pivot table (AR/AP aging)

The Transactions - Aged pivot table is built to replicate the NetSuite AR and AP aging reports. If you need an aging report, use this pivot table rather than building one with lists and formulas.

Build an AR aging report

1. Insert a Transactions Aged pivot table using the Flattened Pivot Table format.

2. Choose the Subsidiary and the Account Type: AR for customer invoices or AP for vendor bills.

   

3. Optionally scroll down to filter to specific accounts or dates to age the transactions, then click OK.
4. The raw data shows the Name/ID column (customer names for AR, vendor names for AP), the invoices and payments, and the number of days each item is overdue. 
5. Right-click the pivot table and open Choose Columns to format the transactions to replicate the NetSuite Aged Receivables report.
6. Add the aging buckets you want, for example 1–30, 31–60, 61–90, 91–120 and 121+ days.
7. Rename Name/ID to Customer name (or Vendor name for AP) and group it.

8. Delete any columns you don't need, such as Memo.

   

9. Click OK. Transactions are spread across the buckets, like the NetSuite report.

   

Useful extras

• Transaction currency: for multi-currency companies, add the transaction-currency version of the amount to see how much, for example, your GBP transactions have aged. Useful if your company uses the transaction currency rate instead of the consolidated rate for foreign transactions.
• More customer or vendor fields: under More information you can add fields such as a legal name or internal ID. The internal ID is useful because company names can change.
• Custom aging buckets: each aging bucket is a formula built into the pivot table. From Selected Columns, double-click a bucket to see or edit its formula or use the formula option to create your own calculations.

Inventory Snapshot pivot table

The Inventory Snapshot pivot table replicates the NetSuite report of the same name by showing stock levels as of the moment you pull the data. To report on items over a past period, use the Transactions - Posted pivot table instead.

To use it: choose the subsidiary, optionally look up a specific item, and click OK. The default columns are item, location and quantity. Use Choose Columns to add further fields to match a customised NetSuite inventory snapshot.

Tracking stock over time: pull the Inventory Snapshot regularly (for example, daily or weekly) and save each result. You can then use native Excel charts to show trends.

Budgets pivot table

The Budgets pivot table pulls budget figures rather than actuals. It's a quick way to check that a budget uploaded to NetSuite is correct without building a full report.

1. Insert a Budgets pivot table.

   

2. Choose the Subsidiary and the Budget Category. The budget category is the version of the budget held in NetSuite. Companies with the NetSuite Advanced Financials module can hold multiple budget categories. If your company does not use Advanced Financials, choose the Legacy option.

3. Choose the Accounts (use the * wildcard for all accounts) and the From Period. Scroll down the menu if you wiah to filter the budget result by Class, Department or Location then click OK.

    

4. On the summary version, lay out the table with the Excel PivotTable Fields pane, for example accounts as rows. On the flattened version, use Choose Columns instead.

Under More information in Choose Columns you'll find every dimension a budget can be posted to. This includes custom segments, if you have a Solution 7 custom adapter enabled and use custom segments for budgeting.

Transactions - Non-posting and Statistical pivot tables

These two pivot tables pull transactions that aren't used for GL reporting. They're useful for ad hoc reporting, forecasting and tracking metrics.

Non-posting transactions

This pivot table covers non-posting transactions such as quotes, estimates, sales orders, purchase orders and work orders. Companies often pull non-posting for forecasting, budgeting or board reporting based on open orders.

1. Insert a Transactions - Non-Posting pivot table.

2. Choose the Subsidiary, Account, From Date and To Date.

   

   Dates instead of periods: enter a From/To Date rather than an accounting period. Use the date format of your Excel/Windows settings: for June 2026, "01/06/2026" in the UK format or "06/01/2026" in the US format.

3. Scroll down the menu to the Transaction Type parameter. The lookup lists every transaction type in NetSuite. Choose the non-posting type you need, such as Purchase Order.

   

4. Click Choose Columns to add the Transaction Status field.

   

5. Transaction status: add Transaction status in Choose Columns, then use it as a filter to show, for example, only Fully Billed or Pending Receipt orders.

   

   

• Not included: transactions under "pending approval" status. These aren't held in the database until they're approved.

Statistical transactions

This pivot table pulls statistical transactions, usually statistical journals. They non-currency amounts, e.g. monthly headcount or operational metrics used by manufacturing companies.

Filtering pivot tables

Filtering a flattened pivot table

A summary pivot table has a filter on every column header. A flattened pivot table only has a filter on the first column, but you can use it to filter on any column:

• Click the filter arrow on the first column, then pick the column you want under Select Field.

• Or, quicker: select a cell in the column you want to filter, then click the filter arrow. It defaults to that column.

  

Avoid copying the headers above the table and applying a standard Excel filter. This works until a column is added, then it breaks. Use the pivot table's own filtering instead.

Show filter buttons on every column

You can also make a flattened pivot table show filter buttons on every column, and keep them when the table changes:

1. Right-click the pivot table and open PivotTable Options.
2. On the Totals & Filters tab, tick Allow multiple filters per field.
3. On the Display tab, tick Classic PivotTable layout.
4. Click OK.

Filter the data before it's pulled

To pull less data from NetSuite, add your own filters to the pivot table's parameter screen. This helps when there are many transactions.

1. Right-click the pivot table and open Edit table > Choose Columns.

   

2. Move the field you want to filter on (for example, Transaction: Status) into Filter columns at the bottom, then click OK.

   

3. The new filter appears at the bottom of the parameter screen. Click Lookup to choose the status and click OK.

   

Tip: if a text value looks like a cell reference (for example, an item name beginning with letters followed by numbers, such as AC100), put it in quotation marks so Solution 7 reads it as text.

Best practices and common questions

Are pivot tables refreshable? Yes. Like the Solution 7 formulas, pivot tables refresh with the latest NetSuite data.

Can I copy a sheet with a pivot table and change the copy? We don't recommend it. Copied pivot tables sometimes work, but they can become corrupt later and show errors. To create a new pivot table, always insert a new one.

Should I use pivot tables or formulas? Both give the same figures. Pivot tables are often easier with very large transaction volumes, or when you need granular reports with many transaction fields.

Can I replicate my NetSuite saved search? Usually, yes. Check how the saved search is set up (posted transactions, aging, inventory, budgets or non-posting), choose the matching pivot table, and add the same columns and filters in Choose Columns.

I can't find a field I need. Contact support. If a field isn't in Choose Columns, the team can check whether it's available through ODBC and ask development to add it.

Internal: guidance for the support team

For Confluence, not the customer knowledge base.

• Show pivot tables in every initial training session. Customers find them very useful, and the aim is for everyone on the team to demo them as standard.
• Use a simple P&L as the demo. Build the P&L with the functions first, then pull the same P&L with a Transactions Posted pivot table to show the figures match.
• Tailor anything further. Show specialised reports, such as item receipts, only if the customer asks. Alternatively, ask which NetSuite reports or saved searches they use and try replicating one on the call.
• Point aging questions to Transactions Aged. Don't suggest a list-and-formula approach for AR/AP aging.
• Recommend flattened pivot tables to customers new to Excel pivots.
• Missing fields: search Choose Columns with the customer first. If the field isn't there, raise it with the development team.
• Deeper sessions are by request. Pivot table deep dives aren't part of the standard install and training call; offer one if the customer needs it.