Forum Discussion

LaurenHouzet's avatar
LaurenHouzet
New Contributor
6 months ago

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

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 tak...
Application Properties
  • T_Kress's avatar
    T_Kress
    6 months ago

    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)

     

  • FredLucas's avatar
    FredLucas
    6 months ago

    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_Kress's avatar
T_Kress
Contributor III
6 months ago

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's avatar
    LaurenHouzet
    New Contributor
    6 months ago

    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's avatar
      FredLucas
      Contributor III
      6 months ago

      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.

      • LaurenHouzet's avatar
        LaurenHouzet
        New Contributor
        6 months ago

        Thank you, I'll give this a try!