Forum Discussion

pling's avatar
pling
New Contributor II
3 years ago

Retrieve Parent property

SOURCE: ONESTREAM CHAMPIONS

Hi All,

I am trying to find a way to create a report that shows the Entity and the Parent in a column next to it. I tried to do this by using the functions =XFGetMemberProperty() and =XFGetRelationshipProperty(), both returns an error when I try.

=XFGetRelationshipProperty(“Entity”,"",B15,“Parent”,"","") returns “#ERROR: Parent”
=XFGetRelationshipProperty(“Entity”,B16,"",“Parent”,"","") returns “#ERROR: Member Name”
=XFGetRelationshipProperty(“Entity”,B17,"",“Parent”,“ACTUAL”,“2021M1”) returns “#ERROR: Member Name”
=XFGetRelationshipProperty(“Entity”,B4,B5,“Parent”,"","") returns “#ERROR: Parent”

=XFGetMemberProperty(“Entity”,B13,“Parent”,“Finance”,“Actual”,“2021M1”) returns “#ERROR: Finance”
=XFGetMemberProperty(“Entity”,B14,“Parent”,"",“Actual”,“2021M1”) returns “#ERROR: Parent”
=XFGetMemberProperty(“Entity”,B14,“Parent”,"",“Actual”,“2021M1”) returns “#ERROR: Parent”
=XFGetMemberProperty(“Entity”,B11,“Parent”,"","",“2021M1”) returns “#ERROR: Parent”
=XFGetMemberProperty(“Entity”,B12,“Parent”,"","","") returns “#ERROR: Parent”

as a reference, I tried the same function on “currency” and that works.
=XFGetMemberProperty(“Entity”,B21,“Currency”,"","","") returns “EUR”

I am assuming that the complexity lies in the fact that the parent could vary in time (?) .

I hope some body can help on this thanks.

Phil

 
 
 
Excel Add In
  • Dominic_A's avatar
    Dominic_A
    Contributor
    3 years ago

    Hi Philip,
    If you have the parent then it should be possible to retrieve a property.
    This is a Text1 property of a member in Golfstream:

    image
    image.png1322×44 2.92 KB


    The Actual and 2019M1 can be “” and “”, if there is a default.

     

    As you can see from this example, the property name drives which property is retrieved:

    image
    image.png1304×45 2.99 KB


    The response can be True/False.
    The get relationship property returns relationship information such as e.g. Parent Sort Order:

    image
    image.png1392×97 5.17 KB


    It is possible to build member list business rules based on information about parent-child, ancestors and descendants, if the members cannot be derived with the built in member filter functions.

    • pling's avatar
      pling
      New Contributor II
      3 years ago

      Hi Dominic,

      thanks for the reply. Actually I am trying to create a similar overview as in your last screenshot. Namely:
      Column A Parent Entity
      Column B Child Entity

      How could I generate a list like that in Onestream? My approach in the post was through a combination of quickview and the function xfgetmember/relationshipproperty, but not sure whether this is the right direction.

    • Dominic_A's avatar
      Dominic_A
      Contributor
      3 years ago

      Hi Philip,
      For that List I just used the grid view on the dimension editor (similar to a cube view member filter)

       Generally I look top-down in the member list rules, as I only needed the parents or the children.
      However, I tend to use the Metadata Editor from the marketplace to define the Parent-Child relationships in new apps. XML extracts of the Metadata for a given dimension allow an extract including relationships, this will not update automatically if transferred to Excel.
      Using “.TreeDescendantsInclusive” shows a hierarchy that is not side by side.
      Having a member chosen by a combobox (with parameter) could be an option in a dashboard including ‘Spreadsheet’ with an embedded cube view, there are ways of setting the member chosen into the Spreadsheet report.
      In that case the Cube View, embedded in Spreadsheet, could have e.g. X#|!P_MyMemberParam!|.[detail] such as .Parents, .Ancestors and .descendants

      If the member filter builder does not have enough logic then resort to a member list in business rules. I would recommend loading the snippet editor from the OneStream Marketplace:

       

      • NicoleBruno's avatar
        NicoleBruno
        Valued Contributor
        3 years ago

        Hi Philip,
        If you have the parent set in a text field, you can pull it through in a UD (UD8 for example) so you can show it as a separate column in a CV. We’ve done it for something else entirely, but here’s how we set it up:

        1. Set up the master data with the text field showing as you’d like it to be reported.
        2. Set up a UD8 (ours is called Scope) as a dynamic calc with the following calc in order to pull the text field in as text (we’re using UD1 and text2):

        'This dynamic reporting member will show the Text2 property of each UD1 as an Annotation
        If api.View.IsAnnotationType()
        'Return the UD1’s Text2 setting for this text-based View member
        Return api.UD1.Text(api.Pov.UD1.MemberId,2)
        Else
        'Return a NoData Datacell.
        Return api.Data.CreateDataCellObject(0.0, True, False)
        End If

        1. Set up the CV column to pull in the details. Ours looks like this based on how we want to see all the data in the CV:
          image

        That’s a bit different than what you’ve been targeting above but simple and useful for us. Maybe not the best solution if you’re expecting the text field to change often and it can easily be pulled via a different formula. Good luck!

  • MichaelSBrokaw's avatar
    MichaelSBrokaw
    New Contributor III
    3 years ago

    Philip:

    In our instance, we built a DynamicCalc formula in UD8 dimension to get the parent of a UD4 member.

    This could be modified to get an Entity’s parent based on its relationship properties such as Parent Sort Order, Text1 to Text 8.

    ' Derive Industry for selected Entity. 

