VBA

Dynamic tm1p.ini and homepages in Excel

Submitted by admin on Tue, 2019-11-19 14:59

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:

VBA misc

Submitted by admin on Tue, 2019-11-19 08:50

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

TM1 and Excel in one click

Submitted by admin on Tue, 2019-11-19 08:42

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

Submitted by admin on Tue, 2019-11-19 08:38

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

Submitted by admin on Tue, 2019-11-19 08:35

VBA snippets of code to integrate with Perspectives

Bulk reporting

Submitted by admin on Mon, 2019-11-18 15:01

The TM1->Print Report function from Perspectives is useful to generate static reports in bulk for a given set of elements.
The following code is mimicking and extending that functionality to achieve bulk reporting for a TM1 report in a more flexible fashion.
For example you could get a report based on the branches of a company to be saved in each respective branch documents folder instead of getting them all dumped in a single folder or you could also get each branch report emailed to its own branch manager.

Here is the Excel VBA code:

A closer look at dynamic slices

Submitted by admin on Mon, 2019-11-18 14:48

Dynamic slices can be quite useful when the elements you display in your reports evolve over time, they automatically update with the new elements.
The following article will try to dig into the parameters that define these slices and show some of the possibilities to interact with these. The idea was originally submitted by Philip Bichard.

The dynamic slice parameters stored in the names list of the worksheet
to display these in Excel: insert -> name -> paste -> paste list