Using Multiple Currencies in Payment Date SQL Queries and Fields

The ${sql.exchangeRate} expressions deliver the most recent currency conversion factor. However, for some use cases, such as Property “Value – Book” (property.value_book) field, you want to perform the currency conversion according to the date the cost value was deemed accurate; for example, the Property “Date Book Value Assessed” (property.date_book_val). Doing so lets you view time-sensitive currency values – for instance you can get a sense of the appreciation and depreciation of the value of a property since it was last assessed.

To do so, use these expressions:

New Expression	Result
${sql.exchangeRateFromBudgetForDate( destinationCurrencyCode, exchangeRateType, dateField)}	The Exchange Rate from Budget Currency to specified currency, for specified exchange rate type (“Budget” or “Payment”) and date of transaction.
${sql.exchangeRateToBudgetForDate( sourceCurrencyCode, exchangeRateType, dateField)}	The Exchange Rate from specified currency to Budget Currency, for specified exchange rate type and date.
${sql.exchangeRateFromBudgetToUserForDate( exchangeRateType, dateField)}	The Budget-Exchange Rate from Budget Currency to User Default Currency, for specified exchange rate type and date
${sql.exchangeRateFromFieldForDate(sourceCurrencyField, destinationCurrencyCode, exchangeRateType, dateField)}	The Exchange Rate from currency stored in specified field to another currency, for specified exchange rate type and date.

When using these expressions please notice:

• dateField. The dateField is the name of a date field within the table you are querying. It cannot be a literal value (that is, it must be of the form “property.date_book_val” and not “2011-07-07”).
• Performance. The macros that deliver the exchange rate perform one sub-query on the Archibus Currencies table for each row. These will deliver adequate performance on results that are retrieved per-page that is, listing all Properties and their converted Book Values on the screen or in a paginated report). Do not use these expressions on large amounts of data.

For instance, if the User Display Currency is Euros (EUR), and the Budget Currency is US Dollars (USD), then you could show the Book Value in Budget Currency, the “Date Book Value Assessed”, and the Historical Currency Conversion Factor using this SQL query in a data source:

SELECT pr_id ${sql.as} 'Property Code', value_book ${sql.as} 'Book Value – Budget Currency', value_book * ${sql.exchangeRateFromBudgetToUserForDate( 'BUDGET', 'property.date_book_val')} ${sql.as} 'Book Value – User Currency', ${sql.exchangeRateFromBudgetToUserForDate( 'BUDGET', 'property.date_book_val')} ${sql.as} 'Historical Currency Conversion Factor', date_book_val, FROM property WHERE value_book > '0.00';