Tm1 perspective F9 calculation wierd behaviour

Post Reply
nicola531
Posts: 43
Joined: Thu Oct 22, 2009 7:58 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007

Tm1 perspective F9 calculation wierd behaviour

Post by nicola531 » Mon Jul 25, 2011 2:22 pm

hello everybody,

I've got an issue in TM1 perpespective. (from version 9.5.0 to 9.5.2).
It is related to TM1 Spreadsheets.
First of all, I have to say that I always work with Excel Autocalculation switched-off.
Now, the problem is that when I try to open a spreadsheet previously created (same version or previous one),
TM1 downloads the spreadsheet with some initial figures.
Then, if I press F9 then the spreadsheet calculates all 0 (or somethime some wrong numbers): I have not understood if it is the initial photography of the report.
But if I refresh with Ctrl - Alt F9 then all the correct figures are coming out correctly.
Has anyone of you experienced something like it?

P.s. If I use DBR instead of DBRW everything is working properly.

Thanks in advance for you help.

N.

Christopher Kernahan
Community Contributor
Posts: 147
Joined: Mon Nov 29, 2010 6:30 pm
OLAP Product: Cognos TM1
Version: 10.1
Excel Version: Office 2010

Re: Tm1 perspective F9 calculation wierd behaviour

Post by Christopher Kernahan » Mon Jul 25, 2011 2:51 pm

Do you have any Circular References in the file? And, it sounds like you may be using an Active Report - it is set to automatically refresh?

nicola531
Posts: 43
Joined: Thu Oct 22, 2009 7:58 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007

Re: Tm1 perspective F9 calculation wierd behaviour

Post by nicola531 » Mon Jul 25, 2011 3:07 pm

Yes sorry, you're right I0m using Active Form.
I don't think we have circular reference. But I'll check, Any other possible reasons?

if this can help it happens for sure when, starting from an active form genereated by tm1 I insert instructions to read from another cube.

Thanks in advance,

N.

nicola531
Posts: 43
Joined: Thu Oct 22, 2009 7:58 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007

Re: Tm1 perspective F9 calculation wierd behaviour

Post by nicola531 » Mon Jul 25, 2011 3:08 pm

p.s. the autocalcuation in off

N.

Catherine
Posts: 108
Joined: Wed May 20, 2009 7:30 am
OLAP Product: TM1
Version: 10.2.2 - PA
Excel Version: 2010
Location: Rennes, France

Re: Tm1 perspective F9 calculation wierd behaviour

Post by Catherine » Mon Jul 25, 2011 3:28 pm

Hello,

I met this problem with 9.4 version.
To know if this is the same problem, try this workaround:
Instead of using a reference to the TM1Rptview cell in your DBRW formula, just use a cell containing the cube nam (Instance:CubeName), and make your DBRW use this new cell.
Does it work?

If yes, it sounds like you have the same problem, that is TM1 does not refresh cells in the right order resulting in visualizing zeros or even false numbers.

Can you test?

lotsaram
MVP
Posts: 3325
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Tm1 perspective F9 calculation wierd behaviour

Post by lotsaram » Mon Jul 25, 2011 4:42 pm

@Nicola - are you using F9 or Alt + F9 to recalculate the active form? If any title elements have changed you have to use Alt + F9 to rebuild. Active forms use a special cache and often require a rebuild rather than just a recalc particularly if any title elements or column headers have been changed, otherwise the required value won't be in the active form's view cache and a zero will be returned.

@Catherine - a better solution to replacing the cube reference with a simple hardcoded string of server:cube is to use a VIEW formula instead. This will also fix the problem and also usually give much better performance.

tomok
MVP
Posts: 2642
Joined: Tue Feb 16, 2010 2:39 pm
OLAP Product: TM1, Palo
Version: Beginning of time thru 10.2
Excel Version: 2003-2007-2010-2013
Location: Atlanta, GA
Contact:

Re: Tm1 perspective F9 calculation wierd behaviour

Post by tomok » Mon Jul 25, 2011 5:48 pm

Also make sure you don't have any interdependent DBRW formulas. What I mean by that is you have a cell with a DBRW formula in it, and that cell is used as a dimension reference in another DBRW formula. This will cause problems. If you absolutely HAVE to have this type of setup, where you are performing a cube lookup to get a dimension reference, use DBR for the lookup.
Tom O'Kelley - Manager Finance Systems
American Tower
http://www.onlinecourtreservations.com/

nicola531
Posts: 43
Joined: Thu Oct 22, 2009 7:58 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007

Re: Tm1 perspective F9 calculation wierd behaviour

Post by nicola531 » Thu Aug 04, 2011 5:59 pm

Thanks a lot to everybody,

of all the cases that we spoke about, mine was the one addressed by Tomok. You can see it in my quote.

tomok wrote:Also make sure you don't have any interdependent DBRW formulas. What I mean by that is you have a cell with a DBRW formula in it, and that cell is used as a dimension reference in another DBRW formula. This will cause problems. If you absolutely HAVE to have this type of setup, where you are performing a cube lookup to get a dimension reference, use DBR for the lookup.
It has been sufficent to put DBR instead of DBRW in the cube lookup formula and everything worked properly.
Nothing else.

Thanks again for everybody's help.

N.

stex2727
Posts: 65
Joined: Tue Sep 15, 2009 11:29 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Tm1 perspective F9 calculation wierd behaviour

Post by stex2727 » Thu Aug 04, 2011 9:11 pm

nicola531 wrote:Thanks a lot to everybody,

of all the cases that we spoke about, mine was the one addressed by Tomok. You can see it in my quote.

tomok wrote:Also make sure you don't have any interdependent DBRW formulas. What I mean by that is you have a cell with a DBRW formula in it, and that cell is used as a dimension reference in another DBRW formula. This will cause problems. If you absolutely HAVE to have this type of setup, where you are performing a cube lookup to get a dimension reference, use DBR for the lookup.
It has been sufficent to put DBR instead of DBRW in the cube lookup formula and everything worked properly.
Nothing else.

Thanks again for everybody's help.

N.
I encountered a similar problem and it was also making the report unstable leading to unexpected crashes of excel. The same fix worked for me.

On another note with active forms, I encountered a similar problem when adding a new column to an existing report in order to get a prior years data. The problem is that the year dimension was a title element and if I referenced a different cell the figures would not update correctly. I found by deleting the "TM1RPTTITLE" reference to that dimension in the view formula "TM1RPTVIEW" corrected the behaviour.

Steve

Post Reply