How to create a formula that's specific to the base entities of a Parent

[LaurenHouzet]

Hi OneStream Community - I'm trying to create a rule to apply a specific % to the base entities of a specific region. To keep it simple, say we have 5 regions and I want to create an account that takes their PBBIT and multiplies it by a % that is different for each region, and I want it to calculate it at the base entity level, of that region. Is this possible? Many thanks in advance!

[T_Kress · Accepted solution]

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)

[T_Kress · Accepted solution]

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)

[LaurenHouzet]

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!

[FredLucas]

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.

[FredLucas · Accepted solution]

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

[Henning]

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.

[Henning]

Seems like I misunderstood the question a bit 😉 @FredLucas and @T_Kress got it!