Foreign currency conversion for data query
Include foreign currency conversion data in data query results
This article explains how to include foreign currency conversion data in data query results by using the
fx_convert
function.
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.
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)
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.idConvert 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 accountConvert 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
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