FX rates retrieval – based on different source currencies

[AndraEcxarcu]

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!

[NicolasArgente]

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]

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? 

[NicolasArgente]

Can you please send your full script? Thanks.

[AndraEcxarcu]

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

[RobbSalzmann]

Hi @AndraEcxarcu , you can get Entity Parent Currency:

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

[Big_Rick_CPM]

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]

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!