Solution 7 Functions and Formulas for Flexible Report Templates

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).

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, 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.

Overview

This article covers a set of Solution 7 techniques that go beyond the standard training session. They are useful whenever you need more flexibility in how you report on your NetSuite data — whether that's grouping accounts, reporting over specific dates, looking up NetSuite reference data, or adding extra fields to a report.

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 guide). This article assumes your NetSuite chart of accounts is set up in a logical, hierarchical way (e.g. balance sheet accounts begin with 1, 2 and 3, revenue accounts begin with 4, expense accounts begin with 5, 6 etc.)

Grouping Accounts with Wildcards

Wildcards allow you to get totals for a whole group of accounts without building out a full list-based report — just by using a cell reference.

The asterisk (*) wildcard

The asterisk matches any number of characters and is the most common way to group accounts by a shared prefix (for example, 4* for total revenue). For a full walkthrough of the asterisk wildcard — including combining multiple patterns with arrays — see the Solution 7 Basic Concepts & Building Your First Report guide.

The question mark (?) wildcard

The question mark matches any single character, which is useful when accounts are split by the number of digits in the account number — for example, if summary accounts contain 3 digits but the child accounts you actually want to report on have 4 digits.

To return only 4-digit accounts beginning with 4, enter 4??? (four characters total: the digit 4 followed by three question marks) as the account parameter.

1. Select an empty cell and enter 4???
2. Open the function menu and choose the NetSuite Balances function NSGLABAL to return balances by account number.
3. Use the Lookup button to select the Subsidiary.
4. With your cursor in the Account argument, select the cell containing 4???
5. Lookup or enter the reporting period(s) and click OK.

You can narrow the results further by replacing one or more of the question marks with fixed digits. For example, 40?? returns only 4-digit accounts that begin with 40 (rather than every 4-digit account beginning with 4), since two digits are now fixed and only the last two are left as wildcards.

At the other extreme, using only question marks with no fixed digit — ???? — returns every 4-digit account regardless of what it begins with, since nothing is fixed and all four characters are treated as wildcards.

Use wildcards in lists too!

Wildcards aren't limited to Balance functions — you can also use them directly in a Solution 7 list. For example, entering ???? as the account argument on an Accounts (By Number) list will return a list of every account with a 4-digit number, the same way it would return a total in a Balance function. This is useful when you want to see the individual matching accounts themselves, rather than just a combined total.

The signage is wrong, what do I do?

Where a report combines revenue and expense into a single total (e.g. gross profit), whether the result displays as positive or negative depends only on Excel's formatting choices, not on the NetSuite UI report — NetSuite records revenue and expense transactions with opposite signage, and Solution 7 pulls that raw data through unchanged. You can adjust the display sign in Excel to match how you want the report to read.

Returning Balances by Account Name and Account Type

Alongside grouping by account number, Solution 7's Balance functions can also summarize by account name or account type — useful when an account doesn't have a number assigned, or when a customer wants to replicate a standard report format (like a cash flow) that's built around account types rather than individual account numbers.

Account name (NSGLNBAL)

Reporting by account name is most useful when an account doesn't have a number assigned, or when you want a report that's easy to read at a glance without cross-referencing account numbers.

1. Open the function menu and choose the NetSuite Balances function NSGLNBAL.

   

2. Select the Subsidiary.
3. With your cursor in the Account argument, click Lookup and choose the account name (e.g. Sales).
4. Enter the reporting period(s) and click OK.

As with account numbers, you can use the asterisk wildcard to summarize multiple accounts by name — for example, Sales* would total every account whose name begins with "Sales."

However, reporting by account number is generally easier when it comes to grouping accounts into hierarchies. Account numbering conventions are far more standardized than naming conventions — a company will typically number all its sales accounts within a consistent range, but there's no guarantee every sales account will actually have the word "Sales" in its name. Since a name-based wildcard like Sales* will only catch accounts that happen to follow that naming pattern, it's easy to miss accounts that are functionally part of the same group but named differently. Account number ranges or account types tend to be a more reliable way to capture a full group of accounts.

Typos cause trouble

Account names must be typed exactly as they appear in NetSuite. A typo (for example, an extra letter) will return a zero rather than an error, so it's worth double-checking against the account list if a result looks off. You can look up the correct account names at any time via the account list's Lookup option, without needing to check NetSuite directly.

Account type (NSGLTBAL)

This is especially useful for replicating a cash flow report, which NetSuite typically builds around account types rather than individual accounts.

1. Type the account type name directly into a cell (e.g. Accounts Receivable, Bank, Expense, Income, Cost of Goods Sold).
2. Open the function menu and choose the NetSuite Balances function NSGLTBAL.
3. Select the Subsidiary.
4. In the Account Type argument, reference the cell containing the account type name.
5. Enter the reporting period(s) and click OK, then copy the formula down for each account type.

