For those of us still using earlier versions of TM1 where Active Forms are not available, below is a set of VBA code that would enable you to recreate the dimension tree in your excel reports .
In this example, we have a list of sales area managers from the Sales Managers dimension derived using the ELCOMPN formula. The objective is to make the list resemble the dimension hierarchy in TM1 as shown below.
Below is the list of Sales Managers.The numbers along side the elements are the indentation value and also serves as the grouping levels.
VBA Code for the Indentation
For row = firstrow to lastrow
indentval = CDec(Cells(row, col2).Value)
If indentval> 0 Then
Cells(row, col).Select
Selection.InsertIndentindentval
End If
Next row
Note : col refers to the column with the names of sales managers and col2 refers to the column that has the indentation value
Code for Grouping
For z = firstrow To lastrow
grplvl = Cells(z,col2).Value
If grplvl = 3 Then
s = z + 1
Do While Cells(s, col2).Value = 4
s = s + 1
Loop
Rows(z + 1 & ":" & s - 1).Select
Selection.Rows.Group
z = s - 1
End If
Next z
For the indentation VBA code, I would use:
Application.ScreenUpdating = False
For Each Rng In Columns(col2).SpecialCells(2, 1)
If Rng.Value Then Cells(col, 1).InsertIndent Rng.Value
Next
And for grouping:
Application.ScreenUpdating = False
For Z = firstrow To lastrow
If Cells(Z, col2).Value = 3 Then
s = Z + 1
Do While Cells(s, col2).Value = 4
s = s + 1
Loop
Rows(Z + 1 & “:” & s – 1).Group
Z = s – 1
End If
Next