Skip to main content
A
AndraEcxarcu
September 12, 2023

FX rates retrieval – based on different source currencies

  • September 12, 2023
  • 7 replies
  • 0 views

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!

7 replies

N
NicolasArgente
September 12, 2023

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.

A
AndraEcxarcuAuthor
September 12, 2023

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? 

N
NicolasArgente
September 12, 2023

Can you please send your full script? Thanks.

A
AndraEcxarcuAuthor
September 12, 2023

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

 

R
RobbSalzmann
September 12, 2023

Hi AndraEcxarcu , you can get Entity Parent Currency:

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

 

B
Big_Rick_CPM
September 12, 2023

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.

A
AndraEcxarcuAuthor
September 21, 2023

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!

Terms & ConditionsAccessibility statement