Enter the parameters as text

Account types rarely change once set up in NetSuite, so typing them directly into a cell is generally preferable to inserting a full Account Types list. Having fewer lists in a workbook also helps keep file size down and reduces refresh time.

Reporting by Transaction Date

The standard NetSuite Balances functions summarize by posting period (e.g. a calendar month). The Advanced Balances functions provide additional flexibility by allowing you pull balances by a specific date range — useful for tracking daily or weekly sales, or any other transactional data that needs to be reported at a finer level than a full month.

1. Open the function menu and choose one of the Advanced Balances by Date functions.

   

2. Select the Subsidiary and Account (by number, name, or type — the same options as the standard Balance functions).
3. In the From Date argument, enter the start of the range (e.g. 01/01/2026).
4. In the To Date argument, enter the end of the range (e.g. 07/01/2026.
5. Click OK, then copy the formula down or across as needed.

See how the formula name changes when returning balances by date using account types:

Transaction date vs posting period

This function looks at the transaction date on an entry, not the posting period. A transaction dated January 1st will be included in a January 1–7th report even if it was posted later (for example, in February).

Enter a full date range

Both a From Date and a To Date must be entered if you wish to get a weekly balance. If only a From Date is entered, the function will only return transactions for that single day.

Date formatting matters!

The date format Excel expects here depends on your Windows regional settings, not on Solution 7 itself — for example, a US-based setup would read 01/07/2026 as January 7th, while a UK/EU-based setup would read the same entry as July 1st. Check your Windows date format if a report seems to be picking up the wrong date.

Report by Non-posting and Statistical Transactions

Each function covered above will summarize all posted transactions — the transactions recorded for standard month-end accounting. NetSuite also supports non-posting and statistical transactions, which not every company uses, but can be useful for tracking additional metrics.

"Non-posting" refers to a transaction type that never hits the general ledger by design (for example, a sales order or purchase order) — it does not refer to a transaction that is simply awaiting approval before posting (status: Pending Approval). A transaction in pending approval will post to the GL once approved and is still a posting transaction; it will not appear in the non-posting or statistical Balance functions covered here.

• Non-posting transactions include sales orders or purchase orders.
• Statistical transactions are typically recorded as statistical journals — a standard NetSuite transaction type used to track quantities or other metrics that aren't purely financial. E.g. headcount.

Both non-posting and statistical entries can be returned and summarized using their own dedicated balance functions, following the same logic as the others — by account number, name, or type, and optionally by date.

Transaction Types

The Advanced Balances functions include an Option/Value pair of arguments that let you filter results down to specific NetSuite transaction types — for example, Purchase Orders or Sales Orders — rather than returning every non-posting transaction that hits an account. 

1. Select a cell and enter Purchase Order or Sales Order. If you don't use purchase orders or sales orders in NetSuite, choose a different transaction type.

2. Open the function menu and choose one of the Advanced Balances (Non-Posting) functions.

3. Select the Subsidiary and Account (by number, name, or type — the same options as the standard Balance functions).

4. Choose either a From Period or a From Date and To Date depending on which function you chose.

5. In the Option 1 argument, click Lookup and choose "Transaction Type" from the list.

6. In the Value 1 argument, select the Purchase Order/Sales Order cell and click OK.

   

Isolating specific transactions types is useful when an account is shared by more than one transaction type. For example to see purchase order commitments separately from sales order activity on the same account.

The Advanced Balances (Statistical) functions will only return a balance of transactions determined of "statistical" type in NetSuite.

Using Lookup Functions to Return Different Fields

NetSuite lookup functions (found under NetSuite Lookups in the function menu) follow a general pattern: pick a record type (e.g. Account, Subsidiary), tell the function which field to filter on then tell it which field value to return. This makes them useful for far more than just balances — you can use them to return a list of values from different fields on the same record. E.g. you can return a list of currencies based on the subsidiary records you have setup in NetSuite.

Important note: Lookup functions are only available for native NetSuite records — Account, Class, Location, Department, Customer, Vendor, and so on. Custom segments cannot have a dedicated lookup function. However, custom fields built on native records will appear in the lookup field options.

Returning a list of accounts by type

1. Open the function menu, go to NetSuite Lookups, and choose the account lookup function NSACCOUNT.

   

2. Select the Subsidiary using the Lookup button.

3. Set the "Filter Field" to Account Type Name and the "Filter Value(s)" parameter to the account type you want (e.g. Income).

   

4. Set the "Return Field" to Account No. (or Account Name, if you'd prefer names instead of numbers).

   

5. Click OK. The function returns a spill list of all matching accounts under the type "income".

   

Auto update: Formulas vs Lists

Because this is a live formula rather than a static list, it automatically refreshes whenever Excel is opened and Solution 7 is in use. If account names or numbers change in NetSuite, or new accounts are added, the lookup formula will automatically update. A list-based report, by contrast, needs a manual refresh (right-click and refresh, or refresh from the ribbon) to pick up the same changes.

Returning an account internal ID

Balances can only be returned by account number, account name, or account type — not by internal ID. However, internal IDs can still be displayed for reference using a lookup function, which is useful since internal IDs don't change even if an account number or name does.

1. Copy/paste the list of account numbers as text in a new column.
2. Open the function menu, go to NetSuite Lookups, and choose NSACCOUNT.
3. Select the Subsidiary.
4. Set the "Filter Field" to Account Number, and the "Filter Value(s)" parameter to the account you want to use.

5. Set the "Return Field" to Account ID to return the internal ID of the account number.

   

   

6. Click OK.

   

Account IDs don't change if your account number or name does!

Account IDs are worth displaying in a report even though you can't build a balance directly from them, because they give you a stable reference point if an account is renamed or renumbered. This is particularly useful for reports that need to stay reliably linked to a specific account over time — for example, a report that cross-references a list of accounts against another data source by ID, or a check column used to confirm a formula is still pointing at the account you expect, even after a renumbering project in NetSuite. 

Displaying a subsidiary short name from its full name

The same filter/return pattern applies to subsidiary records - useful when you want to use the subsidiary name to find the currency, hierarchy level or internal ID. 

NetSuite subsidiaries are displayed by their full name in Solution 7, which includes the entire parent hierarchy (for example, Parent Company : Region : Subsidiary) — useful for NetSuite's own record structure, but not always the clearest way to label a report. You can use the same lookup pattern to pull the subsidiary's short name or internal ID, making it easier for anyone reading the report to identify the subsidiary.

1. Select an empty cell and insert a Pop-up list of subsidiaries.

   

2. In the empty cell next to your Pop-up list, insert the lookup function NSSUBSIDIARY.

   

3. Put your cursor into the Subsidiary parameter and select the Pop-up list cell.
4. Set the "Return Field" to Subsidiary Name.
5. Click OK. 

If your Pop-up list is set to the consolidated subsidiary, the NSSUBSIDIARY lookup function will return a list of all the (short) subsidiary names under that consolidated hierarchy. Select a different subsidiary from the drop-down and see the lookup function return the short name.

Identifying the subsidiary currency

NetSuite subsidiaries can operate in different base currencies, and it isn't always obvious which reporting currency is being used in a multi-subsidiary/currency workbook. You can use the same lookup pattern to pull a subsidiary's currency from its legal name, making it easy to confirm which currency a report's figures are being pulled in.

1. In the empty cell next to the subsidiary short name, insert another NSSUBSIDIARY lookup function.
2. Put your cursor into the Subsidiary parameter and select the subsidiary full name.
3. Set the filter field to Subsidiary Name, and the filter value to the subsidiary's short name.
4. Set the return field to Currency Symbol.

5. Click OK.

   

   

Once you know a subsidiary's currency, you can use it directly to control the currency a report is displayed in. The parent currency parameter (NSGLABAL) or subsidiary context parameter (NSGLAPBAL) let you specify a currency for a report by referencing the subsidiary's full name, rather than the report simply defaulting to the subsidiary's base currency.

This method is only useful when the report is using the subsidiary base currency. If you are reporting by transaction currency see the Multi-Currency Reporting guide.

Adding Extra Fields with Choose Columns

Beyond returning balances and account lists, Solution 7 lets you bring in additional fields onto any list using the Choose Columns menu. These are the same fields you'll see in the "Filter Field" options on the lookup functions covered above — NetSuite ODBC database fields that Solution 7 makes available for you to work with. Choose Columns lets you display the same fields as columns in a list or in a pivot table, rather than filtering or looking up a single value with them.

1. Insert an accounts list as normal (e.g. Accounts by Number, filtered to revenue codes).
2. Open Choose Columns to view the available database fields.

3. Double-click a field from the available columns on the left, to make the field appear in selected columns on the right.

   

4. Click OK to insert the full list of selected fields into your Excel sheet.

The list will return every account paired with every value of the added field (e.g. every account paired with every department), which you can then filter or refine as needed to build the combination you want.

Custom fields appear in choose columns! 

Custom fields in NetSuite work the same way as default fields — if you are using a custom adapter, your custom fields will appear under "filter values" on the Lookup Functions and in the List and Pivot Table Choose Columns menus.

Summary

Wildcards, name/type-based Balance functions, NetSuite lookup functions, Advanced Balances by Date, and the Choose Columns option all provide flexible ways to group, summarize, and enrich NetSuite data in Solution 7 without needing to build out a full list-and-function report every time. Non-posting and statistical transaction functions extend this further for companies tracking sales orders, purchase orders, or statistical journals.

Still need help? Contact Solution 7 Support.