A simple way of managing TM1 Excel worksheets across multiple server environments

Many larger companies using TM1 operate development, user testing and production environments with separately named TM1 server instances, for example; TM1_Dev,  TM1_Test and TM1_Prod.  Migrating TM1 server objects across servers is relatively straightforward as no object changes are required. However with reports and data entry templates developed in Excel it is usually a different matter as the server name reference will need to be replaced.  Often this is a tedious and manual job that involves a developer of admin manually opening files and performing a find and replace (or developing a VBA macro or windows script to automate this task.)  This violates the separation of environments principle as a report that has passed UAT must be changed  in order to function in production.

Well it doesn't have to be that way.  What follows is a simple method to share and promote workbooks between named servers with no requirement to find/replace server names at all.

All that is required is prior knowledge of the number of environments and the TM1 server name of each environment and the TM1User function.

Steps:

1.       For each environment have a cell or named range in the worksheet to hold the server name

2.       For each environment have a matching TM1User function linking to the appropriate cell containing the server name. If the user is connected to a server he function will return the TM1 client name, otherwise is will return an empty string

3.       Have a named range "pServer" which contains an IF test to see if the TM1User functions have returned values

4.       Replace any hardcoded server name references in TM1 formulas with pServer&":

The following two screen shots show the cells that are required and the formula logic:

Additional cells required

Note the highlighted cell is a named range called "pServer"

Formulas in each cell

Note in other TM1 Excel formulas (SUBNM, VIEW, TM1RptView, TM1RptRow, etc.) hardcoded server name strings are replaced with a reference to the pServer named range.

The workbook will now be capable of being passed between environments with no further need for any modifications. This technique is suitable for any customer using TM1 across multiple servers where each instance of TM1 is running under a separate TM1 server name.