09-12-2023
05:42 AM
- last edited on
09-12-2023
07:24 AM
by
JackLacava
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!
09-12-2023 07:42 AM - edited 09-12-2023 07:44 AM
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.
09-12-2023 08:00 AM
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?
09-12-2023 08:35 AM
Can you please send your full script? Thanks.
09-12-2023
08:42 AM
- last edited on
09-12-2023
10:06 AM
by
JackLacava
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
09-12-2023 10:37 AM - edited 09-12-2023 10:41 AM
Hi @AndraEcxarcu , you can get Entity Parent Currency:
Dim destCurrId As Integer= api.Pov.GetCurrencyIdForEntityParentCons()
09-12-2023 12:42 PM - edited 09-12-2023 12:43 PM
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.
09-21-2023 04:50 AM
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!