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!
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.
Can you please send your full script? Thanks.
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
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.
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!