Tm1 perspective F9 calculation wierd behaviour

[nicola531]

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]

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]

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]

p.s. the autocalcuation in off

N.

[Catherine]

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]

@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]

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.

[nicola531]

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.

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]

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.

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