Print dimension structure as tree

[JChris]

Some years ago some kind person gave me some VBA code that enabled the user to look at a TM1 dimension, or parts of it, as a tree within an Excel spreadsheet and therefore print it. I have since moved companies and no longer have access to that code. I have had a request from one of our finance managers for this capability so that she can use them for understanding and reviewing some of the structures.

I would be grateful if anyone has or knows of any code that might be of help.

Thanks
Chris

[Jaspe]

Hi !
First sorry for my bad english

You can write a formula in Excel, without writing VBA code.

In your spreadsheet, you have in the cell A1 : "MyServer:MyDimension" where MyServer is the name of your server and MyDimension your dimension name.
In the cell A2, you can write this formula (it's in french sorry because my Excel version is french, sorry !) :
=SI(CELLULE("ligne",A2)-1<=DIMSIZ($A$1),CONCATENER(REPT(" ",(ELLEV($A$1,DFRST($A$1))-ELLEV($A$1,DIMNM($A$1,CELLULE("ligne",A2)-1,"")))*3),DIMNM($A$1,CELLULE("ligne",A2)-1,"")," - ",DIMNM($A$1,CELLULE("ligne",A2)-1,"Description")),"")

You can copy this formula in cells A3,A4,A5,.... and your can see your dimension, as a tree.

Equivalent functions in english :
SI --> IF
CELLULE --> CELL
"ligne" --> "row"
CONCATENER --> CONCATENATE
REPT --> REPT

I hope this function to display a TM1 dimension in Excel will help you !

Bye

Nadia

[Mike L]

Here is a VBA snip that lists a dimension tree using multiple columns to show levels:

Code: Select all

Sub DimensionTreeByColumn( _
        Dimension As String, _
        Node As String)
    'beginning at the active cell, list descendants
    'of Node in Dimension, with lower level elements
    'offset in columns to the right.
    
    Dim N As Integer
    Dim i As Integer
    Dim Child As String
    
    With ActiveCell
        .Value = Node
        .Offset(1, 0).Select
    End With 'ActiveCell
    N = Application.Run("ELCOMPN", Dimension, Node)
    If N > 0 Then
        ActiveCell.Offset(0, 1).Select
        For i = 1 To N
            Child = Application.Run("ELCOMP", Dimension, Node, i)
            DimensionTreeByColumn Dimension, Child
        Next i
        ActiveCell.Offset(0, -1).Select
    End If 'N > 0
End Sub 'DimensionTreeByColumn

Here is an alternative method that uses a single column with formatting to show the levels:

Code: Select all

Sub DimensionTreeByIndent( _
        Dimension As String, _
        Node As String, _
        Optional Depth As Integer = 0)
    'beginning at the active cell, list descendants
    'of Node in Dimension, using a single column
    'showing level with indent format.
    
    Dim N As Integer
    Dim i As Integer
    Dim Child As String
    
    With ActiveCell
        .HorizontalAlignment = xlLeft
        .IndentLevel = Depth
        .Value = Node
        .Offset(1, 0).Select
    End With 'ActiveCell
    N = Application.Run("ELCOMPN", Dimension, Node)
    For i = 1 To N
        Child = Application.Run("ELCOMP", Dimension, Node, i)
        DimensionTreeByIndent Dimension, Child, Depth + 1
    Next i
End Sub 'DimensionTreeByIndent

These subroutines are optimized for clarity rather than performance, but are quick enough for most purposes.

Cheers!

[JChris]

Thanks for all your replies, they have been extremely useful and enlightening.

Nadia
The french wasn't too daunting and you kindly provided a dictionary, if it had been a problem, I have a boss who is French!
Your formula gives the structure of a dimension that looks like that the structure you see when you hit the "All" button in the dimension subset editor which, unfortunately, is not quite what I am looking for. However, the form of your formula has given me much food for thought.
Thanks again for you reply
Chris

Mike
Your VBA code does just what I am looking for. Since I don't do a great deal of programming your code proved useful in another way - it reminded me about recursive use of subroutines.
Many thanks
Chris