Forum Discussion

AndraEcxarcu's avatar
AndraEcxarcu
New Contributor II
2 years ago

FX rates retrieval – based on different source currencies

Hi everyone!

I have two entity hierarchies, one in currency EUR, and one in USD.

I need to create a cube view to retrieve the FX rates based on the parent’s currency. I’ve created an account, for example ClosingRate, as a DynamicCalc. I am using this formula to retrieve the rate: 

api.FxRates.GetCalculatedFxRate(rateType, cubeId, timeId, sourceCurrId, destCurrId)

 

For the destination currency, it’s straightforward:

Dim destCurrId As Integer = api.Pov.Cons.MemberPk.MemberId

But I am unsure how to set the source currency as variable, I usually either set it to EUR, or USD.

Dim sourceCurrId As Integer = Currency.EUR.Id

Is there a way to get a parent’s currency? Or, I had another idea. To create a Text1 property at the level of each entity base member, to specify the Parent’s currency. I can retrieve it in the script as a string, but after that I don’t know how to get the Id.

Appreciate your help!

Thank you!

Finance
  • RobbSalzmann's avatar
    RobbSalzmann
    Valued Contributor II
    2 years ago

    Hi AndraEcxarcu , you can get Entity Parent Currency:

    Dim destCurrId As Integer= api.Pov.GetCurrencyIdForEntityParentCons()

     

  • NicolasArgente's avatar
    NicolasArgente
    Valued Contributor
    2 years ago

    Hi AndraEcxarcu 
    I am sorry, I did not have time to test what I am sending. I just hope it will help you. Can you try that please?
    Dim nValue As Integer = api.Pov.GetCurrencyIdForEntityParentCons()
    A feedback would be appreciated.
    Have a good day

    PS: Sorry, I did not see it was a for a Cubeiew. It wont work like this then.

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

    Hi NicolasArgente 

    Thanks for your reply! Unfortunately, it doesn't work. It brings the value 1 for all currencies 😞 

    Do you think there is any other option for this to work in a cube view? 

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

    This is the script from the member formula:

    Dim destCurrId As Integer = api.Pov.Cons.MemberPk.MemberId
If destCurrId = Currency.EUR.Id Then
	Return 1.0
Else
	Dim rateType As FxRateType = api.FxRates.GetFxRateTypeForRevenueExp()
	Dim cubeId As Integer = api.Pov.Cube.CubeId
	Dim timeId As Integer = api.Pov.Time.MemberPk.MemberId
	Dim sourceCurrId As Integer = Currency.EUR.Id
	Dim rate As Decimal = api.FxRates.GetCalculatedFxRate( _
		rateType, cubeId, timeId, sourceCurrId, destCurrId)
	Return rate
End If

     

  • Big_Rick_CPM's avatar
    Big_Rick_CPM
    Contributor
    2 years ago

    In order for api.pov.GetCurrencyIdForEntityParentCons or api.pov.Parent.MemberId to work, you have to make sure the cube view is displaying valid parent/child combinations. You will likely need to change how your cube view is setup if you want to display consolidation type information like parent currency (since this info is parent/child specific). 

    I have set up several reports in the past that use dynamic calculations to return consolidation information like this.  Those reports typically have the entity dimension in the rows and uses some type of '.Tree' expansion function.

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

    Thank you everyone for the help!

    Big_Rick_CPM  yes, I had to change my cube view. However, it would not have been helpful to display parent/child. Instead, I decided to use as entity only the parent, thus changing in my script the source currency to be api.Entity.GetLocalCurrencyId:

    Dim sourceCurrId As Integer = api.Entity.GetLocalCurrencyId

    This works exactly as I need.

    Have a great day!