Community Blog

8 MIN READ

Floor's Lava—Time to Branch Out: Building Your Tree

New Contributor III

26 days ago

Branching Out in OneStream: A Beginner’s Deep Dive into Building Employee Trees

Welcome to the blog! In today's post, we're diving deep into the world of hierarchical data visualization—Mission Tree-Ting style. If you’ve ever needed to see your organization’s structure at a glance or wanted a smart, efficient way to visualize complex relationships like employee-manager hierarchies, you're in the right place.

Objective:
We’ll show you how to build a robust tree structure using VB.NET that transforms raw data into an intuitive, expandable TreeView. By leveraging a custom DataAdapter and binding it to a dashboard component, you'll learn to effortlessly bring your data to life—no messy code left behind!

Use Case:
Imagine managing an organizational chart where each node represents an employee and their reporting relationships. Whether you're in HR, IT, or any department that thrives on clear, visual insights, this solution provides a reusable method to:

Parse and filter your data via dynamic SQL queries.
Construct a hierarchical tree with unique nodes and child collections.
Seamlessly embed this TreeView into your OneStream dashboard, allowing for interactive exploration of your organizational structure.

So, grab your coding toolkit and get ready to transform your data into a dynamic tree that not only meets everyday business needs but also adds a touch of Mission Impossible flair to your projects. Let's get started on turning the impossible into possible—tree style!

Although this example demonstrates a use case from People Planning, the same approach can be applied to any dataset with a Parent/Child hierarchy — such as product hierarchies, organizational structures, Capital Planning or cost centers.

Note: The method described in this blog is one of many possible ways to visualize and manage hierarchical data in OneStream. Depending on your business requirements, alternate implementations may better suit your needs.

Please refer below Blogs related to the Tree View

Introduction To Tree Views | OneStream Community
How to build a Multi-Select TreeView | OneStream Community

Overview

What is Tree View?

The TreeView is primarily used for intuitive navigation and visualization of hierarchical relationships. It helps users quickly identify structural paths and validate lineage, making it useful for both data review and administrative control.

Generating Tree View

Our function, GetTreeViewEmpMgr, performs the following steps:

Constructs an SQL Query:
The query selects employee data along with manager names. A NULL value for the manager indicates that the employee is at the top level (a root node).
Executes the Query and Retrieves a DataTable:
Using a database connection, the function executes the SQL query and stores the result in a DataTable named "Employee".
Builds the Tree Structure:
The function uses custom classes—XFTreeItem and XFTreeItemCollection—along with a dictionary called nodesByName to construct the tree:
- XFTreeItem represents each node in the tree. It holds properties such as the display text (HeaderText), state information (bold, selected, expanded), visual properties (colors and images), and a list of child nodes.
- XFTreeItemCollection is a container for the tree’s nodes (typically the root nodes) and provides methods for creating a DataSet from the entire tree.
- The nodesByName dictionary is used to quickly look up and manage nodes by their unique identifier (in this case, by name), ensuring that each node is only created once and can easily have children added later.
Returns the Tree as a DataSet:
Finally, the tree is converted into a DataSet using treeItems.CreateDataSet(si) and returned for UI rendering.

Constructs an SQL Query

The data is retrieved from the XFW_PLP_Register (People Planning Register) based on the current Workflow Profile, Time, and Scenario — all passed as parameters to ensure contextual relevance.

Select
 CASE WHEN M.RegisterID = E.RegisterID THEN NULL 
 Else Concat(M.FirstName,' ',M.LastName) End as 'Parent'
, Concat(E.FirstName,' ',E.LastName) as 'Child'
 FROM XFW_PLP_Register E 
LEFT JOIN XFW_PLP_Register M ON E.Code11 = M.RegisterID 
Where  
E.Status <> 'NewHire' And
   E.WFScenarioName = '" & WFScenarioName & "' And E.WFTimeName = '" & WFTimeName & "' And E.WFProfileName =  '" & WFProfileName & "' And 
   (M.WFScenarioName = '" & WFScenarioName & "' And M.WFTimeName = '" & WFTimeName & "' And M.WFProfileName = '" & WFProfileName & "' OR M.RegisterID IS NULL)

For each employee, we extract the First Name and Last Name, along with their manager’s corresponding First Name and Last Name. In this configuration, the manager's Employee ID is stored in the Code11 field. By performing a self-join on the XFW_PLP_Register table using this reference, we can resolve and display the appropriate manager name for every employee.

Emphasis on Key Components

XFTreeItem

The XFTreeItem class is the building block of our tree structure. Each XFTreeItem represents a node that can have:

