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 enables you to convert the values of a field to any currency that you have set up in your account. To apply currency conversion in a dataset or workbook, you must have the Multiple Currencies feature enabled in your account. Additionally, you should make sure that your currency records and currency exchange rates are set up correctly to ensure correct conversion results. For more information, see Currency Exchange Rates.

Currency Conversion Using Custom Formula Fields

In the Dataset Builder, you can create custom formula fields using the context value #converted to convert field values that are in multiple currencies.

For example, to display the remaining balances on the invoices in your account regardless of the original currency used on each invoice, you can create a custom formula field using the following definition:

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

The function TO_NUMBER is required in the definition because the current version of the Formula Builder does not support the CURRENCY output type, and TO_NUMBER casts these values to the FLOAT output type. In single currency accounts for example, you could use the following formula definition without the #converted context value:

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

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

Use the following sample formulas to convert the values of the total amount (transaction currency) field. The output type for these formulas 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 the base currency of your account. The exchange rate used for the conversion is the rate that is in effect on the date when the query or workbook visualization is run.

            TO_NUMBER({foreigntotal#converted[1]})

  • Displays the currency amounts in the currency ID specified in the square brackets [1]. The exchange rate used for the conversion is the rate that is in effect on the date when the query or workbook visualization is run.

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

  • Displays the currency amounts in the currency specified in the square brackets [1], using the exchange rate that is in effect on a specific date. You must enter the date using the YYYY-MM-DD format.

Currency Conversion from the User Interface

In a workbook visualization, you can convert currency values directly from the user interface for amount fields from the transaction and transaction line record types. These options are not available from within a dataset.

To apply conversion from within 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 original currency used for the transaction. In table views and pivot tables, this is the default selection for transaction and transaction line fields.

  • Apply Conversion: converts each amount using the currency and date selected on the Convert To window. By default, the Convert To window is set to the current date and the base currency assigned to your account or subsidiary. In charts, this is the default selection for transaction and transaction line fields.

  • Conversion Options...: opens the Convert To window. In the Convert To window, you select the currency and date to use when the Apply Conversion option is selected. The rates used in the conversion are drawn from the table of currency exchange rates set up in your account. For more information, see Currency Exchange Rates List Page.

    Note:

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

If you are creating a calculated measure that includes fields with values in multiple currencies, you can also apply conversion as part of the measure definition. For more information, see Calculated Measures.

Related Topics

General Notices