Custom Translation for FX Scenario - Help

[gkeushkerian]

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?

[Hweeli]

Hi,

You may refer to this post https://community.onestreamsoftware.com/t5/Rules/Copy-FX-Rates-between-FX-Types-and-Different-period... to create a new FX rate type and copy prior year rate using BR.

[gkeushkerian]

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.

[Hweeli]

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

[Henning]

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 · Accepted solution]

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]

Hi Henning,

Thanks for the reply. This is just what i was trying to achive. But i couldn't make it work. Tried with a FinanceFunctionType.FxRate and also FinanceFunctionType.Translate but couldnt get the values translated to the prior year rate.

I finally created two exchange rate tables PYAverageRate amd PYClosingRate and created a Extensibility Rule that copies Rates from AverageRate and ClosingRate Tables to the new ones switching back one year.

		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
			Try

				Me.SetPriorYearRates(si, "PriorYearAverageRate", "AverageRate")
				Me.SetPriorYearRates(si, "PriorYearClosingRate", "ClosingRate")

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
		
#Region "PriorYearRates"
		Public Function SetPriorYearRates(ByVal si As SessionInfo, ByVal targetRateTable As String, ByVal sourceRateTable As String)
			Try
				
				'****************************************************************************************************
				'Define the target Rate Type:
				Dim fxRateTypeTarget As String = targetRateTable
				'Define the source Rate Type:
				Dim fxRateTypeSource As String = sourceRateTable
				
				'Define the target period year:
				Dim myWorkflowUnitPk As WorkflowUnitPk = BRApi.Workflow.General.GetWorkflowUnitPk(si)
				Dim workflowTimeTarget As String = BRApi.Finance.Time.GetNameFromId(si, myWorkflowUnitPk.TimeKey).Substring(0,4)
				'Define the source period year:
				Dim workflowTimeSource As String = BRApi.Finance.Time.GetNameFromId(si, BRApi.Finance.Time.AddYears(si, myWorkflowUnitPk.TimeKey, -1)).Substring(0,4)
				'BRApi.ErrorLog.LogMessage(si, "Target Time = " & workflowTimeTarget & ", Souce Time = " & workflowTimeSource)
				
				'Define the list of periods:
				Dim periods As String() = {"M1","M2","M3","M4","M5","M6","M7","M8","M9","M10","M11","M12"}
				
				'Define the list of source currencies:
				Dim currencyStr As String = BRApi.Utilities.GetApplicationProperties(si).CurrencyFilter
				Dim sourceCurrencies As List(Of String) = StringHelper.SplitString(currencyStr,",",StageConstants.ParserDefaults.DefaultQuoteCharacter)

				'Loop periods:
				For Each period As String In periods
					'Loop source currencies:
					For Each sourceCurrency As String In sourceCurrencies
						
						'Define destination currencies:
						Dim destinationCurrency As String = Currency.USD.Name
						
						
						'Get source's rate type, time period, source and destination currencies:
						Dim objFxRatePkUsingNamesSource As New FxRatePkUsingNames(fxRateTypeSource,workflowTimeSource & period,sourceCurrency,destinationCurrency)	
						Dim objFXRateUsingNamesSource As FXRateUsingNames = BRApi.Finance.Data.GetStoredFxRate(si, objFxRatePkUsingNamesSource)
						
						If objFXRateUsingNamesSource.Amount > 0 Then
						
							'Get target's rate type, time period, source and destination currencies:
							Dim objFxRatePkUsingNamesTarget As New FxRatePkUsingNames(fxRateTypeTarget,workflowTimeTarget & period,sourceCurrency,destinationCurrency)
						
							'Define the FX rate value (set HasData to True and IsInvalid to False in order to submit the FX rate successfully):
							Dim objFxRateUsingNamesTarget As New FxRateUsingNames(objFxRatePkUsingNamesTarget, objFXRateUsingNamesSource.Amount, True, False)
						
							'Submit the rate if exists:
							brapi.Finance.Data.SetFxRate(si,objFxRateUsingNamesTarget)
						End If
						
					Next
				Next

				'****************************************************************************************************
				
				Return Nothing
				Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
#End Region

			End Try
		End Function

[gkeushkerian]

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.

[Krishna]

Hi - Could you please the snippet just the api.data.transalate line ? also did you change the Cube Properties to custom translate ? and attached the BR in the rule section. 

Thanks

[gkeushkerian]

Hi Chris,

Here goes the code. You need to set Custom to the Translation Algorithm Type in Cube Properties.

					Case Is = FinanceFunctionType.Translate
						'Assign BR to Mgmt Reporting cube and set Custom as Translation Algorithm Type in cube properties
						If api.Pov.Scenario.Name = "ActualAtPYRate" Then 

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

							Dim RateTypeCloActPre As FxRateType = api.FxRates.GetFxRateTypeForAssetLiability(,ActualScenarioId) 
							Dim cloRateActPre As Decimal =  api.FxRates.GetCalculatedFxRate(RateTypeCloActPre,priorTimeId)
							Dim RateTypeAvgActPre As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp(,ActualScenarioId)
							Dim avgRateActPre As Decimal =  api.FxRates.GetCalculatedFxRate(RateTypeAvgActPre,priorTimeId)

							api.ExecuteDefaultTranslation(FxRuleType.Periodic, avgRateActPre, FxRuleType.Direct, cloRateActPre)
						
						Else
							api.ExecuteDefaultTranslation()
						End If
				End Select

[Krishna]

Thanks for the response. Appreciate your help.