HeaderText: The display text for the node.
Visual Properties: Such as text color, images (using properties like imageSource and imageName), and formatting (bold, enabled, selected, expanded).
Children Collection: A list that holds any child nodes, making the node hierarchical.
Additional Data: You can attach other metadata as needed, making XFTreeItem flexible for various applications.

 ' Common visual properties.
Dim textColour As String = XFColors.Black.Name
Dim imageSource As String = XFImageFileSourceType.ClientImage
Dim imageName As String = XFClientImageTypes.StatusGrayBall.Name
Dim isBold As Boolean = False
Dim isEnabled As Boolean = True
Dim isSelected As Boolean = False
Dim isExpanded As Boolean = False

In our code, we create new XFTreeItem objects for every child and parent as needed, ensuring that each node correctly reflects its employee or manager data.

Initializing the node with default properties

' Create a node for the child.
Dim childNode As New XFTreeItem(childName, childName, textColour, isBold, isEnabled, isSelected, isExpanded, imageSource, imageName, childName, Nothing)

updating the parent node properties

' Create a new parent node if it doesn't exist.
   parentNode = New XFTreeItem(parentName, parentName, textColour, isBold, isEnabled, isSelected, True, imageSource, imageName, parentName, Nothing)

'Updating the properties of parent node
If parentNode.Children Is Nothing Then
	parentNode.IsBold = True
	parentNode.IsExpanded =True
	parentNode.Children = New List(Of XFTreeItem)
		
End If
parentNode.HeaderText = parentName+ " ("+(parentNode.Children.Count+1).ToString+")"

'Adding the child node to the parent's children collection
parentNode.Children.Add(childNode)

XFTreeItemCollection

The XFTreeItemCollection class serves as the container for the entire tree. It typically holds the collection of root nodes and offers helper functions to manipulate or output the tree. In our function, after building the tree, we call:

Return treeItems.CreateDataSet(si)

This method converts the tree structure into a DataSet, making it easier to bind to UI controls like a TreeView. By using a dedicated collection class, we encapsulate all tree-related functionality, keeping the code modular and maintainable.

The Item Table holds the detailed data for each node.

The Relationship Table captures how these nodes relate to each other (i.e., which node is the parent of which).

Note: These are random names not any Org data

nodesByName Dictionary

The nodesByName dictionary is a crucial element in building the tree. Its main purposes are:

Ensuring Uniqueness: It prevents duplicate creation of nodes by keeping track of every node by its unique name (or identifier).
Facilitating Lookup: When processing a new row, the dictionary is quickly checked to see if a node already exists for the given parent or child. This way, if a parent node is referenced later, it is easily found and updated.
Simplifying Tree Building: By centralizing node management, it simplifies the process of attaching children to the correct parent nodes.

' If there is a parent, check if the parent node already exists.
Dim parentNode As XFTreeItem = Nothing
If nodesByName.ContainsKey(parentName) Then
	parentNode = nodesByName(parentName)
	BRApi.ErrorLog.LogMessage(si,"Parent is there: "+parentNode.HeaderText)
Else
	' Parent does not exist yet; create it as a root node.
	parentNode = New XFTreeItem(parentName, parentName, textColour, isBold, isEnabled, isSelected, True, imageSource, imageName, parentName,Nothing)

	treeItems.TreeItems.Add(parentNode)
	nodesByName(parentName) = parentNode
	
End If

This approach avoids the overhead of scanning a list for a matching node each time and guarantees that each node appears only once in the tree.

The Complete Code

Below is the complete VB.NET code for the GetTreeViewEmpMgr function with detailed comments explaining the use of XFTreeItem, XFTreeItemCollection, and nodesByName.

