Currency Consolidation and Conversion Using Custom Formula Fields

You can consolidate or convert values in multiple currencies using the context values #currency_consolidated or #converted. In the Dataset Builder, you'll need this if you want to see totals or run calculations on fields with multiple currencies. In workbook visualizations, any field with values in more than one currency will have conversion options right in the interface. For more information, see Currency in Datasets and Workbooks.

In Workbook, currency consolidation is only applicable to amount fields from the transaction accounting line record type. Currency conversion is only applicable to amount fields from the transaction and transaction line record types.

For example, to consolidate values from the amount net field in the transaction accounting line record type, use this formula:

          TO_NUMBER({transactionlines.accountingimpact.netamount#currency_consolidated})

To convert the values from the amount paid field on the transaction record type, use this definition:

          TO_NUMBER({foreignamountpaid#converted})

With #consolidated, you can also choose the specific currency code and exchange rate date if needed. For more information, see Currency Conversion Using Custom Formula Fields.

You always need to use TO_NUMBER with #currency_consolidated or #converted. That's because the current Formula Builder doesn't support the CURRENCY output type, so TO_NUMBER changes results to FLOAT. For more information about using TO_NUMBER to cast currency values, see Casting Field Values using TO_NUMBER and TO_NCHAR.

Related Topics