Skip to main content

Aggregation in Solution 7

Summarizing NetSuite Data with Functions, Lists, Wildcards, and Arrays

Please read our Privacy Statement and Standard License Terms. By downloading any of the documents below, you are agreeing to our Standard License Terms.

NOTE: Solution 7 is migrating their website content and downloads to the Zone Help Center! Update your bookmarks and saved pages now!

  • To download the latest editions of Solution 7, please select the correct edition under Downloads & Releases > Edition Downloads (NetSuite, IRIS, Dream).

Before you start, make sure you're comfortable with the basics of building summarized reports using Solution 7's Lists and Functions (see the Solution 7 Basic Concepts & Building Your First Report guide).

Introduction

Before you begin, there are a few things worth knowing about the exercises in this guide.

NetSuite OneWorld: This guide is written from the perspective of a NetSuite OneWorld user. If you're not running NetSuite OneWorld (because you don't have subsidiaries), you can still follow the exercises, but some dialogs may look slightly different (e.g., missing the Subsidiary field). Wherever this guide refers to Subsidiary, substitute as appropriate for your instance of NetSuite.

Activation: Solution 7 isn't automatically activated when you open Excel — it only needs to be activated when you're building new report templates or refreshing NetSuite data in existing ones. Once activated, one of your Solution 7 licenses is considered in use; closing Excel releases that license for another user.

To activate Solution 7 within Excel: click Solution 7 > Activate.

If you don't see the Solution 7 menu, or activation fails, see the Troubleshooting Guide or contact support.

Solution 7 Ribbon The Solution 7 ribbon expands after activation and is your starting point for every Solution 7 feature.

This guide will help you build NetSuite reports in Solution 7 by grouping accounts from your NetSuite Chart of Accounts (CoA) using wildcards and arrays — whether you want to group by account number, account name, or account type.

  • Grouping by account type is especially useful for reports like your cashflow report, where you need to roll up activity across a whole category of accounts (e.g. all Income or all Expense accounts).
  • Grouping by account number or account name can help with reports like your trial balance or profit & loss (P&L), where you often need to summarize specific accounts or ranges of accounts (e.g. all Sales accounts, or accounts 4000–4999).

This article covers:

  • Using the NetSuite Balance functions (NSGLABAL, NSGLNBAL, NSGLTBAL) to summarize account balances
  • Driving reports dynamically from cell references
  • Building report frameworks with Accounting Lists
  • Aggregating multiple values with cell ranges, wildcards, and arrays
  • Using Named Ranges and INDIRECT to simplify and improve reports

Before you start, make sure you're comfortable with the basics of building summarized reports using Solution 7's Lists and Functions (see the Solution 7 Basic Concepts & Building Your First Report guide).

Accounting Functions

Solution 7 extends Excel's function set with additional functions for pulling live, real-time summarized data from NetSuite. The three Balance functions let you pull values in different ways:

Function Summarizes balance by...
NSGLABAL Account number
NSGLNBAL Account name
NSGLTBAL Account type

 

How to return a summary balance (NSGLABAL)

Steps:

  1. Select an empty cell (e.g. C3), click the Large fx button, and select NetSuite Balances > NSGLABAL.
  2. With your cursor in the Subsidiary argument, click Lookup, select Consolidated from the top of the list, and click OK.
  3. With your cursor in the Account argument, enter the account number (e.g. 4000).
  4. With your cursor in the From Period argument, enter a period (e.g. Jan 2026).
  5. Click OK.

Note: Use the Lookup button to pick a (subsidiary, account, period etc.) value from your NetSuite account.

Solution 7 summarizes all transactions for the chosen account/period combination and returns the result as a normal Excel cell value.

Make your report dynamic

Rather than hard-coding values into the function, you can reference cells directly so the report updates automatically when those cells change. Take a look at the article on Using Solution 7 > Learning the Basics > Building Your First Report for a full dynamic report example.

Accounting Lists

Solution 7 Lists insert live NetSuite reference data — like account numbers or types — directly into Excel, giving you a framework to build reports around. Lists are dynamically linked and can be refreshed at any time.

Three Accounting Lists are available:

  • Account Types
  • Accounts (By Name)
  • Accounts (By Number)

How to insert Accounting Lists

