TM1 Forum

Hi,

I want write a cube cell value to a specific cellCoulmn A,Row 1) without distrubing data on other cells of a excel sheet.

I just thought to get ride of Server Name change on every Report whenever we migrate reports b/w environments,if i successful to implement above functionality.

Thanks a lot in advance.
Nata

nataraja

I *think* that you mean this:

you want to have a DBRW function in Excel to retrieve a value from a certain cube.

Indeed, you need to specify the server name in the formula.

But you can set the server name in a dedicated (hidden) cell somewhere in your Excel file, and reference that cell any number of times.
If you want, you could use a Name for it.

Migrating to a new server would then just mean you have to change the server name in the cell / Name.

Needless to say, if you take the same TM1 server name, you do not have to change it and your reports continue to work.

If you use the AsciiOutput function to export data to a certain file, you can export whatever (String) values you want. You decide yourself whether or not to incorporate the server name or not.

Thanks for your suggestion Wim Gielis.
We already have single cell "server Name" reference on all reports.whenever we do migration b/w environments,we do change that single cell.

I am trying to remove that manula work of changing Server name on cell.As we know all Reports are resides on
"}Extrenals" Folder of Data Directory, i could create a TI Process to Change that "Server Name" cell on each reports based on cube value(which holds server Name).it reduces manula effort of going to indidual sheet to do the same on every migration.

If you change the server name how will the formula know where to recieve the new server name from? A few options I could think of

1) Have a Global Variables server (which never changes) and use a dbrw formula to retrieve the server name from a cube in that server.

2) On every report use an excel named range "sServerName" and use VBA to scroll through the reports and then all sheets to find the range and change the value.

3) Place some VBA in the worksheet open event to determine the latest Server name every time some one opens the report.

Steve

When creating reports that need to move through the Dev > Test > Production server migration, one accepted easy method is to create a small hidden table listing the known possible servers, with a =TM1User(<servername>) formula next to them.
If the user opens the report and is logged into any of these servers, then the formula will display their user name next to the server they are logged into.
An IF statement in another cell can then refer to these TM1User formulas in priority order to return the server reference name.
Refer to this last cell as the server reference for all other TM1 formulas within the report.
Dont forget to recalculate this range of cells with an Workbook_Open event.


TM1ServerA =TM1USER(A1)
TM1ServerB =TM1USER(A2)
TM1ServerC =TM1USER(A3)
Server Name =IF(B1<>"",A1,IF(B2<>"",A2,IF(B3<>"",A3,"Not Logged in")))

Thanks Paul,
Nice idea but what if user logged in morethan 2 servers at a time?.

Firstly your post title is singularly misguided. You are lucky that people took the time to read and descipher your post and figure out what you were really on about. ASCIIOutput writes out discrete records line by line to a text file of "flat" file. It can do no more than create a text file.

In terms of the technique mentioned by Oratia623, it is intended for prod/dev/test environments. Should the user be logged on to multiple servers it is up to the report designer to determine which instance should take prescedence in this case (i.e. should it be dev or should it be prod?) You can find a more detailed explaination of this here http://www.cubewise.com/tech-archive-46.php on the Cubewise tech-tip blog or in this earlier post on this forum by blackhawk http://www.tm1forum.com/viewtopic.php?f ... 65&p=18232

In your current situation you would have hardcoded server references. You can get around this with VBA code to go through your }Externals folder (or any folder) and find and replace references from one server name to the other.

Hi All,
Sorry, if my tittle miss guided you guys becasue of incomplete description of requirement.
let me clear my requirement:

I have a report with Single cell "server"
cell("A1")=TM1Dev

TM1 functions(Subnm,Tm1rptrow) are reference to cell( "A1") for server name.
Currently whenever we move the reports to tst environment,we are manually changing cell("A1") to TM1tst on all reports individually.

what I thought, if have "server name" on dummy cube(Dim1:Server Name,Dim2:Name) then i will use ASCIIOUTPUT function on TI Process to change Server name cell("A1") on all reports(Worksheet saved on }External Folder) at one go.

Hope its clear now.

Thank you all for your suggestion & ideas.
-Nata

nataraja.pl@tcs.com wrote:Hi All,
Sorry, if my tittle miss guided you guys becasue of incomplete description of requirement.
let me clear my requirement:

I have a report with Single cell "server"
cell("A1")=TM1Dev

TM1 functions(Subnm,Tm1rptrow) are reference to cell( "A1") for server name.
Currently whenever we move the reports to tst environment,we are manually changing cell("A1") to TM1tst on all reports individually.

what I thought, if have "server name" on dummy cube(Dim1:Server Name,Dim2:Name) then i will use ASCIIOUTPUT function on TI Process to change Server name cell("A1") on all reports(Worksheet saved on }External Folder) at one go.

Hope its clear now.

Thank you all for your suggestion & ideas.
-Nata

I already told you that ASCIIOutput can't do this. AND I did tell you how you can do this with VBA to change the cell references (maybe more spoon feeding is necessary?) With a pure formula approach you can use the TM1User function as described and not need to do any find/replace. With your current approach there is zero value in storing the server name in a cube as you need the server name to return an DBRW value in the first place so you may as well just have a single hardcoded value.

You would just need some VBA code something like this: (note you will need a reference to microsoft scripting runtime library to loop through files in a given directory)