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.
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
Any updates on this request?
Bump. Would also like to know if there has been a solution built for this export.
