Skip to main content

Foreign Currency Conversion for Data Query

Zuora

Foreign Currency Conversion for Data Query

This article explains how to include foreign currency conversion data in data query results by using the fx_convert function. For more information, see Foreign currency conversion and Data Query.

You can convert monetary values in your Zuora tenant from an original currency to a target currency through Data Query. A typical example of a monetary value is a transaction amount. Foreign currency conversion in Data Query helps you with consistent reporting across different countries.

Currency Conversion for Data Source Exports vs. Data Query

  Currency Conversion for Data Source Exports Currency Conversion for Data Query

Supported Zuora objects

Transaction-related objects

For a complete list, see Data Source objects that support foreign currency conversion.

All Zuora objects

Supported object fields

Fields with transaction amount values

For a complete list, see Values for the foreign currency conversion fields.

Fields with numeric values, including transaction amount values and other monetary values. For example Invoice.Balance and Account.Balance.

Custom fields with numeric values are also valid.

Original currency and exchange rate date

Zuora has pre-defined the rule.

For more information, see Values for the foreign currency conversion fields.

You can specify the currency and exchange rate date as needed

Configuration settings for currency conversion

You can configure in your Zuora tenant

For more information, see Configure foreign currency conversion.

Pre-defined settings

Convert values using the fx_convert function

To include foreign currency conversion data in Data Query results, use the fx_convert function in SQL statements.

The fx_convert function converts a value from an original currency to a target currency using the exchange rate provided by Oanda based on an exchange rate date.

Syntax

fx_convert(original_value, convert_from_currency, convert_to_currency, exchange_rate_date)

Parameters

Note that all these parameters are required.

Parameter Format Description Examples
original_value Numeric A numeric value to be converted 50, Invoice.Amount, Account.Balance
convert_from_currency 3-letter ISO currency code

The currency of the original value

For more information, see ISO Currency Codes.

‘USD’, Account.Currency
convert_to_currency 3-letter ISO currency code

The target currency to be converted to

For more information, see ISO Currency Codes.

‘USD’
exchange_rate_date ISO 8601 formatted date The date that the conversion exchange rate from Oanda is based on date ‘2022-06-10’, Invoice.InvoiceDate

Returns

Converted value in the target currency.

SQL examples for converting values to target currencies

Convert the transaction amount to your home currency

The following query shows how to convert Invoice.Amount from the transaction currency (Account.Currency) to your home currency (USD). The exchange rate is based on the invoice date of each invoice.

SELECT invoice.amount, fx_convert(invoice.amount, account.currency, 'USD', least(invoice.posteddate, invoice.invoicedate))
FROM invoice
INNER JOIN account ON invoice.accountid = account.id

Convert account balances to GBP and add them up

The following query shows how to convert Account.Balance across all accounts in your tenant from the Account.Currency to a specific currency (GBP) using the exchange rate on 2022-06-10.

SELECT sum(fx_convert(account.balance, account.currency, 'GBP', date '2022-06-10'))
FROM account

Convert a numeric value to multiple currencies

The following query shows how to convert a numeric value (50) from USD to two target currencies (GBP and JPY) using the exchange rate on 2022-06-10.

SELECT fx_convert(50, 'USD', 'GBP', date '2022-06-10'), fx_convert(50, 'USD', 'JPY', date '2022-06-10')

Best practices for currency conversion through Data Query

When converting monetary values through Data Query, you can use any currency or exchange rate date. This gives you the flexibility to perform the currency conversion as needed. However, it is not always appropriate to use an arbitrary currency or exchange rate date.

We recommend that you choose meaningful currencies and exchange rate dates. For example, when converting Invoice.Amount to your home currency, do not use a fixed currency code (e.g., GBP or JPY) as the transaction currency. The currency might be different for each transaction, especially when you have customers from different countries. You should use Account.Currency instead of a fixed currency code. In addition, for each transaction, you should use the earlier of Invoice.InvoiceDate and Invoice.PostedDate as the exchange rate date. Do not use a fixed date such as 2022-06-10.

For more information about the suggested transaction currency and exchange rate date for each transaction amount field, see Values for the foreign currency conversion fields.

Notes and limitations

Zuora uses the following currency conversion settings for foreign currency conversion through Data Query. You cannot change these settings through the Zuora UI.

  • Exchange Rate Provider: Oanda
  • Exchange Rate Date: Daily
  • Use inverse rate: No
  • Rounding Mode: Half Up

For more information about currency conversion settings, see Configure foreign currency conversion.