If      api.View.IsAnnotationType(api.Pov.View.MemberId) _ 
    And api.Members.IsBase(api.Pov.EntityDim.DimPk, _ 
                           api.Members.GetMemberId(api.Pov.Entity.DimTypeId, _ 
                                                   "PLConsMgmt"), _ 
                           api.Pov.Entity.MemberId) _ 
Then 
    
    Dim Result As String = String.Empty 
    
    ' Customer set as Entity Text4 property by Scenario/Time. 
    Dim Customer As String = api.Entity.Text(api.Pov.Entity.MemberId, _ 
                                             4, _ 
                                             api.Pov.Scenario.MemberId, _ 
                                             api.Pov.Time.MemberId) 
    If Not String.IsNullOrEmpty(Customer) Then 
        
        ' Customer is member of Customers UD4 dimension. 
        Dim CustomerId As Integer = api.Members.GetMemberId(DimType.UD4.Id, _ 
                                                            Customer) 
        If Not CustomerId = -1 Then 
            
            ' Get Customer's Parent. 
            Dim oParents As List(Of Member) = api.Members.GetParents(api.Pov.UD4Dim.DimPk, _ 
                                                                     CustomerId, _ 
                                                                     False) 
            If oParents.Count > 0 Then 
                For i As Integer = 0 To oParents.Count - 1 
                    
                    ' Industry is child of UD4 Customers and parent of Customer. 
                    If api.Members.IsChild(api.Pov.UD4Dim.DimPk, _ 
                                           api.Members.GetMemberId(api.Pov.UD4.DimTypeId, _ 
                                                                   "Customers"), _ 
                                           api.Members.GetMemberId(api.Pov.UD4.DimTypeId, _ 
                                                                   oParents.Item(i).Name)) _ 
                    Then 
                        Result = oParents.Item(i).Name 
                        Exit For 
                    Else 
                        If api.Members.IsBase(api.Pov.UD4Dim.DimPk, _ 
                                              api.Members.GetMemberId(api.Pov.UD4.DimTypeId, _ 
                                                                      "Customers"), _ 
                                              CustomerId) _ 
                        Then 
                            
                            ' Customer not assigned to an industry. 
                            Result = "NoIndustry" 
                        Else 
                            
                            ' UD4 member is not a customer. 
                            Return Nothing 
                        End If 
                    End If 
                Next 
            Else 
                
                ' Member has no parents. 
                Result = "#ERROR: Orphaned Customer" 
            End If 
        Else 
            
            ' Entity Text4 property does not identify a valid customer. 
            Result = "#ERROR: Invalid Customer" 
        End If 
    Else 
        
        ' Entity Text4 property is blank. Not assigned to a customer. 
        Result = "NoIndustry" 
    End If 
    
    ' Return result setting cell status as NoData for suppression. 
    Dim oDataCellEx As DataCellEx = New DataCellEx() 
    oDataCellEx.DataCell.CellStatus = New DataCellStatus(False) 
    oDataCellEx.DataCellAnnotation = Result 
    Return oDataCellEx 
End If 

Return Nothing

    In a quick view, we are able to list entities with its assigned industry.

     

    1
     
     
     
     
    • Sai_Maganti's avatar
      Sai_Maganti
      Contributor II
      3 years ago

      We use a Dashboard Data Set BR to extract base entities and immediate parent. Check out if this is of any help.

      Namespace OneStream.BusinessRule.DashboardDataSet.Entity_Parent_List
	Public Class MainClass
		Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As DashboardDataSetArgs) As Object
			Try
				Select Case args.FunctionType
					
					Case Is = DashboardDataSetFunctionType.GetDataSetNames
						Dim names As New List(Of String)()
						names.Add("CustomData")
						Return names
					
					Case Is = DashboardDataSetFunctionType.GetDataSet
						If args.DataSetName.XFEqualsIgnoreCase("CustomData") Then
						'Build a lookup of members and descriptions using a member script
							Using dbConnFW As DbConnInfo = BRAPi.Database.CreateFrameworkDbConnInfo(si)
								Using dbConnApp As DbConnInfo = BRAPi.Database.CreateApplicationDbConnInfo(si)
									'Get the Members using member script
									Dim ms As String = "E#[Your Entity Top].Base" 'replace with your top entity for base list
									Dim objDim As OneStream.Shared.Wcf.Dim = DimsWcf.GetDimUsingName(dbConnFW, dbConnApp, "Entities")
									Dim members As List(Of MemberInfo) = MetadataWcf.GetMembersUsingFilter(dbConnFW, dbConnApp, Nothing, si.WorkflowClusterPk, si.PovDataCellPk, objDim.DimPk, ms, 0, Nothing, Nothing, True, False, True)							
									
									'Build a dictionary of members and descriptions
									Dim dt As New DataTable()
									dt.Columns.Add("Entity", GetType(String))
								    dt.Columns.Add("Parent", GetType(String))
									
									Dim ent As String = String.Empty
									Dim parent As String = String.Empty
									For Each memInfo As MemberInfo In members
										ent = memInfo.Member.Name & "-" & memInfo.Member.Description
										
										Dim objDimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, "Entities")
										Dim parentList As List(Of Member) = BRApi.Finance.Members.GetParents(si, objDimPk, memInfo.Member.MemberId, False)
										parent = parentList.Item(0).Name
									
										dt.Rows.Add(ent, parent)
									Next
									Return dt							
								End Using
							End Using						
						End If
				End Select

				Return Nothing
			Catch ex As Exception
				Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
			End Try
		End Function
	End Class
End Namespace