Forum Discussion
7 Replies
- FredLucasContributor III
Hi LaurenHouzet,
Depending on the use case you might want to set it up as a dynamic calc or a stored account.
If you are calculating it dynamically or as part of you consolidation logic then you might want to use the following function to validate if the Entity being calculated is a base member of the specific parent Entity / Region:
Dim bValue As Boolean = api.Members.IsBase(dimPk, ancestorMemberId, baseMemberId)
If calculating it as part of a Finance Rule you could set up a DM Step that only triggers the calculation for the relevant entities: E#SpecificRegion.base
- T_KressValued Contributor
You can use this api call to test (IF) whether the current entity being calculated is (descendantmemberid) a descendant of a particular region (ancenstormemberID), and if so continue with your calc or set the % base on a case statement by region:
Dim bValue As Boolean = api.Members.IsDescendant(dimPk, ancestorMemberId, descendantMemberId, dimDisplayOptions)
- LaurenHouzetNew Contributor
Hi Teresa - thank you for your response, I'm not very good at the formula writing though so I'm still struggling! This was my original very basic formula as a DynamicCalc:
"Return api.Data.GetDataCell("A#PBBIT + (A#PBBIT * -0.3) - A#DepAmort")"
So I'm unsure where to add your sentence into the formula and also where I need to override the bits with the region name. For example the region parent name is 'Coho' and one of its base entities is no. 173. How do I tell the system to look at Coho region and calculate the base entity to use 0.2 instead of 0.3?
To complicate it even more, is it possible to write it so that it calculates all other base entities at 0.3 with the exception of a couple specific regions, e.g. Coho, where they have a different value?
I hope the above makes sense and sorry for being so clueless!
- FredLucasContributor III
As I'm sure you know, you should avoid as much as possible to hardcode names into your code to ensure it's future proof and easy to maintain. For example, consider using Text Attributes to tag members that should follow exceptional cases and use those text attributes in your calculations instead.
Having said that here's a simple snippet you can use as a starting point for your use case:
'Only run if is a Base Entity and the view is not of an annotation type If ((Not api.Entity.HasChildren()) AndAlso (Not ViewMember.IsAnnotationTypeViewId(api.Pov.View.MemberId))) Then Dim exceptionRegionName As String = "Coho" Dim exceptionRegionId As Integer = api.Members.GetMemberId(dimTypeId.Entity, exceptionRegionName) Dim isExceptionBaseEntity As Boolean = api.Members.IsBase(api.Pov.EntityDim.DimPk, exceptionRegionId, api.Pov.Entity.MemberId) If isExceptionBaseEntity Then Return api.Data.GetDataCell("A#PBBIT + (A#PBBIT * -0.2) - A#DepAmort") Else Return api.Data.GetDataCell("A#PBBIT + (A#PBBIT * -0.3) - A#DepAmort") End If Return Nothing End If
Also do bear in mind that the above code will only run for base entities calculating that dynamic account on the fly which means data is not stored and will not be consolidated/aggregated up the Entity dimension.
If you want this dynamic calc to also trigger on parent entities (e.g.: Regions, etc.) dynamically then you'd have to update the code accordingly if instead you'd like this calculation to consolidate or aggregate up the entity hierarchy (e.g.: repeating the calc at parent level would not give you the same results) then you might want to consider setting up this account as stored so the amounts are calculated and stored at base level and then aggregated or consolidated up.
- HenningValued Contributor II
Parent-specific calculations apply to Consolidation dimension members (C#) other than C#Local. Everything on C#Local rolls up into each parent entity wherever a given base entity is assigned to.
You could also add (possibly dynamic) reporting members for the calculated data on C#Local, but in that case, users need to be educated to know which adjustment members to select based on the region they want to analyze.
- HenningValued Contributor II
Related Content
- 2 years ago