Just posting a minor update on the code. Changed the file delimiter to Tab (it was causing issues with members containing special characters) and changed the approach of the IsBase Field.
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.Extender.XF_ExportDimsWithProperties
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
'Returns All dimensions in parent/child format including Member Properties
'Fields include Parent, Child, Child Description, RowId, Generation Number, Text1-Text8
Dim sScenarioType As String = args.NameValuePairs.XFGetValue("ScenarioType", String.Empty)
Dim Time As String = args.NameValuePairs.XFGetValue("Time", String.Empty)
Dim ScenarioTypeId As Integer = ScenarioType.GetItem(sScenarioType).Id
Dim TimeId As Integer = BRApi.Finance.Members.GetMemberId(si,dimtypeId.Time,Time)
Dim FileName As String = args.NameValuePairs.XFGetValue("FileName", String.Empty)
'Get parent/child Data Table with Required Properties:
Dim dtTotalDim As New DataTable
'Will Extract All Members across All Dimension Types:
Dim topMbr As String = "Root"
'Loop through all Dimensions Type:
For Each sDimType As DimType In DimType.GetAllDimTypes
Dim sDimList As List (Of [Dim]) = BRApi.Finance.Dim.GetDims(si,sDimType.Id)
'Process All Dims By DimType and saves a Data Table with Unique Parent/Child Relationships:
Dim ParentChildTable As New DataTable
For Each sDim As [Dim] In sDimList
Dim sDimTable As DataTable = Me.GetParentChildTable(si,api,sDim.name,sDimType.Id,topMbr)
ParentChildTable.Merge(sDimTable)
Next sDim
'Creates a Unique Parent/Child Data Table:
Dim UniqueParentChildTable As DataTable = ParentChildTable.DefaultView.ToTable(True, {"Parent","Child"})
'Process Relationships Data Table and re-orders members to get the right dimension Sort Order across extended dims:
Me.GetHirerachyWithPropertiesDataTable (si, api, sDimType.Id, topMbr,ScenarioTypeId,TimeId,UniqueParentChildTable,dtTotalDim)
Next sDimType
'Exports and saves in the User Temp Folder:
Me.ExportToCSV(si,globals,FileName,dtTotalDim)
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Private Function GetParentChildTable (si As SessionInfo, api As FinanceRulesApi, dimName As String, dimTypeId As Integer, topMbr As String) As DataTable
Try
'Initialize table
Dim dtDim As New DataTable
dtDim.Columns.Add("Parent", GetType(String))
dtDim.Columns.Add("Child", GetType(String))
dtDim.Columns.Add("DimName", GetType(String))
dtDim.Columns.Add("RowId", GetType(Integer))
dtDim.Columns.Add("Gen", GetType(Integer))
'Initialize stack variables
Dim stk As Stack(Of StkMbr) = New Stack(Of StkMbr)
Dim stkItem As StkMbr
Dim dimPk As DimPk = BRApi.Finance.Dim.GetDimPk(si, dimName)
Dim rowId As Integer = 1
'Push top member onto stack
Dim stkTop As New StkMbr
stkTop.Name = topMbr
stkTop.Parent = "<root>"
stkTop.Gen = 0
stk.push(stkTop)
'Get child members and place on stack until stack is empty
Do While stk.Count > 0
stkItem = stk.Pop
dtDim.Rows.Add(stkItem.Parent, stkItem.Name, dimName,rowId,stkItem.Gen)
rowID= rowId + 1
'Get children of current member
Dim mbrId As Integer = BRapi.Finance.Members.GetMemberId(si, dimTypeId, stkItem.Name)
Dim mbrChildren As List(Of Member) = BRApi.Finance.Members.GetChildren(si,dimPk, mbrId, Nothing)
mbrChildren.Reverse
If Not mbrChildren Is Nothing Then
For Each child As Member In mbrChildren
Dim stkChild As New StkMbr
stkChild.Name = child.Name
stkChild.Parent = stkItem.Name
stkChild.Gen = stkItem.Gen + 1
stk.Push(stkChild)
Next
End If
Loop
Return dtDim
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
Sub GetHirerachyWithPropertiesDataTable (si As SessionInfo, api As FinanceRulesApi, dimTypeId As Integer, topMbr As String, ScenarioTypeId As Integer, Timeid As Integer, ByVal UniqueParentChildTable As DataTable, ByRef DataTable As DataTable)
Try
'Initialize table
Dim dtDim As DataTable = DataTable
'Initialise Table and Add Colums if Empty:
If DataTable.Rows.Count.Equals(0) Then
dtDim.Columns.Add("SORT_ORDER", GetType(Integer))
dtDim.Columns.Add("DIMENSION", GetType(String))
dtDim.Columns.Add("MEMBER", GetType(String))
dtDim.Columns.Add("MEMBER_DESC", GetType(String))
dtDim.Columns.Add("BASE", GetType(Boolean))
dtDim.Columns.Add("PARENT", GetType(String))
dtDim.Columns.Add("CURRENCY", GetType(String))
dtDim.Columns.Add("Text1", GetType(String))
dtDim.Columns.Add("Text2", GetType(String))
dtDim.Columns.Add("Text3", GetType(String))
dtDim.Columns.Add("Text4", GetType(String))
dtDim.Columns.Add("Text5", GetType(String))
dtDim.Columns.Add("Text6", GetType(String))
dtDim.Columns.Add("Text7", GetType(String))
dtDim.Columns.Add("Text8", GetType(String))
End If
'Initialize stack variables
Dim stk As Stack(Of StkMbr) = New Stack(Of StkMbr)
Dim stkItem As StkMbr
Dim rowId As Integer = 1
Dim MembProperties As New Object
'Defines Member Properties Object based on DimType:
Select Case dimTypeId
Case DimType.Entity.Id
MembProperties = BRApi.Finance.Entity
Case DimType.Account.Id
MembProperties = BRApi.Finance.Account
Case DimType.Flow.Id
MembProperties = BRApi.Finance.Flow
Case DimType.UD1.Id, DimType.UD2.Id, DimType.UD3.Id, DimType.UD4.Id, DimType.UD5.Id, DimType.UD6.Id, DimType.UD7.Id, DimType.UD8.Id
MembProperties = BRApi.Finance.UD
Case DimType.Scenario.Id
MembProperties = BRApi.Finance.Scenario
Case Else
MembProperties = Nothing
End Select
Dim MemberDisplay As New memberDisplayOptions
Dim DimDisplay As New DimDisplayOptions
MemberDisplay.IncludeMemberDim = True
'Push top member onto stack
Dim stkTop As New StkMbr
stkTop.Name = topMbr
stkTop.Parent = "<root>"
stkTop.Gen = 0
stk.push(stkTop)
'Get child members and place on stack until stack is empty
Do While stk.Count > 0
stkItem = stk.Pop
Dim MembDimName As String = $"RootDim"
Dim Currency As String = ""
Dim mbrDesc As String = ""
Dim MembId As Integer = -1
Dim text1 = ""
Dim text2 = ""
Dim text3 = ""
Dim text4 = ""
Dim text5 = ""
Dim text6 = ""
Dim text7 = ""
Dim text8 = ""
'Pull member Properties to add into Table:
If Not stkItem.Name.XFEqualsIgnoreCase("Root") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("EntityDefault") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("UD1Default") AndAlso Not stkItem.Name.XFEqualsIgnoreCase("None") Then
' mbrDesc = BRAPI.Finance.Members.GetMember(si, dimTypeId, stkItem.Name).Description
mbrDesc = Brapi.Finance.Members.GetMemberInfo(si,dimTypeId,stkItem.Name,True,Nothing,MemberDisplay).Description
MembId = Brapi.Finance.Members.GetMember(si,dimTypeId,stkItem.Name).MemberId
MembDimName = Brapi.Finance.Members.GetMemberInfo(si,dimTypeId,stkItem.Name,True,Nothing,MemberDisplay).MemberDim.Name
If dimTypeId.equals(DimType.Entity.Id) Then
Currency = MembProperties.GetLocalCurrency(si,MembId).Name
End If
If Not MembProperties Is Nothing
If dimType.IsUDDimType(dimTypeId) Then
text1 = MembProperties.Text(si,dimTypeId,MembId,1,ScenarioTypeId,TimeId)
text2 = MembProperties.Text(si,dimTypeId,MembId,2,ScenarioTypeId,TimeId)
text3 = MembProperties.Text(si,dimTypeId,MembId,3,ScenarioTypeId,TimeId)
text4 = MembProperties.Text(si,dimTypeId,MembId,4,ScenarioTypeId,TimeId)
text5 = MembProperties.Text(si,dimTypeId,MembId,5,ScenarioTypeId,TimeId)
text6 = MembProperties.Text(si,dimTypeId,MembId,6,ScenarioTypeId,TimeId)
text7 = MembProperties.Text(si,dimTypeId,MembId,7,ScenarioTypeId,TimeId)
text8 = MembProperties.Text(si,dimTypeId,MembId,8,ScenarioTypeId,TimeId)
Else If dimType.Scenario.Id.Equals(dimTypeId)
text1 = MembProperties.Text(si,MembId,1)
text2 = MembProperties.Text(si,MembId,2)
text3 = MembProperties.Text(si,MembId,3)
text4 = MembProperties.Text(si,MembId,4)
text5 = MembProperties.Text(si,MembId,5)
text6 = MembProperties.Text(si,MembId,6)
text7 = MembProperties.Text(si,MembId,7)
text8 = MembProperties.Text(si,MembId,8)
Else
text1 = MembProperties.Text(si,MembId,1,ScenarioTypeId,TimeId)
text2 = MembProperties.Text(si,MembId,2,ScenarioTypeId,TimeId)
text3 = MembProperties.Text(si,MembId,3,ScenarioTypeId,TimeId)
text4 = MembProperties.Text(si,MembId,4,ScenarioTypeId,TimeId)
text5 = MembProperties.Text(si,MembId,5,ScenarioTypeId,TimeId)
text6 = MembProperties.Text(si,MembId,6,ScenarioTypeId,TimeId)
text7 = MembProperties.Text(si,MembId,7,ScenarioTypeId,TimeId)
text8 = MembProperties.Text(si,MembId,8,ScenarioTypeId,TimeId)
End If
End If
End If
Dim IsBase As Boolean = False
'Looks Up for Children:
Dim result() As DataRow = UniqueParentChildTable.Select("[Parent] = '" & stkItem.Name & "'")
If result.Count > 0 Then
'Loop through the result backwards to get the stack in the right order:
For i As Integer = result.Count - 1 To 0 Step -1
Dim stkChild As New StkMbr
stkChild.Name = result(i).Item("Child")
stkChild.Parent = stkItem.Name
stkChild.Gen = stkItem.Gen + 1
stk.Push(stkChild)
Next
Else 'If Member Has No Children, IsBase=True
IsBase = True
End If
'Adds into the Table:
dtDim.Rows.Add(rowID,MembDimName,stkItem.Name,mbrDesc,IsBase,stkItem.Parent,Currency,Text1,Text2,Text3,Text4,Text5,Text6,Text7,Text8)
rowID= rowId + 1
Loop
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub
'Data structure for stack
Private Structure StkMbr
Property Name As String
Property Parent As String
Property Gen As Integer
End Structure
Private Sub ExportToCSV (ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal FileName As String, ByVal DataTable As DataTable)
Try
'1. Declare target dims and DataTable
Dim csvContent As New Text.StringBuilder
Dim qualifier As String = StageConstants.ParserDefaults.DefaultQuoteCharacter
' Dim delimiter As String = StageConstants.ParserDefaults.DefaultDelimiter
' Dim delimiter As String = " "
Dim delimiter As String = constants.vbTab
'Create DataTable
Dim dt As DataTable = DataTable
'2. If rows returned, create CSV file content
If Not dt Is Nothing Then
Dim csv As New Text.StringBuilder
'Declare string builder to store csv text
csv.AppendLine("")
csv.AppendLine("")
'Create column list from dt
Dim colList As New List(Of String)
For Each dc As DataColumn In dt.Columns
colList.Add(dc.ColumnName)
Next
'Write the column Definitions
Dim colDescs As New Text.StringBuilder
For Each colName As String In colList
Dim dc As DataColumn = dt.Columns(colName)
'Check to see if the column data type requires quotes
Dim quotesRequired As Boolean = False
If dc.ColumnName.Contains(delimiter) Then
quotesRequired = True
End If
If colDescs.Length > 0 Then
colDescs.Append(delimiter)
If quotesRequired Then colDescs.Append(qualifier)
colDescs.Append(dc.ColumnName)
If quotesRequired Then colDescs.Append(qualifier)
Else
If quotesRequired Then colDescs.Append(qualifier)
colDescs.Append(dc.ColumnName)
If quotesRequired Then colDescs.Append(qualifier)
End If
Next
csvContent.AppendLine(colDescs.ToString)
'Write the Data Rows
For Each dr As DataRow In dt.Rows
Dim rowVals As New Text.StringBuilder
For Each colName As String In colList
Dim dc As DataColumn = dt.Columns(colName)
Dim rowVal As String = dr(dc.Ordinal).ToString
'Check to see if the row data type requires quotes
Dim quotesRequired As Boolean = False
If rowVal.Contains(delimiter) Then
quotesRequired = True
End If
If rowVals.Length > 0 Then
rowVals.Append(delimiter)
If quotesRequired Then rowVals.Append(qualifier)
rowVals.Append(rowVal)
If quotesRequired Then rowVals.Append(qualifier)
Else
If quotesRequired Then rowVals.Append(qualifier)
rowVals.Append(rowVal)
If quotesRequired Then rowVals.Append(qualifier)
End If
Next
csvContent.AppendLine(rowVals.ToString)
Next
'3. Write File to User Temp Folder (to open from dashboard button)
' This folder is cleared when user session expires
Dim fileBytes As Byte() = Encoding.UTF8.GetBytes(csvContent.ToString)
BRApi.Utilities.SaveFileBytesToUserTempFolder(si, si.UserName, fileName, fileBytes)
'Add this text To button that calls Function. Navigation Action. Open File
'FileSourceType=Application, UrlOrFullFileName=[Internal/Users/|UserName|/Temp/Test.csv], OpenInXFPageIfPossible=False
End If
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Sub
End Class
End Namespace
Great Stuff Albert - would be great to convert it to a workspace utility, using assemblies, when you have time. For example, some drop downs for Scenario Type, Time, File name and maybe location (for users without access to the systems tab - file explorer)
Yes, I am working on it 🙂
Trying to pass a value from DM as DIMType and extract particular DIM. Have you done that?
I have added if statement and its working but cant get to work when I pass value.
Hi pranav46,
This is definitely possible. You can either pass in the DimType Name as String or the ID as Integer.
If you send me the BR snippet I can help you out.
Best,
Albert
Albert,
I passed String and converted to integer and use it. Its working from dashboard and working on adding more properties in column.
Only problem i have is sometimes it extract multiple files for same DIM. I have to spend more time to figure out the bug.
Thanks
Pranav
