02-13-2023 08:15 PM - last edited on 05-02-2023 09:59 AM by JackLacava
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?
Solved! Go to Solution.
02-14-2023 06:06 AM - edited 02-14-2023 06:18 AM
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)
02-13-2023 09:19 PM
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.
02-13-2023 09:29 PM
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.
02-13-2023 11:15 PM
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
02-14-2023 03:33 AM - edited 02-14-2023 03:47 AM
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.
02-14-2023 06:06 AM - edited 02-14-2023 06:18 AM
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)
02-14-2023 02:02 PM
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
02-17-2023 05:24 PM
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.
03-21-2023 08:37 AM
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
03-23-2023 09:13 AM
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
03-29-2023 07:40 PM
Thanks for the response. Appreciate your help.