Forum Discussion

gkeushkerian's avatar
gkeushkerian
New Contributor II
2 years ago

Custom Translation for FX Scenario - Help

Hello, I am trying to create a FX Scenario - Acutuals at Prior Year Rate. I know i can use Constant Year For FX Rate and assign the year from which OneStream will pick Actuals rate in this case. But i dont want the user to be changing this property every year.

My approach is to set "Translation Algorithm Type" to "Standard Using Business Rules For FX Rates" and create a custom translation for that specific scenario "Actuals at Prior Year Rate". 

Does anyone has the logic to translate the scenario using Actual Exchange rate tables switching one year back from the POV being translated?

Finance
  • Henning's avatar
    Henning
    2 years ago

    In your case, all you need may just be this (have not tested it) in a Finance BR attached to the cube. Provided that you add some limitations to ensure this executes for the correct scenario only and still execute the default translation for your standard scenarios.

     

     

    Dim BudScenarioId As Integer = api.Members.GetMemberId(dimTypeId.Scenario, "BUD")
Dim priorTimeId As Integer = api.Time.GetPriorYearPeriodId(api.Pov.Time.MemberPk.MemberId)

Dim RateTypeCloBudPre As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability(,BudScenarioId) 
Dim cloRateBudPre As Decimal =  api.FxRates.GetCalculatedFxRate(RateTypeCloBudPre,priorTimeId)
Dim RateTypeAvgBudPre As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp(,BudScenarioId)
Dim avgRateBudPre As Decimal =  api.FxRates.GetCalculatedFxRate(RateTypeAvgBudPre,priorTimeId)

api.ExecuteDefaultTranslation(FxRuleType.Direct, avgRateBudPre, FxRuleType.Direct, cloRateBudPre)

     

     

    (Using S#BUD and prior year period as an example source for the FX rate) 

  • Hweeli's avatar
    Hweeli
    New Contributor III
    2 years ago

    Hi gkeushkerian,

    you can try the following:

    If (api.Cons.IsCurrency() And Not api.Cons.IsLocalCurrencyforEntity()) Then

    Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId
    Dim timeIdPY As Integer = api.Time.GetLastPeriodInPriorYear

    Dim ratecls_opn As FxRateType = api.FxRates.GetFxRateType("ClosingRate")
    Dim openRate As Decimal = api.FxRates.GetCalculatedFxRate(ratecls_opn,timeIdPY)

    api.data.calculate("A#Headcount_TestFX_OR = A#Headcount_TestFX_OR:C#Local:V#YTD * " & api.Data.DecimalToText(openRate) & ")")
    End If

    • gkeushkerian's avatar
      gkeushkerian
      New Contributor II
      2 years ago

      Hi Hweeli for your reply.

      I have developed some rules to copy rates from one period to the other, even from one rate table to another one.

      I was trying to avoid creating several exchange rate tables just for period movements. Wanted to handle that through a BR where i could just switch one year back when grabbing the rates.

  • Henning's avatar
    Henning
    Valued Contributor II
    2 years ago

    Hi, maybe this snippet is also of help to you.

    There are also a few snippets in OneStream when you search for "translate". In addition to that, there are a few examples in the Design & Reference Guides.

     

     

    Namespace OneStream.BusinessRule.Finance.CustomTranslation
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As FinanceRulesApi, ByVal args As FinanceRulesArgs) As Object
			Try

'				Custom Translation For rate simulation members In UD8
				If api.FunctionType = FinanceFunctionType.Translate Then
					
				    'execute DEFAULT translation for everything except the cases that follow the default translation
                    api.ExecuteDefaultTranslation()
                                                                        
                    'execute rate simulation only for Actual scenario
                	If api.Pov.Scenario.Name = "Actual" Then
                                                                                    
	                    'get time from POV to determine corresponding rate
						 Dim priorTimeId As Integer = api.Time.GetPriorYearPeriodId(api.Pov.Time.MemberPk.MemberId)
						 
						 'get scenario ID for source FX rates
						 Dim BudScenarioId As Integer = api.Members.GetMemberId(dimTypeId.Scenario, "Budget")
						 						 
						 'get previous BUDGET average and closing rates 
						 Dim RateTypeCloBudPre As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability(,BudScenarioId) 
						 Dim cloRateBudPre As Decimal =  api.FxRates.GetCalculatedFxRate(RateTypeCloBudPre,priorTimeId)
						 Dim RateTypeAvgBudPre As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp(,BudScenarioId)
						 Dim avgRateBudPre As Decimal =  api.FxRates.GetCalculatedFxRate(RateTypeAvgBudPre,priorTimeId)
						 
						 'execute custom translation for rate simulation member in UD8
						 api.Data.Translate("U8#PreviousBUDRateACT", "U8#None",  FxRuleType.Direct, avgRateBudPre, FxRuleType.Direct, cloRateBudPre) 'Actual @ previous Budget rates
						 
					End If
		
				End If
				
				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace

     

     

    Please keep in mind that this is just an example to show you some code. Using a separate scenario - just as you plan to do - is typically recommended, not the UD way. Only for small(er) cubes or special use cases, using UD members may make sense. This is because if one does the rate simulations in a UD, one duplicates (or even multiplies) all data in the data unit, which has an adverse impact on performance.

    • Henning's avatar
      Henning
      Valued Contributor II
      2 years ago

      In your case, all you need may just be this (have not tested it) in a Finance BR attached to the cube. Provided that you add some limitations to ensure this executes for the correct scenario only and still execute the default translation for your standard scenarios.

       

       

      Dim BudScenarioId As Integer = api.Members.GetMemberId(dimTypeId.Scenario, "BUD")
Dim priorTimeId As Integer = api.Time.GetPriorYearPeriodId(api.Pov.Time.MemberPk.MemberId)

Dim RateTypeCloBudPre As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability(,BudScenarioId) 
Dim cloRateBudPre As Decimal =  api.FxRates.GetCalculatedFxRate(RateTypeCloBudPre,priorTimeId)
Dim RateTypeAvgBudPre As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp(,BudScenarioId)
Dim avgRateBudPre As Decimal =  api.FxRates.GetCalculatedFxRate(RateTypeAvgBudPre,priorTimeId)

api.ExecuteDefaultTranslation(FxRuleType.Direct, avgRateBudPre, FxRuleType.Direct, cloRateBudPre)

       

       

      (Using S#BUD and prior year period as an example source for the FX rate) 

      • gkeushkerian's avatar
        gkeushkerian
        New Contributor II
        2 years ago

        Hi Henning,

        Finally i could make it work with a FinanceFunctionType.Translate in a BR and a Custom Translation Algorithm Type in the Cube Properties.

        Thanks for the piece of code.