The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
Parent Name using UD8 Formula
Has anyone been able to successfully obtain a parent name for a member, within a UD8 formula without needing to leverage a text field on the member (using Parent Sort Order for example)?
As an exam...
Ok, I tested it myself and parent sort order does not influence / determine the order within the list when the GetParents function is used. It seems that the member ID is driving this. Do not rely on this as the member ID can differ between applications for the same members.
I did look into the member properties that can be used but did not find any that includes the parent sort order that can be used to sort the list directly (something like list.sortBySortOrder).
You can, however, use this:
Api.Entity.ParentSortOrder()This pulls the sort order number of a given member under a parent. You can then use this to determine the member using the sort order number you want. Not as pretty as I was hoping for but maybe someone here has a more pretty version of that in mind.
If you go down that route, make sure to document this well so that no one will ever set up a member without setting the sort order as needed.
Another edit:
Having said that, using a text field might be the superior solution. Depending on your exact use case of course. With a text field you can pull the desired parent directly from your member. No loop, just a single rule line.
With the above, one needs to pull the parents into a list, loop through the list and then use the one with the lowest parent sort order number (or whatever number one specifies).Text attributes seem more user friendly, less error-prone and easier to maintain to me without knowing all the requirement details.
Henning
OneStream Employee
OneStream EmployeeHi, the easiest to get / retrieve a member parent in the metadata may just be to use a text field if you need to retrieve a specific parent frequently.
You can also use things such as lookup tables if you have a more "mapping-type" logic in mind and retrieve the parents you need for your logic.
The challenge when using a rule is that each member can be shared across different hierarchies and therefor can have multiple parents. That is why when you use a rule to get the parents of a member, you will not get a single string name of a parent, you will get all parents in form of a list, e.g.:
Dim entityDimPk As DimPk = Api.Pov.EntityDim.DimPk
Dim parentList As List(Of Member) = Api.Members.GetParents(entityDimPk, memberId, False, Nothing)There might only be a single parent, in which case you can use this, but if someone moves the member somewhere else, your logic breaks as the parent list then contains more than a single parent. So I recommend caution and careful and detailed documentation of this in case this is implemented.
Long story short, you will have to decide whether a text field or another solution serves your requirements best.
Thanks, Henning.
Was thinking we could assign a parent sort order to each of the hierarchy parents and then reference this in the formula depending on which parent we want to return.
- Henning
Not a bad idea, I never thought about that - i.e. I have never tested that. You could test it with a couple of parents and then write the parent list into the error log to see whether the parent sort order influences the order in the list. But I do recommend some alternating tests to really make sure it works and to rule out any accidental behavior that appears to be similar.
- Henning
Ok, I tested it myself and parent sort order does not influence / determine the order within the list when the GetParents function is used. It seems that the member ID is driving this. Do not rely on this as the member ID can differ between applications for the same members.
I did look into the member properties that can be used but did not find any that includes the parent sort order that can be used to sort the list directly (something like list.sortBySortOrder).
You can, however, use this:
Api.Entity.ParentSortOrder()This pulls the sort order number of a given member under a parent. You can then use this to determine the member using the sort order number you want. Not as pretty as I was hoping for but maybe someone here has a more pretty version of that in mind.
If you go down that route, make sure to document this well so that no one will ever set up a member without setting the sort order as needed.
Another edit:
Having said that, using a text field might be the superior solution. Depending on your exact use case of course. With a text field you can pull the desired parent directly from your member. No loop, just a single rule line.
With the above, one needs to pull the parents into a list, loop through the list and then use the one with the lowest parent sort order number (or whatever number one specifies).Text attributes seem more user friendly, less error-prone and easier to maintain to me without knowing all the requirement details.
Hi Henning,
Still puzzling through this. I have very elementary knowledge of vb.net so wondering if you could provide some guidance on how I could write a member formula to obtain this, or if even possible. We have an alternate hierarchy where all the parent names contain the text "BIP" and I'd like a UD8 member formula that returns the BIP parent member name.
So far, I have this. Thought it would return a whole list of parents and then the code would be further refined to condition on containing "BIP" text but nothing is returned. I am thinking this isn't even viable for a member formula?
Dim memberId As Integer = api.Pov.Entity.MemberId
Dim entityDimPk As DimPk = Api.Pov.EntityDim.DimPk
Dim parentList As List(Of Member) = Api.Members.GetParents(entityDimPk, memberId, False, Nothing)
If api.View.IsAnnotationType() Then
Return parentList
Else
' Return a NoData Datacell
Return api.Data.CreateDataCellObject(0.0, True, False)
End If- Henning
Hi, you are returning "parentList" to your annotation cell.
Remember how you declared parentList:
Dim parentList As List(Of Member)I.e. it is a list of members. The return function for annotation expects a string and does not know what to return from the list of members you are giving it.
In your case this means that you have to loop through parentList (i.e. loop through each member in the list) and then pull the one parent (I assume only one parent uses your text field tag) into a string which you can then return to your cube view cell.
Something along those lines:
Dim parentName_Text1_BIP as String For each parentEntity in parentList 'Check each member and declare parentName_Text1_BIP = the name of the member with BIP in Text1 '... Next If api.View.IsAnnotationType() Then Return parentName_Text1_BIP Else ' Return a NoData Datacell Return api.Data.CreateDataCellObject(0.0, True, False) End IfFantastic! Appreciate your help immensely!
Sharing the final code that we used in case it helps anyone else. This is searching for specific text at the beginning of the member name - this was more ideal for us than maintaining a text field for these parents - but could be easily modified to reference a text field as Henning shows above.
Dim parentName_BIP As String Dim memberId As Integer = api.Pov.Entity.MemberId Dim entityDimPk As DimPk = Api.Pov.EntityDim.DimPk Dim parentList As List(Of Member) = Api.Members.GetParents(entityDimPk, memberId, False, Nothing) For Each parentEntity In parentList If parentEntity.Name.StartsWith("BIP") Then parentName_BIP = parentEntity.Name Exit For End If Next If api.View.IsAnnotationType() Then Return parentName_BIP Else ' Return a NoData Datacell Return api.Data.CreateDataCellObject(0.0, True, False) End If
