TM1 Forum

Hi All

I have 50 worksheets as an application in one TM1(dev) server. We want to migrate to different(uat) server but admin wanted to change the instance name. Due to this instance change we have to change all the worksheets pointing to that instance name(Subnm formulae, View formulae). Is there any way we can avoid this tedious work.

Thanks

When designing a TM1 Excel report it is always a good good idea or best practice to have a named range or named constant that defines the server name and link all other formulas to that name. This goes doubly when working on a model with different server names between environments. That way for each workbook there is only a single cell to change when migrating.

Still, even if your reports have not been designed with this principle it is not very much work to write a VBA routine to open all files in a specified directory and do a find/replace for the target server names and re-save each workbook.

lotsaram wrote:When designing a TM1 Excel report it is always a good good idea or best practice to have a named range or named constant that defines the server name and link all other formulas to that name. This goes doubly when working on a model with different server names between environments. That way for each workbook there is only a single cell to change when migrating.

Still, even if your reports have not been designed with this principle it is not very much work to write a VBA routine to open all files in a specified directory and do a find/replace for the target server names and re-save each workbook.

Hello Lotsaram,

question on the SUBNM's. I was told earlier that for SUBNM's, we better hardcode the server name, instead of using cell reference. Hence, we need to find/replace instead of only changing 1 cell per file. What is your experience in this?

Thanks,

Wim

Wim Gielis wrote:
question on the SUBNM's. I was told earlier that for SUBNM's, we better hardcode the server name, instead of using cell reference. Hence, we need to find/replace instead of only changing 1 cell per file. What is your experience in this?

Wim

Wim,
I think this is very version specific and IIRC has come and gone with different versions. For example I seem to recall formulae in SUBNM working in one sub release of 9.4 (MR1 FP1?) but not another. So I think try it on your version and see! Obviously, if this is the situation it is not satisfactory for a developer.

From 9.4.1 FP2 onwards SUBNM with reference for server name work on double click to launch subset editor.

(but the previously useful functionality of being able to launch subset editor from a SUBNM with incomplete arguments has been broken.)

Thank you. Gurus

Will do a VBA for that.

Hi

The attached program lets you pick a directory holding your Excel Sheets and it then replaces all instances of one bit of text with another, eg local: with tm1serv:. It does this recursively for all sub-directories.

There are also commercial programs like WinGrep that will do this. If you have SQL Server that also has a replacer.

I tend to agree with the comments about subnm's not working if the server name is given as a cell reference. It is a long term source of annoyance and I wish that IBM would sort it out, ideally by introducing a version of the subnm formulae that takes the server name as an additional parameter.

Regards


Paul Simon