TM1 Forum

Hi gurus,

Just wondering if there is a formula that can be used in Excel that will dynamically return the name of the TM1 server to which a workbook has been published via the "Copy the file to the TM1 Server" option. We have three main TM1 servers, PROD, TEST and DEV and at the moment when we migrate workbooks from DEV -> TEST -> PROD the server name reference has to be updated manually each time.

For workbooks that I publish via the "Add the file as a reference" option I do have a solution. We store our workbooks in very specific locations on our network, in a folder structure that looks something like this ...\TM1\DEV\Reports\....., or ...\TM1\PROD\Reports\....., etc. Then, using the =CELL("filename") formula I can test for the location of the workbook on the network and determine if it live in PROD, TEST or DEV. The full formula, that works like a charm, is:

=IF(ISERROR(SEARCH("\PROD\",CELL("filename"),1)),IF(ISERROR(SEARCH("\TEST\",CELL("filename"),1)),IF(ISERROR(SEARCH("\DEV\",CELL("filename"),1)),IF(AND(ISERROR(SEARCH("\DEV\",CELL("filename"),1)),ISERROR(SEARCH("\TEST\",CELL("filename"),1)),ISERROR(SEARCH("\PROD\",CELL("filename"),1))),"NONE","NONE"),"DEV"),"TEST"),"PROD")

However, when uploading the workbook to the server, and particularly when publishing to web, this won't work. Web doesn't recognise the =CELL("filename") formula. And even if not in web, the =CELL formula returns something like C:\Users\username\AppData\Local\Temp\6\[TM14A0D.xls]Sheet1 which doesn't tell us whether it is in PROD, TEST or DEV.

Currently using TM1 9.5.2

Any ideas? Thanks.

The method I have been using for years is explained here http://www.tm1forum.com/viewtopic.php?p=22913

lotsaram wrote:The method I have been using for years is explained here http://www.tm1forum.com/viewtopic.php?p=22913

This works great the vast majority of the time. It slips up when people log onto more than one server e.g. as they may do during UAT when comparing numbers in the new template against numbers against a production template. Also it's a small pain if you need to go through all the templates if you change your server names or add new ones.

Thanks for the pointers, guys.

My conclusion is there is no risk-free option here, and the option for testing
=TM1User("DEV")
=TM1User("TEST")
=TM1User("PROD")
in that order is the preferred one for me, but not great when connecting to more than one environment at the same time.

I may use it, but with care, where practical.

The method I have been using for years is explained here viewtopic.php?p=22913

The above is Pretty archaic I reckon with its own issues but if you are going down the VBA path then http://dailydoseofexcel.com/archives/20 ... le-in-vba/

Cheerio

nick_leeson wrote:

The method I have been using for years is explained here viewtopic.php?p=22913

The above is Pretty archaic I reckon with its own issues but if you are going down the VBA path then http://dailydoseofexcel.com/archives/20 ... le-in-vba/

Cheerio

VBA methods won't work on TM1 Web so I guess it's horses for courses!

Hello,

In PA 2.0 there is the "TM1PRIMARYDB" function: Returns the primary TM1 server name that the user is authenticated through, even if the user is implicitly logged in to multiple TM1 servers.: https://www.ibm.com/support/knowledgece ... ics_2.html

Elessar wrote: ↑Wed Mar 07, 2018 8:54 am Hello,

In PA 2.0 there is the "TM1PRIMARYDB" function: Returns the primary TM1 server name that the user is authenticated through, even if the user is implicitly logged in to multiple TM1 servers.: https://www.ibm.com/support/knowledgece ... ics_2.html

Do you know the syntax of this new function?
I tried TM1PRIMARYDB() and also TM1PRIMARYDBNAME() but excel does not recognize them. These are TM1 Perspectives functions right? The IBM documentation has nothing.

It will be for PAX, not for Perspectives.

Yup, what Wim says: must be PAX not Perspectives.

This work only in PAX,
But you can use it in Perspectives (with error displayed) and upload workbook to websheet: this will work without PAX (but will be difficult to debug).

Elessar wrote: ↑Wed Mar 21, 2018 9:54 am This work only in PAX,
But you can use it in Perspectives (with error displayed) and upload workbook to websheet: this will work without PAX (but will be difficult to debug).

Again one of those stupid IBM "new features". This has been always a limitation in TM1, but since they took the effort to introduce this new function (which ironically works in TM1Web), why the hell they did not enable this in Perspectives,which has always been the development tool for WebSheets, and in my Opinion, PAX is still so far behind Perspectives