Using Multiple Currencies in Custom SQL Queries

Last updated
Save as PDF

Using Multiple Currencies in Custom SQL Queries

To specify currency titles for views that use custom SQL in queries or fields, Add-In Managers use macros.

The following are expressions that can be used in paginated report panels or view panels:

New Expression	Result
`${project.budgetCurrency.code}`	Organizational-Budget Currency code, for example, 'USD'.
`${project.budgetCurrency.symbol}`	Symbol for Organizational-Budget Currency,for example, “$”
`${user.userCurrency.code}`	User-Default Currency code, for example, 'Euro'. Determined based on the locale.
`${user.userCurrency.symbol}`	Symbol for User-Default Currency, e.g. “€”. Determined based on the locale.
`${project.currencyCodeFor(currencySymbol)}`	Code for specified currency symbol from the afm_currencies.code field.
`${project.currencySymbolFor(currencyCode)}`	Symbol for specified currency code from the afm_currencies.symbol field.

The following are expressions that can be used in data sources:

The exchange rate expressions evaluate to 1.0 if the currencies are the same. The expressions return the most currently updated exchange rates. For cases where you want to perform the currency conversion using a specific date, see Using Multiple Currencies in Payment Date SQL Queries and Fields .

For instance, below is an example of a data source that shows the costs from the Costs (cost_tran) table in the Budget Currency, USD, Euro, and Yuan in the same view. Such a view can be useful for line-of-business managers who want to see the budget figures not only in the Budget Currency but in their local currency, so they can have a better feel for the values. This practice is similar to newspapers such as the Economist that mention financial figures in both Dollars and Euros.

<dataSource id="reportGridSqlStats_ds"> <sql dialect="generic"> SELECT cost_tran_id, amount_expense, ( amount_expense * ${sql.exchangeRateFromBudget("USD", "Budget")} ) ${sql.as} amount_expense_usd, ( amount_expense * ${sql.exchangeRateFromBudget("EUR", "Budget")} ) ${sql.as} amount_expense_euro, ( amount_expense * ${sql.exchangeRateFromBudget("CNY", "Budget")} ) ${sql.as} amount_expense_yuan FROM cost_tran </sql> <table name="cost_tran" role="main"/> <field table="cost_tran" name="cost_tran_id"/> <field table="cost_tran" name="amount_expense"> <title>Cost in Budget Currency (${project.budgetCurrency.code})/title> </field> <field table="cost_tran" name="amount_expense_usd"> <title>Budget Cost in USD: ${project.currencySymbolFor("USD" )}</title> </field> <field table="cost_tran" name="amount_expense_euro"> <title>Budget Cost in Euro: ${project.currencySymbolFor("EUR" )}</title> </field> <field table="cost_tran" name="amount_expense_yuan"> <title>Budget Cost in Yuan: ${project.currencySymbolFor("CNY" )}</title> </field> </table> </dataSource>