Dynamic Server Name reference in workbook
-
- 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
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.
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.
-
- MVP
- Posts: 3661
- 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
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.
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Dynamic Server Name reference in workbook
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.lotsaram wrote:The method I have been using for years is explained here http://www.tm1forum.com/viewtopic.php?p=22913
Robin Mackenzie
-
- 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
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.
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.
-
- 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
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/The method I have been using for years is explained here viewtopic.php?p=22913
Cheerio
-
- MVP
- Posts: 733
- Joined: Wed May 14, 2008 11:06 pm
Re: Dynamic Server Name reference in workbook
VBA methods won't work on TM1 Web so I guess it's horses for courses!nick_leeson wrote: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/The method I have been using for years is explained here viewtopic.php?p=22913
Cheerio
Robin Mackenzie
- Elessar
- Community Contributor
- Posts: 346
- 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
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
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
-
- 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
Do you know the syntax of this new function?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
I tried TM1PRIMARYDB() and also TM1PRIMARYDBNAME() but excel does not recognize them. These are TM1 Perspectives functions right? The IBM documentation has nothing.
-
- MVP
- Posts: 3123
- 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
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
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
-
- 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
Yup, what Wim says: must be PAX not Perspectives.
Paul
- Elessar
- Community Contributor
- Posts: 346
- 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
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).
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).
-
- 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
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