Multi-Book Reporting in Solution 7: Balances, Budgets and Pivot Tables

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: Multi-Book Reporting will be available with the Fall.13 version that is currently available for preview only (testing). The full release is coming soon.

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

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

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

Overview

If your NetSuite account uses more than one accounting book (a primary book and a secondary or adjustment book) Solution 7 lets you report on any of them directly. Rather than being a separate tool, multi-book has been built into the features you already use: Functions, Pivot Tables, and Budget Upload.

NetSuite's multi-book accounting feature lets a company keep more than one set of books for the same underlying transactions, and using Solution 7 for your reporting means you can build reports against any of your accounting books without switching tools. A secondary book is often used where different regions or entities need to report under different accounting standards — for example, a US parent company reporting under US GAAP in its primary book, while a subsidiary in another region also needs figures under IFRS or local GAAP in a secondary book. An adjustment book, on the other hand, is typically used to record adjustments made throughout the year — for example, consolidation or elimination entries — without altering the primary book's figures. 

Being able to report on any of these books directly from Excel means you're not limited to reporting only on your primary book, or having to piece together adjustments manually.

This article covers:

• Returning balances for a specific accounting book using the Advanced Balances functions
• Reporting on transaction lines posted to a specific accounting book using a flattened pivot table
• Uploading a budget to a secondary or adjustment book

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

Reporting Balances by Accounting Book

To report on balances by different accounting books, use the Advanced Balances functions.

1. Insert a Subsidiary pop-up list and an Accounting Book pop-up list. Using cell references in the formula will make it easier to build a dynamic report.

   

2. Insert a column list of accounts as normal (e.g. 4* for income codes), and type in the accounting period(s) you want to report on.

   

3. Select an empty cell and open the function menu to choose an Advanced Balances function — any of the available categories will work, but a function that returns a balance by accounting period and account number (NSGLAPBAL) is a good starting point.
4. Select the Subsidiary pop-up list cell.

5. Reference the Account and From Period cells from your sheet.

   

6. In the Option 1 argument, click Lookup and choose Accounting Book.
7. In the Value 1 argument, choose Primary Accounting Book (or reference the cell containing it, if it's already on your sheet).

8. Click OK, then copy the formula down to get your totals.

   

Dynamically update the accounting book!

Because the formula references your Accounting Book pop-up list, you can toggle which book it reports on at any time.

This gives you balances only. If you need to see the individual transaction lines that make up a secondary book balance, use a pivot table instead — covered next.

Reporting Transactions by Accounting Book with Pivot Tables

If you want to see the individual transaction lines posted to a specific accounting book, rather than just a total balance, the best approach is to use a flattened pivot table.

1. Open a new sheet in the workbook and insert a Subsidiary pop-up list and an Accounting Book pop-up list, the same as above.
2. In an empty cell, enter the accounting period you want to report on.
3. Open the pivot table menu and choose Flattened pivot table — this shows the breakdown of transaction lines for a given subsidiary, account, period, and accounting book, rather than a summarized total.

4. Choose Transactions - Posted as your table type. 

   

5. Select the Subsidiary pop-up list cell.
6. Enter your account argument (e.g. 4* for income codes).

7. Choose your From Period from the sheet.

   

8. Scroll down the list argument screen to the optional Accounting Book parameter, and select it from your sheet.

   

9. Click OK.

Solution 7 will pull through every transaction line for the subsidiary, accounts, period(s), and accounting book you selected.

Adding the Accounting Book column

By default, the pivot table won't automatically include a column to identify which accounting book the transactions belong to. You'll need to bring it in via Choose Columns.

1. Right-click the pivot table and go to the Solution 7 Table menu.

   

2. Click Edit Table... > Choose Columns.

   

3. In the available columns area, scroll down to More Information, then Transaction Accounting Line — this is the ODBC table the accounting book field lives on.

4. Scroll down again to More Information on the Transaction Accounting Line table, and open the Accounting Book table.

   

5. Double-click or click and drag the Accounting Book Name column into the "selected columns" area.

   

6. Click OK on the choose columns screen, and then click OK again on the next window.

Modify your pivot tables any time!

You can rearrange the columns afterward so the accounting book name appears wherever is easiest to read — for example, as the first column, so it's immediately clear which book each transaction line is coming from.

Once the column is added, switching the Accounting Book pop-up list to a different book (e.g. Secondary Accounting Book) will refresh the whole pivot table, pulling through the transactions for that book instead.

Uploading Budgets by Accounting Book

Budget upload now supports Multi-Book too. Previously, you could only upload a budget to the primary book — you can now upload to a secondary book, an adjustment book, or any other accounting book, separate from the primary book.

The process is exactly the same as any other budget upload, with one difference: an extra Accounting Book field appears alongside your other required fields on the Map Worksheet(s) screen. You don't need a separate template per accounting book — a single template can upload to either the primary or secondary book, just by changing the Accounting Book pop-up list before mapping and uploading.

For the full mapping and upload walkthrough, see the Budget & Forecast Upload guide.

Summary

Multi-Book reporting hasn't introduced a separate tool — it's been built directly into the Balance functions, pivot tables, and budget upload features you already use. Wherever you previously saw an option for Subsidiary, Account, or Period, you'll now also see an option for Accounting Book, letting you report on and upload to your primary book, secondary book, or any other accounting book set up in NetSuite.

Still need help? Contact Solution 7 Support.