Public Function GetTreeViewEmpMgr(ByVal si As SessionInfo, ByVal args As DashboardDataSetArgs) As DataSet
	Try
		
		Dim sql As New Text.StringBuilder()
		Dim WFProfileName As String = args.NameValuePairs.XFGetValue("WFProfileName")
		Dim WFScenarioName As String = args.NameValuePairs.XFGetValue("WFScenarioName")
		Dim WFTimeName As String = args.NameValuePairs.XFGetValue("WFTimeName")

		sql.append("Select")
		sql.Append(" CASE WHEN M.RegisterID = E.RegisterID THEN NULL ")
		sql.append(" Else Concat(M.FirstName,' ',M.LastName) End as 'Parent'")
		sql.append(", Concat(E.FirstName,' ',E.LastName) as 'Child'")
		sql.append(" FROM XFW_PLP_Register E ")
		sql.append("LEFT JOIN XFW_PLP_Register M ON E.Code11 = M.RegisterID ")
		sql.append("Where ") 
		sql.Append("E.Status <> 'NewHire' And")
		sql.append("   E.WFScenarioName = '" & WFScenarioName & "' And E.WFTimeName = '" & WFTimeName & "' And E.WFProfileName =  '" & WFProfileName & "' And ")
		sql.append("   (M.WFScenarioName = '" & WFScenarioName & "' And M.WFTimeName = '" & WFTimeName & "' And M.WFProfileName = '" & WFProfileName & "' OR M.RegisterID IS NULL)")
		BRApi.ErrorLog.LogMessage(si,sql.ToString)
		
		Dim dt As DataTable
		'Execute the query
		Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
			dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString(), False)
			dt.TableName = "Employee"
		End Using
		
		' Create the main tree collection.
		Dim treeItems As New XFTreeItemCollection
		
		' Dictionary to keep track of nodes by name.
		Dim nodesByName As New Dictionary(Of String, XFTreeItem)()
	
		 ' Common visual properties.
		Dim textColour As String = XFColors.Black.Name
		Dim imageSource As String = XFImageFileSourceType.ClientImage
		Dim imageName As String = XFClientImageTypes.StatusGrayBall.Name
		Dim isBold As Boolean = False
		Dim isEnabled As Boolean = True
		Dim isSelected As Boolean = False
		Dim isExpanded As Boolean = False
		
		#Region "Buid Tree"
		' Process each row from the DataTable.
		Dim i As Integer = 0
		
		For Each row As DataRow In dt.Rows

			Dim parentName As String = ""
			If Not IsDBNull(row("Parent")) Then
				parentName = row("Parent").ToString().Trim()
			End If				    
			Dim childName As String = row("Child").ToString().Trim()

			' Create a node for the child.
			Dim childNode As New XFTreeItem(childName, childName, textColour, isBold, isEnabled, isSelected, isExpanded, imageSource, imageName, childName, Nothing)
			BRApi.ErrorLog.LogMessage(si,"Adding to Child Node: "+childNode.HeaderText)
			
			If String.IsNullOrEmpty(parentName) Then
				' If there is no parent, then this is a root node.
				treeItems.TreeItems.Add(childNode)
				nodesByName(childName) = childNode
			Else
				' If there is a parent, check if the parent node already exists.
				Dim parentNode As XFTreeItem = Nothing
				If nodesByName.ContainsKey(parentName) Then
					parentNode = nodesByName(parentName)
				Else
					' Parent does not exist yet; create it as a root node.
					parentNode = New XFTreeItem(parentName, parentName, textColour, isBold, isEnabled, isSelected, True, imageSource, imageName, parentName,Nothing)
					treeItems.TreeItems.Add(parentNode)
					nodesByName(parentName) = parentNode
				End If

				' Add the child node to the parent's children collection.
				If parentNode.Children Is Nothing Then
						parentNode.IsBold = True
						parentNode.IsExpanded =True
						parentNode.Children = New List(Of XFTreeItem)
				End If
				parentNode.HeaderText = parentName+ " ("+(parentNode.Children.Count+1).ToString+")"
				parentNode.Children.Add(childNode)

				' Also add the child node to the dictionary.
				If Not nodesByName.ContainsKey(childName) Then
					nodesByName.Add(childName, childNode)
				End If
			End If			
		Next
		#End Region
		Return treeItems.CreateDataSet(si)
	Catch ex As Exception
		Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
	End Try
End Function

How to Call the Employee-Manager Tree Code via DataAdapter

The code in the GetTreeViewEmpMgr function is designed to be invoked from the DataAdapter layer using a parameterized string. This allows you to easily pass in filtering values and trigger the building of the hierarchical tree structure based on the specified employee-manager relationships.

Setting up the data adapter

{TreeView}{EmpMgr}{WFProfileName= [PLP_US.PeoplePlanning],WFScenarioName=|WFScenario|,WFTimeName=|WFTime|}

Attach the DataAdapter to the TreeView and Embed it into the Dashboard:

Attach and Bind:
Create a TreeView component in your application and bind the DataSet produced by the GetTreeViewEmpMgr function directly to this TreeView. This binding ensures that the hierarchical data is rendered correctly in the TreeView control

Embed into the Dashboard:
Once the TreeView is populated, embed it into your dashboard. This integration allows end-users to interact with and explore the employee-manager hierarchy in a visually intuitive manner.

Together, these steps transform raw data into a dynamic, interactive component that sits proudly on your dashboard—just like a successful mission from Mission Impossible. Your organizational structure is now displayed in a neat, expandable tree format, and your mission is accomplished!