Indenting dimension elements in Excel

Post Reply
Wim Gielis
MVP
Posts: 1896
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Indenting dimension elements in Excel

Post by Wim Gielis » Tue Jan 08, 2019 1:26 pm

Hi all,

Challenge of the day :lol:

We start with a flat list of elements and we want to add both indenting (based on the hierarchical relationships) and also an indicator for N / C / S type elements:
02.png
02.png (32.61 KiB) Viewed 289 times
The start is column B, and we want to have the indentation like in the Cube Viewer.
Adding "icons" is relatively straightforward.
But the indentation is not. I added indentation based on the level of the element (including consolidations) but then it's different from the Cube viewer.
The thing is that the Cube Viewer respects parent-child relations whereas a simple ELLEV does not.

In an active form we have the TM1RPTELLEV function but without active form and TM1RPTROW function this cannot be used.
The idea is not to copy/paste from Excel in the Subset Editor, copy/paste back to Excel, adding coloured icons.
The goal is to simulate the indentation like in the Subset Editor.

Did anyone do this already ? Does TM1 Tools indent like the Subset Editor or more like the ELLEV approach above ?

Best regards,

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

bgregs
Posts: 56
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: Indenting dimension elements in Excel

Post by bgregs » Tue Jan 08, 2019 2:13 pm

Hi Wim,

Another good exercise! I don't have time to test this out right now, but I will definitely take a stab at it when I get some time! I found the following function in the VBA API that looks like it _could_ be the function that returns the right information to build out the hierarchy. Again, this is untested, but it sounds promising at least. ;)

Please note, the real relevant information appears to be taken once the returned string is passed into the supporting functions. I'll try to take a look at this, but just thought I'd share in case you wanted to get a head start!

Side note, it looks like the function that will be most useful is TM1SubsetElementDisplayLevel, but you may need information from the function in the link provided first (i.e. use the return value). Also, it looks like the indentation is pulled from a subset, which means that ad-hoc views in the subset editor that aren't saved won't have any indentation information pull (since they only exist in volatile memory). This leads me to believe that this will only work for saved subsets, but again, I will need to test. :D

https://www.ibm.com/support/knowledgece ... splay.html

Wim Gielis
MVP
Posts: 1896
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: Indenting dimension elements in Excel

Post by Wim Gielis » Tue Jan 08, 2019 2:23 pm

bgregs wrote:
Tue Jan 08, 2019 2:13 pm
Hi Wim,

Another good exercise! I don't have time to test this out right now, but I will definitely take a stab at it when I get some time! I found the following function in the VBA API that looks like it _could_ be the function that returns the right information to build out the hierarchy. Again, this is untested, but it sounds promising at least. ;)

Please note, the real relevant information appears to be taken once the returned string is passed into the supporting functions. I'll try to take a look at this, but just thought I'd share in case you wanted to get a head start!

Side note, it looks like the function that will be most useful is TM1SubsetElementDisplayLevel, but you may need information from the function in the link provided first (i.e. use the return value). Also, it looks like the indentation is pulled from a subset, which means that ad-hoc views in the subset editor that aren't saved won't have any indentation information pull (since they only exist in volatile memory). This leads me to believe that this will only work for saved subsets, but again, I will need to test. :D

https://www.ibm.com/support/knowledgece ... splay.html
Thanks !

I will look at the link. I am not against creating (and deleting) a temporary subset if that would be needed. But using the old VB/VBA API is maybe not the best approach. But still better than writing many many loops and logic ourselves in VBA.

FYI, here's the current code to have the output as in column C. Select the cells you want to indent, then run the macro.
The macro supports more functionality than I showed, so whoever wants to: steal the code and use it.

Code: Select all

