Multi-Currency Reporting

Reporting by Multiple Currencies in Solution 7: Subsidiary Currency, Transaction Currency, Bank Account Currency and Exchange Rates

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

Overview

If you work across multiple subsidiaries, you'll often have a specific reporting requirement tied to currency — translating a report from a subsidiary's base currency into the parent company's currency, reporting your bank accounts in their own account currency rather than the subsidiary currency, or identifying transactions posted in one specific currency within a subsidiary that trades in several. Solution 7's Balance functions let you report in any of these currency contexts directly. Not only can you report amounts in each of these contexts, you can also control which NetSuite exchange rate — consolidated, average, current, historical, or budget — is used to get there. Where you need more granular detail than a summarized balance, a pivot table lets you see the individual transactions themselves, identified by both the transaction (document) currency they were posted in and the subsidiary currency equivalent.

This article covers:

• Reporting in the base currency of a subsidiary.
• Translating foreign currency amounts.
• Reporting by account currency.
• Reporting by transaction currency.
• Reporting by different exchange rates.
• Viewing transactions in multiple currencies.

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 by Subsidiary Base Currency

By default, a Balance function reports in the base currency of whichever subsidiary you've selected.

1. Insert a Subsidiary pop-up list.
2. Select an empty cell, open the function menu, and choose a NetSuite Balances function — for example, NSGLABAL to report by account number.
3. Select the Subsidiary from your pop-up list.
4. Enter an Account number.
5. Enter an Accounting Period.
6. Click OK.

If your Subsidiary pop-up list is set to the consolidated subsidiary, the result is the roll-up amount across every subsidiary in that consolidated hierarchy, reported in the parent company currency.

If you change the pop-up list to a single subsidiary instead (e.g. Mexico) the amount is returned in the new subsidiary base currency (Mexican Pesos). Changing the pop-up list again to a different subsidiary (e.g. UK) returns the amount in GBP instead.

If you wish to change the amount from the subsidiary base currency to the global reporting currency, see the next section.

FAQ: Why have the formulas shown #N/A result?

Solution 7 formulas may show #N/A when first inserted or refreshed. This is normal and just means the formula is loading the amount(s) from NetSuite.

Translating to the Parent Currency

If you want to see one specific subsidiary's figures in the parent company currency you can use the Parent Currency parameter within the formula. 

1. Copy the balance formula down by one cell.

2. Use the small fx button to open the function arguments screen.

   

3. Scroll down to the Parent Currency parameter.

4. Click Lookup and select the parent company.

   

   

5. Click OK.

   

In this example, we have translated the income from the Mexico subsidiary from pesos into USD for reporting at the global level.

Parent currency for parent companies

This parameter is specifically for translating into a parent subsidiary's currency — it will only work when you choose from parent subsidiaries in your NetSuite hierarchy, not any subsidiary. To translate into a non-parent subsidiary's currency, see the next section.

Translating to a Different Subsidiary Currency

If you want to translate a subsidiary's amount into the currency of a different subsidiary that isn't its parent — for example, a child or sibling subsidiary elsewhere in the hierarchy — you'll need to use the Advanced Balances functions instead.

1. Open the function menu and choose an Advanced Balances function — for example, NSGLAPBAL to report by account number.
2. Select the Subsidiary you want to report on (e.g. USA).
3. Enter your Account and From Period.
4. In the Option 1 argument, click Lookup and choose Subsidiary Context.
5. In the Value 1 argument, click Lookup and select the subsidiary whose currency you want to translate into — any subsidiary in your NetSuite hierarchy will work here, not just a parent.
6. Click OK.

The result takes the subsidiary amount (e.g. USA income) and applies the currency rate of whichever subsidiary you selected in Value 1 (e.g. Mexico), rather than the reporting subsidiary base currency or the parent currency.

This is particularly useful where a NetSuite subsidiary structure has multiple child subsidiaries in different currencies all rolling up to the same parent, rather than a separate parent company set up for each child in its own currency.

Reporting by Transaction Currency

If a subsidiary has transactions posted in more than one currency — for example, a USA subsidiary with a mix of USD, GBP, and Mexican peso transactions — you can isolate the transactions posted in one specific currency and get a summarized balance.

1. Open the Advanced Balances function, select your Subsidiary, enter your Account, and enter your From Period.
2. In the Option 1 argument, click Lookup and choose Transaction Currency.
3. In the Value 1 argument, click Lookup. Solution 7 will pull through every transaction currency posted in your NetSuite account — choose the one you want (e.g. USD).
4. Click OK.

The result is a balance made up only of transactions posted in that currency. If a subsidiary has no transactions in the currency you've chosen, the result will be zero — this doesn't necessarily mean anything's wrong, just that there's nothing to report for that combination.

Reporting by Account Currency

In NetSuite, GL accounts are in the base currency of their subsidiary — except bank accounts, which can be set up in any currency you like. To report a bank account in its own account currency, rather than the subsidiary's base currency, use the Result Basis option.

1. Open the Advanced Balances function as before, and select the Subsidiary you want to report on.
2. In the Account argument, enter your bank account code(s).
3. Enter your From Period.
4. In the Option 1 argument, click Lookup and choose Result Basis.
5. In the Value 1 argument, click Lookup and choose Account Currency.
6. Click OK.

Rather than applying the subsidiary currency, the result applies whichever currency has been set on each bank account in NetSuite, giving you the true bank account balance in its own currency.

This is particularly useful when you're reporting at the consolidated subsidiary level, but still want to see your bank account activity in it's own currency — for example, if your global reporting currency is USD but you have a Euro bank account, and you want that account's balance shown in Euros rather than translated into USD.

Changing the Exchange Rate

