Ho to extract a hierarchy for any dimension in Level format in OneStream?

[OSAdmin]

Originally posted by Vignesh Mohan

Hi, How can we extract the hierarchy of any dimension in an excel in a Level based format starting from top most level to the bottom most level for every base member ? Basically I do not want a Parent-Child format extract. Please find the attachments for the sample screen shot.

[CAIGuySeanF]

Any updates on this request?

[SxD]

Bump. Would also like to know if there has been a solution built for this export.

[Nikpowar97]

I wrote  a code from my end for my Account dimensions (you can replicate for others)

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine

Namespace OneStream.BusinessRule.DashboardDataSet.AccountHierarchyTable
	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.GetDataSet
						
						If args.DataSetName.XFEqualsIgnoreCase("GetAccountStructure") Then
							
							
								Dim Acct As String = args.NameValuePairs.XFGetValue("Acct")  'Get Top Parent
															
							
								Dim Sql As String = "SELECT Member.Name As L1,Member.Description As Description_L1 FROM Relationship RIGHT OUTER JOIN Member ON Relationship.DimTypeId = Member.DimTypeId AND Relationship.ChildID = Member.MemberId WHERE (Member.DimTypeId = 5) AND Member.DimId IN (SELECT DimId FROM Dim WHERE Name = 'DIMNAME_Accounts') AND (Relationship.ParentId = '-2') and Member.Name = '"& Acct &"' ORDER BY Member.Name"
								
								
								Dim Dt As New DataTable
								
								Using AppCon As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
								
									dt = BRApi.Database.ExecuteSql(AppCon,Sql,True)	
									
								End Using
								
								
								Dim i As Integer = 1
								Dim conditionMet As Boolean = False
																
								Dim DataTable As New DataTable

								DataTable.Columns.Add("L1")

								DataTable.Columns.Add("Description_L1")
															
								Dim DelRows As New List(Of DataRow)()  'NIK

								While Not conditionMet

									Dim rowsToAdd As New List(Of DataRow)()


									For Each Dr As DataRow In Dt.Rows
										
										Dim ColName As String = "L" & i.ToString
										
										If dr(ColName).ToString = String.Empty Then
											Continue For
										End If
										
										Dim ChildList As List(Of Member) = Brapi.Finance.Members.GetChildren(si,Brapi.Finance.Dim.GetDimPk(si,"DIMNAME_Accounts"),Brapi.Finance.Members.GetMemberId(si,Dimtypeid.Account,dr(ColName).ToString))

										If ChildList.Count > 0 Then 
												
											For Each Child As Member In ChildList
												
												
												
												Dim DataTBRow As DataRow = DataTable.newRow
												
												
												For Z As Integer = 1 To i 
													
													DataTBRow("L" & Z.ToString) = Dr("L" & Z.ToString)
													DataTBRow("Description_L" & Z.ToString) = Dr("Description_L" & Z.ToString)

													
												Next
												
												If Not dataTable.Columns.Contains("L" & (i+1).ToString) Then
												
													DataTable.Columns.Add("L" & (i+1).ToString)
													DataTable.Columns.Add("Description_L" & (i+1).ToString)
												
												End If
	
												DataTBRow("L" & (i+1).ToString) = Child.Name
												DataTBRow("Description_L" & (i+1).ToString) = Child.Description
												
												
												If BRApi.Finance.Members.HasChildren(si,Brapi.Finance.Dim.GetDimPk(si,"DIMNAME_Accounts"),Brapi.Finance.Members.GetMemberId(si,Dimtypeid.Account,Child.Name)) Then 'NIK
													
													DelRows.Add(DataTBRow)
													
												End If
												
												rowsToAdd.Add(DataTBRow)
												
												conditionMet = False
												
											Next
													
										Else
												
											Continue For
											
										End If
										
									Next
									
									
									If Not dataTable.Columns.Contains( "L" & (i+1).ToString) Then
												
										conditionMet = True
												
									End If
									
									i = i + 1
									

									For Each row In rowsToAdd
									    DataTable.Rows.Add(row)
									Next
									

									dt = DataTable

											
								End While
'								Next


								
								Dim LNum As Integer = 0
							
								For Each Dc As DataColumn In Dt.Columns
									
									If Dc.ColumnName.Contains("L") And Not Dc.ColumnName.Contains("Description_L")Then 
										
										If CInt(StringHelper.ReplaceString(Dc.ColumnName,"L","",True)) > 0 Then
										
											LNum = CInt(StringHelper.ReplaceString(Dc.ColumnName,"L","",True))
																				
										End If
										
									End If
									
								Next
						
								For Each rowToRemove In DelRows
								    Dt.Rows.Remove(rowToRemove)
								Next
								
	
							
								For Each Dr1 As DataRow In Dt.Rows
									
									For L As Integer = LNum To 1 Step -1
										
										Dim ClmnName As String = "L" & L
											
										If L = LNum And Not (Dr1.IsNull(ClmnName) OrElse Dr1(ClmnName).ToString() = String.Empty) Then
											
											
											Exit For
										
										Else If Dr1.IsNull(ClmnName) OrElse Dr1(ClmnName).ToString() = String.Empty Then
											
	
											
											Continue For
											
										Else 
											Dr1("L" & LNum) = Dr1(ClmnName).ToString()
											Dr1("Description_L" & LNum) = Dr1("Description_" & ClmnName).ToString()
											Dr1(ClmnName) = String.Empty
											Dr1("Description_" & ClmnName) = String.Empty
												
											
											Exit For
	
										End If
										
									Next
								
								Next
	
								Dt.Columns("L" & LNum).ColumnName = "Base Account"
								Dt.Columns("Description_L" & LNum).ColumnName = "Base Account Description"
								
											
								Return Dt
									
						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