Excel calculation puzzle

[iansdigby]

Greetings to the honoured TM1 community. Salam Alaykum, '您好' [nín hǎo] and Guten Tag.
I have a puzzling problem wth an Excel spreadsheet full of DBR's and DBS's. It goes like this:

1. I open the spreadsheet with Excel calculation on Automatic. Everything calculates correctly.
2. I press F9 and all the DBR's show *KEY_ERR; all the DBS's go to zero.
3. I go to each individual sheet and recalculate with Shift-F9. This restores the correct calculations in the sheet.

Environmental factors:
1. I am logged in to TM1 Perspectives, v9.5.2 FP2 (server is 64-bit same version).
2. There is no 'TM1RebuildOption' named range in the sheet, and the tm1p.ini has no 'TM1RebuildDefault' parameter.
3. Excel calculation is set to Automatic.

Can anyone kindly suggest why this might be happening?

Many thanks, Ian

[mvaspal]

Hi Ian

We also experienced something similar that you described. Users had to choose a month on the first sheet and all other sheets had a reference to this value. Pressing an F9 resulted in KEY_ERR in some cells (but not all in our case) but while going through the sheets one by one and pressing Shift+F9, it simply worked (only DBRWs). So as much as I remember our final solution was to create a simple macro that recalculates all sheets, one after the other.

I think this might be be because when you press F9 you cannot control the order of calculations within Excel or the order in which the client sends data packages to the server and from the server to the client back (I remember something similar post here on the forum but can't recall exactly where I found it).

Matyas

[iansdigby]

Thank you veru much for that Matyas. Using a macro seems a good workaround.

Ian

[Alan Kirk]

Greetings to the honoured TM1 community. Salam Alaykum, '您好' [nín hǎo] and Guten Tag.
I have a puzzling problem wth an Excel spreadsheet full of DBR's and DBS's. It goes like this:

1. I open the spreadsheet with Excel calculation on Automatic. Everything calculates correctly.
2. I press F9 and all the DBR's show *KEY_ERR; all the DBS's go to zero.
3. I go to each individual sheet and recalculate with Shift-F9. This restores the correct calculations in the sheet.

Are they actually DBRs and DBS's, or are they actually DBRW / DBSWs? Because if it's the latter, a common cause of this kind of problem is when one DBRW (not DBR) formula uses as one of its arguments a cell which uses another DBRW (not DBR) to return an element name.

DBRWs calculate in batches. Consequently you can get key errors on seemingly correct formulas when you have one DBRW pointing to another one because at the time that the first one is calculating, so is the second one. The second one doesn't know what it's value will be at the time of calculation, therefore it can't pass an element name to the first one. Result? The first one throws a key error.

The solution is to ensure that the second one (the one that returns an element name to the first one) is a DBR, not a DBRW. DBRs calculate individually rather than as part of the DBRW batch.

(Of course, if you really are using DBRs, that won't be the cause.)

[iansdigby]

Alan,

Truly you are a master TM1-er. If I wasn't nearly 60 I would fly to Oz and beg to be your apprentice.
Many thanks for this reply which, even if it is not the answer to my problem (it probably is: I will investigate soon and advise here), is most educational and helpful both to me and I am sure to others here.

Regards, Ian

[iansdigby]

Alan Kirk's suggestion about circularity within DBRW formulae was indeed the problem and it has now been resolved, wth many thanks.

Regards, Ian