Action Button - Using a cell reference for the Tm1 Server

Post Reply
LanceTylor
Posts: 66
Joined: Mon Feb 27, 2012 12:37 am
OLAP Product: TM1
Version: 10.2.2 Fix Pack 4
Excel Version: 2010

Action Button - Using a cell reference for the Tm1 Server

Post by LanceTylor »

Hello All,

I am attempting to reference a Tm1 server using a cell reference in action button. Within the Action button I used the "Use Reference" under the Tm1 server section and then referenced a cell within a worksheet. This works fine initially but when I close (and save) the workbook and then reopen it the "use reference" is not ticked and the cell reference is gone. I tried both a specific cell reference (C4) and named-range cell (Tm1_Server). It wasn't until I finished compiling this workbook (with nearly 30 processes) that I encountered this error.

Has anyone else had this issue?

If I don't use the "Use Reference" and just select a TM1 server there are no issues. I would like to avoid this option as it will add unnecessary time to there annual rollover process.

I am on Tm1 10.1.1 with Excel 2010.

Thanks
Lance
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: Action Button - Using a cell reference for the Tm1 Serve

Post by lotsaram »

Usual solution for action button parameter problems in versions pre 10.2 is save as .xls file.
LanceTylor wrote:If I don't use the "Use Reference" and just select a TM1 server there are no issues. I would like to avoid this option as it will add unnecessary time to there annual rollover process.
So you are saying part of the year rollover process involves changing the server name? WTF?
LanceTylor
Posts: 66
Joined: Mon Feb 27, 2012 12:37 am
OLAP Product: TM1
Version: 10.2.2 Fix Pack 4
Excel Version: 2010

Re: Action Button - Using a cell reference for the Tm1 Serve

Post by LanceTylor »

Thanks Lotsaram, that solved the issue. I don't change the server name I create a whole new TM1 server and new Tm1 Applications which are then used for the current year budget.
David Usherwood
Site Admin
Posts: 1453
Joined: Wed May 28, 2008 9:09 am

Re: Action Button - Using a cell reference for the Tm1 Serve

Post by David Usherwood »

@lancetylor - glad you got a fix - I've had to go that way on earlier versions.
I'm still scratching my head to think why you want to create a new TM1 instance for each budget cycle - it doesn't make sense to me. Don't your users ever want to look at variances between budgets? Not a good design I would say.
LanceTylor
Posts: 66
Joined: Mon Feb 27, 2012 12:37 am
OLAP Product: TM1
Version: 10.2.2 Fix Pack 4
Excel Version: 2010

Re: Action Button - Using a cell reference for the Tm1 Serve

Post by LanceTylor »

It’s probably best if I provide a bit of background

The project is a migration from EP to TM1 and a lot of the design from EP was incorporated into the TM1 model. I came onto the project to finish a bit of development and carry it into UAT/ Production and deliver training (given the time I would have a changed a couple things). The complicated piece is the HR Planning Budget Model which captures FTEs, Salaries and Benefits (by Dept, Position/Job, Union etc…) and contains 22 cubes (5 inputs, 5 assumptions, 7 calc, 4 output). There is a lot of rules which are all intertwined. It’s worth noting they are only using TM1 Applications (easier transition to end users).

Comparatives and variances for the prior year (Budget/Forecast/Actual) do exist at a summarized level (FTE summary, Salary & Benefit Summary) but all the inputs and calculations are only generated for the budget.

The 2 main reasons for using another TM1 instance
1. Org structure (Cost Centres – approx 1300) changes year on year and the client would like to retain that history. Moreover, the lines of responsibility (VPs, Execs/ Regional and Operational Managers) constantly change within different levels of the hierarchy. They previously had multiple EP Contributor applications going back 4 years, making this an important requirement. They would like to retain all the budget details (every cube) for the whole model as oppose to a few summary cubes and be able to view this information within the TM1 applications
2. TM1 applications (10.1.1 FP) cannot handle multiple applications using the same approval hierarchy. This would require using/building alternative dimensions/cubes and publishing another application. This would entail a lot more maintenance.
AmbPin
Regular Participant
Posts: 173
Joined: Sat Mar 20, 2010 3:03 pm
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2007-10

Re: Action Button - Using a cell reference for the Tm1 Serve

Post by AmbPin »

Does anyone know a solution for this?
I have seen refernces for the TM1 server instance disappear from action buttons on all versions from 9.5 onwards, am currently using 10.1 and it still happens.

If you have a Dev/Test/prod environmet it is convenient to have a server selection sheet to determine which of the servers your websheet is actively connected to which can be configured as a named range. However as the OP pointed out, some (but not always all) of the action buttons often lose reference to this named range in the TM1 Refernce box.
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Action Button - Using a cell reference for the Tm1 Server

Post by PavoGa »

Bumping this to see if anyone has additional light to shed.

We have the problem. However, the action buttons ALWAYS work in our dev environment. In our QA and PROD environments, the migrated web sheets are hit or miss on whether the action button will use the server reference or not. Just to get by, the server names are being hardwired (grrrrrrrrrrrr!) for the action buttons in the web sheets where this occurs.

Any light shed on the matter would be most helpful.

Thanks.
Ty
Cleveland, TN
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: Action Button - Using a cell reference for the Tm1 Server

Post by lotsaram »

PavoGa wrote: Wed Jan 31, 2018 9:46 pm Bumping this to see if anyone has additional light to shed.

We have the problem. However, the action buttons ALWAYS work in our dev environment. In our QA and PROD environments, the migrated web sheets are hit or miss on whether the action button will use the server reference or not. Just to get by, the server names are being hardwired (grrrrrrrrrrrr!) for the action buttons in the web sheets where this occurs.

Any light shed on the matter would be most helpful.

Thanks.
I've never had an issue using this approach between environments with different D T P instance names within a TM1 version. However TM1 upgrades have been problematic as is seems the server reference perameter of the action button gets broken interpreted as literal text string often in the upgrade which then results in needing to re-do all the ABs (and may as well have had fixed name).

Is your Dev on a later version than Test and Prod?
Are all instances on the same admin host? (i.e. might it be hit and miss because the AB is running a process on a different instance?)
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Action Button - Using a cell reference for the Tm1 Server

Post by PavoGa »

Yes, our DEV environment is 10.2.2 FP7. We are needing to push it up, but things like that are slow to get pushed. What is so frustrating is some of the web sheets with seemingly the exact same method (action button references named range "pServer") work just fine. And if run in perspectives they run just fine. And the web sheets fail for admins as well.

D T P environments are on separate admin hosts.

Not sure we have tried it, but are you saying the AB object itself is breaking? So removing and adding the action button back may fix the problem? I'll give it shot.
Ty
Cleveland, TN
User avatar
PavoGa
MVP
Posts: 616
Joined: Thu Apr 18, 2013 6:59 pm
OLAP Product: TM1
Version: 10.2.2 FP7, PA2.0.9.1
Excel Version: 2013 PAW
Location: Charleston, Tennessee

Re: Action Button - Using a cell reference for the Tm1 Server

Post by PavoGa »

Tried removing and redoing the button in the QA environment with 10.2.2.1. No luck. Runs from Perspectives, will not run as a web sheet. :x
Ty
Cleveland, TN
Post Reply