Sub TM1_Indent_Elements(control As IRibbonControl)

    Const iNrOfSpacesPerLevel As Integer = 4

    Dim iLevel                As Integer
    Dim r                     As Range
    Dim sDim                  As String
    Dim Server_Dimension      As String
    Dim lTypeOfOutput         As Long
    Dim lMsgboxAddElementType As VbMsgBoxResult
    Dim sElementType_TM1      As String
    Dim sElementType_Excel    As String
    Dim sServer               As String

    On Error Resume Next

    Server_Dimension = InputBox("Please enter the dimension name" & vbNewLine & vbNewLine & _
                                "If you enter the server name : dimension name string, this server name is used", "(Server:)Dimension name", "")
    sServer = Split(Server_Dimension, ":")(0)
    sDim = Split(Server_Dimension, ":")(1)

    If Run("DIMIX", sServer & ":" & "}Dimensions", sDim) = 0 Then
        MsgBox "Please choose a valid value for server:dimension name"
        GoTo einde
    End If

    Server_Dimension = sServer & ":" & sDim

    Application.ScreenUpdating = False

    lTypeOfOutput = Application.InputBox("Do you want to:" & vbNewLine & vbNewLine & _
                                         "(1) indent cells, in the same column" & vbNewLine & _
                                         "(2) use spaces, in the same column" & vbNewLine & _
                                         "(3) distribute elements over columns" & vbNewLine & vbNewLine, "Indenting elements", 1, , , , , 1)

    If lTypeOfOutput < 1 Or lTypeOfOutput > 3 Then
        GoTo einde
    End If

    If lTypeOfOutput <> 3 Then
        lMsgboxAddElementType = MsgBox("Do you want to add the element type (like Greek Sigma and small n/s (choose: Yes) or not (choose: No) ?", vbYesNoCancel, "Element")
    Else
        lMsgboxAddElementType = vbNo
    End If

    For Each r In Selection.Cells

        If Run("DIMIX", Server_Dimension, r.text) > 0 Then

            iLevel = Run("DNLEV", Server_Dimension) - Run("ELLEV", Server_Dimension, r.text) - 1

            Select Case lMsgboxAddElementType

            Case vbcancel: GoTo einde

            Case vbYes

                sElementType_TM1 = Run("DTYPE", Server_Dimension, r.text)
                sElementType_Excel = IIf(sElementType_TM1 = "C", "S", IIf(sElementType_TM1 = "N", "#", "ab"))

                Select Case lTypeOfOutput

                Case 1

                    r.InsertIndent iLevel
                    If r.HasFormula Then
                        r.formula = "=""" & sElementType_Excel & " " & """ & " & Mid(r.formula, 2)
                    Else
                        r.value = "'" & sElementType_Excel & " " & r.text
                    End If

                Case 2

                    If r.HasFormula Then
                        r.formula = "=""" & Space(1 + iLevel * iNrOfSpacesPerLevel) & sElementType_Excel & " "" & " & Mid(r.formula, 2)
                    Else
                        r.value = "'" & Space(1 + iLevel * iNrOfSpacesPerLevel) & sElementType_Excel & " " & r.text
                    End If

                End Select

                If Not r.HasFormula Then
                    'font type change
                    With r.Characters(start:=InStr(r.text, sElementType_Excel), length:=IIf(sElementType_TM1 = "S", 2, 1)).Font
                        .name = IIf(sElementType_TM1 = "C", "Symbol", "Courier")
                        .FontStyle = "Bold"

                        .ThemeColor = IIf(sElementType_TM1 = "C", xlThemeColorAccent2, xlThemeColorAccent5)
                        .TintAndShade = -0.249977111117893
                        .ThemeFont = xlThemeFontNone

                    End With
                End If

            Case vbNo

                Select Case lTypeOfOutput

                Case 1

                    r.InsertIndent iLevel

                Case 2

                    If r.HasFormula Then
                        r.formula = "=""" & Space(1 + iLevel * iNrOfSpacesPerLevel) & """ & " & Mid(r.formula, 2)
                    Else
                        r.value = "'" & Space(1 + iLevel * iNrOfSpacesPerLevel) & r.text
                    End If

                Case 3

                    If iLevel > 0 Then
                        If r.HasFormula Then
                            r.Offset(, iLevel).formula = r.formula
                        Else
                            r.Offset(, iLevel).value = "'" & r.text
                        End If
                        r.ClearContents
                    End If

                End Select

            End Select

        End If

    Next

    Selection.Columns.AutoFit

    On Error GoTo 0

einde:

    Application.ScreenUpdating = True

End Sub
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

lotsaram
MVP
Posts: 3164
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Indenting dimension elements in Excel

Post by lotsaram » Tue Jan 08, 2019 5:06 pm

Have you not looked in the TM1 Tools VBA at the function which "exports to Excel" a rollup? It is done there with Excel grouping and style formatting. But the logic of which level and nesting the groupings is all there and works.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.

Wim Gielis
MVP
Posts: 1896
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: Indenting dimension elements in Excel

Post by Wim Gielis » Tue Jan 08, 2019 5:11 pm

lotsaram wrote:
Tue Jan 08, 2019 5:06 pm
Have you not looked in the TM1 Tools VBA at the function which "exports to Excel" a rollup?
No, I did not and I should have. Thanks for pointing out, I will have a look at it.
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

Wim Gielis
MVP
Posts: 1896
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.6
Excel Version: 2016 - Office 365
Location: Brussels, Belgium
Contact:

Re: Indenting dimension elements in Excel

Post by Wim Gielis » Tue Jan 08, 2019 10:26 pm

Hi Lotsaram,

Now I looked at it and it's marvellous. Great job !
No need to reinvent the wheel.

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 107 TM1 articles and a lot of custom code
Newest blog article: Turbo Integrator: report on progress

Post Reply