Reporting Currency Conversion on the Fly

Hi All,

In Acumatica you can use standard currency translation tool to translate base currency to any other reporting currency. However this process is available only for GL Balances. You can really translate AP/AP SO/PO balances and total using the same approach.
Here I want to give you the idea of how can you do on the fly currency translation from any currency to any currency on any document in Acumatica for reporting purposes.

Problem

The biggest problem of this task are Rates. In Acumatica we store rates assigned to effective date. But if you have other dates where are no rates defined, we need to user the closest previous rate. Because of this architecture there is no possibility to easily get a rage based on date and currency in SQL, as there is no place to join.
We need to solve this problem first and have a view where you really can have a rates per date.

Plan

To accomplish this task we need to do following:

1. Create a special SQL view that can return rate by date.
2. Create a DAC based on view above. We can use this DAC in reports and GI.
3. Create a Report where we can use view and DAC above.

Action

SQL View
Here you can see the script for Rates view. This script helps to return rates for each currency per each date:

Data Access Class
To be able to use view above we need to have a DAC class:

[Serializable]
public class nt_Rates : IBqlTable
{
    #region TenantID
    [PXDBInt(IsKey = true)]
    [PXUIField(DisplayName = "Tenant ID")]
    public int? TenantID { get; set; }
    public class tenantID : IBqlField { }
    #endregion
    #region FromCuryID
    [PXDBString(5, IsKey = true, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "From Cury ID")]
    public string FromCuryID { get; set; }
    public class fromCuryID : IBqlField { }
    #endregion
    #region ToCuryID
    [PXDBString(5, IsKey = true, IsUnicode = true, InputMask = "")]
    [PXUIField(DisplayName = "To Cury ID")]
    public string ToCuryID { get; set; }
    public class toCuryID : IBqlField { }
    #endregion
    #region CuryEffDate
    [PXDBDate(IsKey = true)]
    [PXUIField(DisplayName = "Cury Eff Date")]
    public DateTime? CuryEffDate { get; set; }
    public class curyEffDate : IBqlField { }
    #endregion
    #region RateType
    [PXDBString()]
    [PXUIField(DisplayName = "Rate Type")]
    public int? RateType { get; set; }
    public class rateType : IBqlField { }
    #endregion
    #region RateID
    [PXDBInt()]
    [PXUIField(DisplayName = "Rate ID")]
    public int? RateID { get; set; }
    public class rateID : IBqlField { }
    #endregion
}

Reportw
Now we can easily create a report where we can select any currency registered in the system for conversion and get documents in converted currency.
Please note the currency selection on the report launcher form:

And here please note on how we can join new view to get proper rate:

First join condition allows us to select proper rate record, Second one gives us the conversion rate itself.
later we can use formulas to do currency conversion:

= [Register.CuryDocAmt] * IsNull(IIF([CurrencyRate.CuryMultDiv]='D', [CurrencyRate.CuryRate], [CurrencyRate.RateReciprocal]), 1)

Have a nice reporting!