Displaying sandbox name in excel cell

[daveed]

Hi,
Does anyone have a way of returning an active sandbox name into an Excel cell? This is so we can print from Excel, displaying the sandbox name.

[lotsaram]

Yes it is possible but not automatically without user intervention. If you are happy for the user to click an action button that runs a process containing the ServerActiveSandboxGet() and TM1User() functions then you can write to a string cell in a cube containing the }Clients dimension the name of the user's active sandbox and return the value of the cell in the Excel worksheet.

This is the only workaround I can think of.

[asingla83]

Hi lotsaram,

I am trying to achieve the same - find a way to display active sandbox name in an excel. I tried your suggested workaround and it worked fine when I tested it using admin login. But the TI process fails to run for non-admin users. I have a changed the security setting for non-admin users and they have read access to TI process. The security access is checked when I right-click on the TI process. When a non-admin user runs the process, it gives following error:

" Error getting active sandbox - no ad-hoc sandbox capability has been granted"

I do not understand this error and unfortunately couldn't find anything on this error message from google search.

Any help on this from you or anyone on the forum is much appreciated.

[Kyro]

Why not create a custom VBA function which returns the sandbox name from the toolbar combo box?
This shouldn't be too hard and will be reliable, as whenever the combo box changes the worksheet would likely be refreshed thus refreshing the function in cell.

[asingla83]

Kyro,

Thanks much for your response. Any help on how I can do it?

[Kyro]

If I get time this week I'll take a look but I'm pretty busy at the moment. Feel free to PM me after a week if I haven't come back with anything and we'll see if we're able to sort something out for you then.

[Kyro]

Code: Select all

Function getSandbox() As String

    Dim it As Long, ib As Long
    For it = 1 To CommandBars.Count
      If CommandBars(it).Name = "TM1 Sandbox" Then
        For ib = 1 To CommandBars(it).Controls.Count
          If CommandBars(it).Controls(ib).Caption = "" Then
            Dim btn As CommandBarComboBox
            Set btn = CommandBars(it).Controls(ib)
            getSandbox = btn.Text
            Exit Function
          End If
        Next ib
      End If
    Next it
    
End Function

You could make it a lot more stable than this by adding filters which ensure the correct control is selected. This is just a working starting point. Once you add this into a workbook you can use =getSandbox() within a cell and it will return the active sandbox.

Let me know how you get on.

[rduggs]

Code: Select all

Function getSandbox() As String

    Dim it As Long, ib As Long
    For it = 1 To CommandBars.Count
      If CommandBars(it).Name = "TM1 Sandbox" Then
        For ib = 1 To CommandBars(it).Controls.Count
          If CommandBars(it).Controls(ib).Caption = "" Then
            Dim btn As CommandBarComboBox
            Set btn = CommandBars(it).Controls(ib)
            getSandbox = btn.Text
            Exit Function
          End If
        Next ib
      End If
    Next it
    
End Function

You could make it a lot more stable than this by adding filters which ensure the correct control is selected. This is just a working starting point. Once you add this into a workbook you can use =getSandbox() within a cell and it will return the active sandbox.

Let me know how you get on.

Did this work for anyone? I am trying to use it in TM1 10.1, but haven`t had any success.
Any help would be greatly appreciated.

-RD