Account Types list:

  1. Add a new worksheet.
  2. In an empty cell, select Column > Account Types.
  3. There are no arguments for this list — click OK to insert all Account Types.

Accounts (By Name) list:

  1. Add a new worksheet.
  2. In an empty cell, select Column > Accounts (By Name).
  3. In the Subsidiary argument, click Lookup, select Consolidated, and click OK — this keeps the list consistent across all subsidiaries.
  4. In the Account Type argument, enter a type (e.g. Income).
  5. Click OK.

Accounts (By Number) list:

  1. Add a new worksheet.
  2. In an empty cell, select Column > Accounts (By Number).
  3. In the Subsidiary argument, click Lookup, select Consolidated, and click OK.
  4. In the Account argument, enter a wildcard pattern (e.g. 4*) to return all matching accounts.
  5. Click OK.

You should have three different sheets, three different ways to return a list of accounts. Rename your sheets so you can easily keep track of which exercise you are doing e.g. NSGLABAL, NSGLNBAL, NSGLTBAL.

Using NSGLABAL, NSGLNBAL, and NSGLTBAL with Lists

Once your lists are in place, you can populate them with summarized balances.

How to summarize by account number (NSGLABAL)

Steps:

  1. In an empty cell next to your Accounts (By Number) list, click the Large fx button and select NetSuite Balances > NSGLABAL.
  2. Select the Subsidiary.
  3. In the Account argument, select the account number cell from the list and press F4 three times to lock it (e.g. $B3).
  4. In the From Period argument, enter a period (e.g. Jan 2026).
  5. Click OK, then copy the formula down to complete the report.

How to summarize by account name (NSGLNBAL)

Steps:

  1. In an empty cell next to your Accounts (By Name) list, click the Large fx button and select NetSuite Balances > NSGLNBAL.
  2. Select the Subsidiary.
  3. In the Account argument, reference the account name cell from the list and press F4 three times to lock the column (e.g. $B3).
  4. In the From Period argument, enter a period (e.g. Jan 2026).
  5. Click OK, then copy the formula down and use Excel's AutoSum to add a total row.

Why use account names?

You may have accounts within your NetSuite CoA that do not have a number assigned, or are designed to be used outside of your normal GL codes. Use the NSGLNBAL function to return balances by these accounts.

How to summarize by account type (NSGLTBAL)

Steps:

  1. In an empty cell next to your Account Types list, click the Large fx button and select NetSuite Balances > NSGLTBAL.
  2. Select the Subsidiary.
  3. In the Account Type argument, select the relevant type cell from the list (e.g. Income).
  4. In the From Period argument, enter a period (e.g. Jan 2026).
  5. Click OK.

Use formulas as a built-in check! 

The value returned by NSGLTBAL for an account type should match the total of the individual account balances returned by NSGLNBAL for accounts of that type — useful as a built-in check on your report.

Account Types for the cashflow

You can use the account types list as a way to aggregate accounts balances using the NetSuite account type groups for your cashflow report.

Working with Multiple Values

Instead of referencing a single argument, Solution 7 lets you reference multiple values at once using cell ranges, wildcard characters, or arrays — increasing both flexibility and the level of aggregation in your report.

Start this exercise using 

How to summarize using a cell range

Steps:

  1. In an empty cell below your accounts (by number) list, click the Large fx button and select NetSuite Balances > NSGLABAL.
  2. Select the Subsidiary.
  3. In the Account argument, select the full range of account numbers from your list (this is treated internally as an array).
  4. In the From Period argument, enter a period (e.g. Jan 2026).
  5. Click OK.

Wildcard characters

A wildcard matches one or more characters in a formula, function, or list argument, so you can pattern-match instead of listing every value individually. Solution 7 supports two wildcard types:

Wildcard Matches
* (asterisk) Multiple characters
? (question mark) Any single character

Using the * wildcard:

  1. In an empty cell below your accounts (by name) list, enter a pattern to match a parent account and its children — e.g. Sales :*.
  2. In an adjacent cell, click the Large fx button and select NetSuite Balances > NSGLNBAL.
  3. Select the Subsidiary.
  4. In the Account argument, reference the wildcard cell and lock it with F4 (e.g. $B12).
  5. In the From Period argument, enter a period (e.g. Jan 2026).
  6. Click OK.

