Homemade TM1 reports can get quite convoluted and users might get a hard time updating them as it is difficult to tell where some TM1 formulas are pointing to.
The excel formula auditing toolbar can be useful in such situations.

• right click next to the top bar to bring up the bars menu
• select "Formula Auditing" bar

It is possible to preformat dynamic slices by using the Edit Element Formats button in the subset editor. However that formatting is static and will not apply to new elements of a slowly changing dimension. Also it takes a long time to load/save when you try to apply it to more than a few dozen elements.

As an example, we will demonstrate how to dynamically alternate row colors in a TM1 report for a Customer dimension.

Pointing all your users to a single TM1 Admin host is convenient but not flexible if you manage several TM1 services.
Each TM1 service might need different settings and you do not necessarily want users to be able to see the development or test services for example.

mytm1.xla is an Excel addin that logs the users on a predefined server and settings as shown on that graph:

Attached below is the TM1 Open Source Toolkit developed by James Wakefield

v 1.0.1 adds:

• vba code for running TI processes/chores
• vba for running MDX reports

v 1.0 includes:

VBA function to check if a user is already logged in

Function IsUserLoggedIn(UserName As String, _
Servername As String) As Variant

IsUserLoggedIn = _
Application.Run("DBRW", Servername & "}ClientProperties" _
, UserName, "STATUS")

End Function

You can then use that in a sub as shown below:

Sub CheckWhetherUserIsLoggedIn()

If IsUserLoggedIn("MyUser", "TM1:") = "ACTIVE" Then
MsgBox "User is logged into TM1."
Else
MsgBox "User is doing something more interesting."
End If

End Sub

The following code will:

• load TM1 add-in
• hide the TM1 toolbars (most people do not need spreading, developer.. tools)
• log you on your TM1 server
• open the Server Explorer
• expand the applications and cubes

so you are just 1 click away from accessing your TM1 data :)

Replace the "\\path\to\tm1p.xla","server","user" and "password" strings to your own settings.

----THIS WORKBOOK------------------
Private Sub workbook_open()

Synchronising Excel data

. to reduce the probability of crashing your Excel, disable Automatic Calculation : Go to Tools->Options-> Calculation Tab then click on the Manual button

You can use:

• F9 to manually refresh all the open workbooks
• Shift F9 to refresh only the current worksheet
• F2 and Enter key (i.e. edit cell) to refresh only 1 cell

VBA snippets of code to integrate with Perspectives