01-27-2022 11:14 AM - last edited on 05-24-2023 12:31 PM by JackLacava
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
01-27-2022 11:30 AM - last edited on 01-27-2022 11:32 AM by agoralewski
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:
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:
The response can be True/False.
The get relationship property returns relationship information such as e.g. Parent Sort Order:
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.
01-27-2022 11:33 AM
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.
01-27-2022 11:42 AM - last edited on 01-27-2022 11:46 AM by agoralewski
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:
01-27-2022 11:47 AM
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:
'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
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!
01-27-2022 11:50 AM
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.
01-27-2022 01:32 PM
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