Every currency translation performed by Solution 7 uses one of NetSuite's exchange rate types. By default, this is determined by NetSuite, but you can also choose it explicitly using the Result Basis option.

On NetSuite's currency exchange rate page, you can set several different rates: consolidated, average, current, and historic, as well as a budget rate. Selecting a rate type in your formula lets you control exactly which one a report uses.

1. In the Option 1 argument, click Lookup and choose Result Basis.
2. In the Value 1 argument, click Lookup and choose the rate type you want — for example, Average, Current, or Historic.

3. Click OK.

   

   

Use result basis to show a quantity value rather than a currency value!

You can also use Result Basis to report by quantity rather than value — useful if you want to see the number of items sold, for example, rather than a currency balance for those sales.

Budget Rates

NetSuite maintains a separate Budget Exchange Rates table (distinct from the Consolidated Exchange Rates used for actuals) specifically to translate budget amounts from child to parent subsidiaries for consolidated budget reporting.

Currency isn't something you set directly when mapping or uploading a budget in Solution 7 — it's determined entirely by NetSuite's own Budget Category setup. Every budget category has a type, set in NetSuite, of either:

• Global — the budget is defined in the base currency of the root parent subsidiary.
• Local — the budget is defined in the base currency of the subsidiary you're uploading to (OneWorld only).

This means the amounts on your upload template need to already be in whichever currency the budget category you're mapping to expects. If you're uploading to a local budget category for a Mexican subsidiary, for example, your template amounts need to be in Mexican pesos; if you're uploading to a global category, they need to be in the parent's currency instead, regardless of which subsidiary you're uploading to.

Budget rates are set up and maintained in NetSuite directly, and requires the Currency permission at Full level to manage — it isn't something you configure from within Solution 7.

Reporting Budget Values by Rate

The same rate-type logic applies to budget values, using the Advanced Budgets functions.

1. Open the function menu, go to NetSuite Budgets, and choose an Advanced Budgets function — for example, NSGLAPBUD.

   

2. Click Lookup to select the Subsidiary, Budget Category, Account, and From Period.
3. In the Option 1 argument, click Lookup and choose Result Basis.
4. In the Value 1 argument, click Lookup and choose the rate you want to report the budget in — consolidated, average, current, or historic.
5. Click OK.

A budget rate is especially useful for forecasting. You can set a future budget rate in NetSuite, upload your budget amounts, and later, when you're building variance reports, apply that same budgeted rate to your actuals for an accurate comparison.

Looking Up Exchange Rates Directly

If you need to check exactly what rate a report is using — for example, if someone asks what exchange rate was applied — you can look this up directly, rather than working it out from the report itself. NetSuite has two types of rate: the consolidated rate (the exchange rate between subsidiaries) and the currency rate (the exchange rate between currencies). Solution 7 has a dedicated lookup function for each, found under NetSuite Currencies in the function menu.

Consolidated exchange rate

1. Open the function menu, go to Currencies, and choose the NSFXCONSOLIDATEDRATE function.

   

2. Click Lookup to select the From subsidiary and the To subsidiary (e.g. Mexico to USA).
3. Enter the Accounting Period.

4. Click Lookup to choose the Rate Type — Average, Current, or Historical — for either actuals or budget.

   

5. Click OK.

The NSCONSOLIDATEDRATE function parameters mimic the same options on the "Consolidated Exchange Rates" page in NetSuite. If you cannot find this page, your NetSuite Administrator will need to adjust your role permissions.

Currency rate

1. Open the function menu, go to Currencies, and choose the NSFXCURRENCYRATE function.
2. Click Lookup to select the Source currency and the Base currency (e.g. MXN to USD).
3. Optionally, enter a date — this defaults to today if left blank, but can be set to look up a historical rate.

4. Click OK.

   

The NSFXCURRENCYRATE function parameters mimic the same options on the "Currency Exchange Rates" page in NetSuite. If you cannot find this page, your NetSuite Administrator will need to adjust your role permissions.

Viewing Transaction Currencies with a Pivot Table

Rather than pulling a single summarized balance, you can use a flattened pivot table to see a full list of transaction lines, along with the currency and amount each one was posted in.

1. Insert your Subsidiary pop-up list, and enter your Account and From Period.

2. Open the pivot table menu and insert a flattened pivot table with Transactions - Posted.

   

3. Select the Subsidiary, Account, and From Period as usual.

   

4. Open Choose Columns. On the left, you'll find your transaction currency amount fields — double-click or drag the Amount (Transaction Currency) field into the "selected columns" area. This will show the local currency amount of the transaction vs the Amount column which shows the same transaction in the currency set in the pivot table.

   

5. Click OK.

Display the transaction foreign currency

To see the currency each transaction was posted in, scroll down to Subsidiary > More Information > Currency, and double-click or drag the Currency Name column and Currency Symbol column.

Once added, you can filter the pivot table by currency — click into the currency column and use the column filter to select a specific currency (e.g. Euro). This shows only the transactions posted in that currency, alongside their converted amount, making it easy to identify which transactions within a subsidiary were posted in a particular foreign currency.

Sum columns live at the bottom

Keep your sum/amount columns together at the end of the column list — this keeps the pivot table's totals easy to read once it's generated.

Summary

Solution 7 gives you several ways to control which currency a report reflects — a subsidiary's own base currency, the parent company's currency, a different subsidiary's currency, an account's own currency, or a specific transaction currency — all from the same Balance and Advanced Balances functions you already use. The Result Basis option additionally lets you choose exactly which NetSuite exchange rate a report is built on, and a flattened pivot table gives you a transaction-level view when a summarized balance isn't enough.

Still need help? Contact Solution 7 Support.