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 =...
Excel Add In
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