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 » Mon Jul 28, 2014 10:58 pm

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: 3107
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TM1, CX
Version: TM1 10.2.2 PA 2.0x
Excel Version: 2010 2013 365
Location: Switzerland

Re: Dynamic Server Name reference in workbook

Post by lotsaram » Tue Jul 29, 2014 6:12 am

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 » Tue Jul 29, 2014 10:13 am

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 » Thu Jul 31, 2014 3:38 am

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 » Thu Jul 31, 2014 4:20 am

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 » Thu Jul 31, 2014 8:26 am

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
Posts: 143
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010
Location: Russia

Re: Dynamic Server Name reference in workbook

Post by Elessar » 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
Sorry for my English ;)

tm123
Posts: 113
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 » Tue Mar 20, 2018 9:32 pm

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: 1784
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0
Excel Version: 2016
Location: Brussels, Belgium
Contact:

Re: Dynamic Server Name reference in workbook

Post by Wim Gielis » Tue Mar 20, 2018 9:36 pm

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

Wim Gielis

Excel Most Valuable Professional, 2011-2014
http://www.wimgielis.com ==> 105 TM1 articles and a lot of custom code
Newest blog article: Looping over input files

Paul Segal
Community Contributor
Posts: 244
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010

Re: Dynamic Server Name reference in workbook

Post by Paul Segal » Wed Mar 21, 2018 9:06 am

Yup, what Wim says: must be PAX not Perspectives.
Capture.PNG
Capture.PNG (10.09 KiB) Viewed 882 times
Paul

User avatar
Elessar
Posts: 143
Joined: Mon Nov 21, 2011 12:33 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2010
Location: Russia

Re: Dynamic Server Name reference in workbook

Post by Elessar » 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).
Sorry for my English ;)

tm123
Posts: 113
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 » Wed Mar 21, 2018 12:59 pm

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