Note: When reporting on account hierarchies, use the * wildcard inside an array formula to avoid missing accounts. For example, replace "Sales :*" with {"Sales","Sales :*"} and press Enter.

In our example, Sales* would also work to summarize all accounts with "Sales" in the account name.

Using the ? wildcard:

  1. In an empty cell below your accounts (by number) list, enter a pattern that matches your accounts in the list. In our example, we use 400? to give a total.
  2. In a separate cell, click the Large fx button and select NetSuite Balances > NSGLABAL.
  3. Select the Subsidiary.
  4. In the Account argument, select your 400? cell.
  5. In the From Period argument, enter a period (e.g. Jan 2026).
  6. Click OK.

Excluding accounts

In our example, you can use 4* or 400? to give a total for all accounts within our income range that we wish to include in our report. Notice that the final two accounts in the list are excluded from the total.

Arrays

An array lets you provide multiple argument values to a single function or list call. When you select a cell range, Solution 7 presents it internally as an array (values in curly braces, separated by commas) — you can also type an array directly in your Excel sheet.

Building a summary with an array via Lookup:

  1. In an empty cell, click the Large fx button and select NetSuite Balances > NSGLNBAL.
  2. Select the Subsidiary.
  3. In the Account argument, click Lookup, hold Ctrl, and select multiple accounts (e.g. Sales, Cost of Goods Sold, Purchases).
  4. Click OK — the accounts are inserted as an array (e.g. {"Sales","Cost of Goods Sold","Purchases"}).
  5. In the From Period argument, enter a period (e.g. Jan 2026).
  6. Click OK.

Entering an array directly in a cell:

  1. In an empty cell, type the array directly — e.g. {"Sales","Cost of Goods Sold","Purchases"}.
  2. In an adjacent cell, click the Large fx button and select NetSuite Balances > NSGLNBAL.
  3. Select the Subsidiary.
  4. In the Account argument, reference the array cell.
  5. In the From Period argument, enter a period (e.g. Jan 2026).
  6. Click OK.

Use arrays with any NetSuite value!

You can use arrays with account numbers, account types, department names, etc. to create groupings. You can use the groupings within a formula to return a balance or budget amount.

Named Ranges and INDIRECT

Named Ranges let you reference a cell range by a friendly name instead of a cell address, which can make reports easier to read and maintain.

How to use a Named Range

Steps:

  1. Select your Accounts (By Name) list.
  2. Using Excel's naming feature, give the range a name (e.g. Income).
  3. In an empty cell, click the Large fx button and select NetSuite Balances > NSGLNBAL.
  4. Select the Subsidiary.
  5. In the Account argument, type the range name (e.g. Income, without quotes).
  6. In the From Period argument, enter a period (e.g. Jan 2026).
  7. Click OK.

Note: This gives you a second way to arrive at the same balance as a direct function reference — useful as a check value in your report.

How to use INDIRECT with a Named Range

Excel's INDIRECT function lets you reference a named range indirectly, via a cell that contains the range's name as text — useful for populating multiple areas of a report from a single source.

Steps:

  1. In an empty cell, type the range name as text (e.g. Sales).
  2. In an adjacent cell, click the Large fx button and select NetSuite Balances > NSGLABAL.
  3. Select the Subsidiary.
  4. In the Account argument, enter INDIRECT($B14) (referencing the cell from step 2).
  5. In the From Period argument, enter a period (e.g. Jan 2026).
  6. Click OK.

Warning: Formulas using INDIRECT are volatile — they recalculate on every change to the workbook, which can slow down large reports.

Grouping with custom segments

Another way to create groupings is by using custom segments within NetSuite. Use a custom segment to group your accounts, departments, regions etc, and integrate the groupings into Solution 7 via a custom adapter. Speak to our team about adding your NetSuite custom segments to your Solution 7 reports.

Summary

Using Solution 7's Balance functions (NSGLABAL, NSGLNBAL, NSGLTBAL) together with Accounting Lists, cell ranges, wildcards, arrays, and Named Ranges/INDIRECT, you can build highly aggregated, dynamic reports from your NetSuite data — with full control over how the data is presented.

Still need help? Contact Support.

Was this article helpful?
Powered by Zendesk