Foreign Currency Conversion for Data Query

Last updated
Save as PDF

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.