Padding zeroes

This a short simple syntax to easily pad zeroes to an index value:

Period = 'Period ' | NumberToStringEx(index, '00','','');

(from olap forums)

alternatively, one could also write (prior to TM1 8.2):

Period = 'Period ' | If(index > 10,'0','') | NumberToString(index);

Creating Dynamic Subsets in Applix TM1 with MDX - A Primer

Lead author: Philip Bichard.
Additional Material: Martin Findon.

About This Document

This MDX Primer is intended to serve as a simple introduction to creating dynamic dimension subsets using MDX in TM1. It focuses on giving working examples rather than trying to explain the complete theory of MDX and makes sure to cover the features most useful to TM1 users.

Zero out portions

In order to zero out data points you can do it either:
- from Cube Viewer
select all the cells with the pointer
right click: Data Spread->Clear...
select the top left corner cell of the portion to zero out
right click: Data Spread->Repeat
set value box to 0 and tick boxes Extend "right" and "down"

- from a TI process
in the Prolog tab:

setup the 'View' to zero out then run that process

VBA misc

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."
MsgBox "User is doing something more interesting."
End If

TM1 and Excel in one click

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()

Removing TM1 formulas from Excel sheets

The following code adds a button in your Excel toolbars to replace all TM1 formulas in the current book with their values in order to get your worksheets TM1-free.

It has the following features:

  • toolbar/button for ease of use
  • applies to all sheets in the workbook
  • changes all TM1 DB functions (DBRW, DBR, DBA...) and SUBNM/VIEW into values

This code might get included in a future version of the TM1 Open Source Toolkit


VBA snippets of code to integrate with Perspectives