Dynamic Server Name reference in workbook

Post Reply
awium
Posts: 7
Joined: Wed Jun 09, 2010 3:40 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: Office 2010

Dynamic Server Name reference in workbook

Post by awium »

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.
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Dynamic Server Name reference in workbook

Post by lotsaram »

The method I have been using for years is explained here http://www.tm1forum.com/viewtopic.php?p=22913
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Dynamic Server Name reference in workbook

Post by rmackenzie »

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.
Robin Mackenzie
awium
Posts: 7
Joined: Wed Jun 09, 2010 3:40 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: Office 2010

Re: Dynamic Server Name reference in workbook

Post by awium »

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.
nick_leeson
Posts: 98
Joined: Sat Feb 11, 2012 11:13 am
OLAP Product: TM1 9x, BPC, Hyperion, HANA
Version: TM1 10
Excel Version: Excel 2003 - 2010

Re: Dynamic Server Name reference in workbook

Post by nick_leeson »

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
rmackenzie
MVP
Posts: 733
Joined: Wed May 14, 2008 11:06 pm

Re: Dynamic Server Name reference in workbook

Post by rmackenzie »

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!
Robin Mackenzie
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Dynamic Server Name reference in workbook

Post by Elessar »

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
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Dynamic Server Name reference in workbook

Post by tm123 »

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.
Wim Gielis
MVP
Posts: 3105
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Dynamic Server Name reference in workbook

Post by Wim Gielis »

It will be for PAX, not for Perspectives.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
Paul Segal
Community Contributor
Posts: 306
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: Dynamic Server Name reference in workbook

Post by Paul Segal »

Yup, what Wim says: must be PAX not Perspectives.
Capture.PNG
Capture.PNG (10.09 KiB) Viewed 7629 times
Paul
User avatar
Elessar
Community Contributor
Posts: 331
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: PA 2
Version: 2.0.9
Excel Version: 2016
Contact:

Re: Dynamic Server Name reference in workbook

Post by Elessar »

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).
Best regards, Alexander Dvoynev

TM1 and Data Science blog: 6th article - PAfE + VBA: Commit each cell without pressing “Commit” button.
tm123
Posts: 132
Joined: Thu Oct 23, 2014 10:15 pm
OLAP Product: tm1, cognos bi
Version: 10.2
Excel Version: 2010

Re: Dynamic Server Name reference in workbook

Post by tm123 »

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
Post Reply