1. SuiteAnalytics
  2. Workbook
  3. Custom Workbooks and Datasets
  4. Currency in Datasets and Workbooks
  5. Currency Conversion in Workbook

Currency Conversion in Workbook

Currency conversion in Workbook lets you convert field values to any currency you've set up in your account. To use currency conversion in a dataset or workbook, you need the Multiple Currencies feature enabled in your account. Also, make sure your currency records and exchange rates are set up right for accurate conversion. For more information, see Currency Exchange Rates.

Currency Conversion Using Custom Formula Fields

In the Dataset Builder, you can create custom formula fields with the context value #converted to change field values that are in different currencies.

For example, to show remaining balances on invoices no matter the original currency, you can create a custom formula field like this:

            TO_NUMBER({foreignamountpaid#converted}) - TO_NUMBER({foreignamountunpaid#converted})

The function TO_NUMBER is needed because the current Formula Builder doesn't support the CURRENCY output type, so TO_NUMBER converts these values to FLOAT. In single currency accounts, you could use this formula without the #converted context value:

            TO_NUMBER({foreignamountpaid}) - TO_NUMBER({foreignamountunpaid})

Depending on your formula, you can convert field values to your account’s base currency or a specific currency you pick. You can also choose the exchange rate based on a date you enter in the formula.

Use these sample formulas to convert the values of the total amount (transaction currency) field. The output is FLOAT:

Note:

To convert values from other fields, replace foreigntotal with a different field ID.

            TO_NUMBER({foreigntotal#converted})

  • Displays the currency amounts in your account's base currency. The conversion uses the exchange rate that's active when you run the query or workbook visualization.

            TO_NUMBER({foreigntotal#converted[1]})

  • Displays the currency amounts in the currency ID specified in the brackets [1]. The conversion uses the exchange rate active on the date when you run the query or workbook visualization.

            TO_NUMBER({foreigntotal#converted[1;2017-01-30]})

  • Displays the currency amounts in the currency specified in the brackets [1], using the exchange rate set for a specific date. Enter the date in YYYY-MM-DD format.

Currency Conversion from the User Interface

In a workbook visualization, you can convert currency values right from the UI for amount fields from transaction and transaction line records. These options aren't available in a dataset.

To convert currencies in a workbook visualization, click the Field Menu icon Field menu icon next to a transaction or transaction line field in the Layout Panel or column header, then select Currency. The following options are presented:

  • Display Original: displays each amount in the transaction's original currency. In table views and pivot tables, this is the default for transaction and transaction line fields.

  • Apply Conversion: changes each amount using the currency and date you pick in the Convert To window. By default, it uses the current date and your account’s or subsidiary’s base currency. In charts, this is the default for transaction and transaction line fields.

  • Conversion Options...: opens the Convert To window, where you pick the currency and date to use for conversion. The rates are drawn from your account's currency exchange rates table. For more information, see Currency Exchange Rates List Page.

    Note:

    If you check the Anchor Relative to Today box, the current date is used for conversion every time the workbook is opened.

If you're creating a calculated measure with fields that have values in multiple currencies, you can apply conversion in the measure definition too. For more information, see Calculated Measures.

Related Topics

General Notices