Ever asked yourself how you get the current TM1 server name from within an Excel report or form? Or, have you ever moved an Excel file from one TM1 server to another and then found that it still refers to the old server? You need to remember to fix the Excel each time you move it so it refers to the right model. No more! You can now use the TM1PRIMARYDBNAME function and your Excel reports will dynamically refer to the currently logged in TM1 server (model).
We strongly recommend to clients that they separate their TM1 development from production. This allows mistakes to be made, TI’s to be run and multiple rule save to be done without impacting users. But what if you only have a single TM1 server? Well, the obvious answer is to create a non production model and do you work there. The problem with that is that the TM1 models are then different names. When you create an Excel report or form, the very first thing that PAX does is hard code the server name into the Excel file. This makes it difficult to then migrate those forms or reports from one TM1 model to another because the Excel file is referring to a hard coded server name.
How to Avoid Hard Coded TM1 Server Names in Excel
Search and Replace
The clumsy method to manage this is to do a find and replace on the model name to reconfigure it to point to the environment you need.
So, if we have, for example, a SUBNM formula that is in the syntax SUBNM(DimName, Subset, IndexOrName, [Alias]), the dimension part of that is a qualified name in the form ModelName:DimName. So it would look like this: SUBNM(“ProdModel:DimName”,”SubsetName”,”ElementID”,”AliasName”). Then you could do a find and replace on all instances of “ProdModel” with “DevModel” and you’d be fine.
The problem is that you need to do it every time you move the excel file from one environment to another.
Use a Variable in Excel
The method that was used prior to Planning Analytics was to put the model name in a cell and then refer in a formula to that cell in place of the model name.
For example, using the SUBMN from above, we would set up a named cell called “TM1_Model”. Then insert “ProdModel” into that cell (without the rabbit ears). Then we would change the formula from:
SUBNM(“ProdModel:DimName”,”SubsetName”,”ElementID”,”AliasName”) to: SUBNM(TM1_Model&”:DimName”,”SubsetName”,”ElementID”,”AliasName”).
Then we would do an Excel find and replace on all “ProdModel:” with TM1_Model&”: (including the inverted commas).
Our spreadsheet would then be setup ready to move from one server to the other. All we need remember to do is change that one cell from “ProdModel” to, say, “DevModel” and bingo. All formulae would update.
The Dynamic Method – using TM1PRIMARYDBNAME
Since the introduction of Planning Analytics, there has been a new method hidden inside Planning Analytics for Excel (not Perspective, mind you). This involves the use of a new function called TM1PRIMARYDBNAME.
If we use the same method as above by setting up a named cell in Excel and then put:
=TM1PRIMARYDBNAME()
Into that cell named “TM1_Model”, we can then refer to it the same way as above, replacing all instances of “ProdModel:” with TM1_Model&”: (including the inverted commas).
TM1PRIMARYDBNAME dynamically returns the current TM1 server the user is logged into. Note again, it does not work in Perspectives, only in PAX, PAW or TM1Web.
Therefore, when we migrate from Dev to Prod, or vie versa, we don’t need to do anything. The excel file will dynamically update to refer to the correct TM1 server name.
