TM1 Forum

Hello,

I have discovered one reason why Excel workbooks have been auto calculating all worksheets even though the Excel's auto calculate setting is set to Manual.

I am working with Excel workbooks that are over 10 years old, and somehow this named ranged was inserted to the old workbook. The workbooks are not Active Forms.

A new calculation model was introduced in TM1 9.4 which forced an automatic recalculation of all sheets in a workbook, even when the Excel calculation mode was set to Manual. The new model forced a recalculation upon initial opening of any workbook, upon element selection via a SUBMN formula, or upon any server connect event.

TM1 9.4 MR1 includes a new workbook name variable, TM1REBUILDOPTION, which lets you manage calculation in workbooks containing TM1 slices and Active Forms. When you create a slice or Active Form in TM1 9.4 MR1, the TM1REBUILDOPTION name is automatically created in the corresponding Excel workbook and set to 1, indicating that all worksheets in the current workbook should be rebuilt and recalculated when a TM1 server connect event occurs.

If TM1REBUILDOPTION is present in a workbook and set to 0, or if TM1REBUILDOPTION is not present in a workbook, worksheets are not rebuilt/recalculated when a server connect event occurs.
Modifying the TM1REBUILDOPTION Setting

If you do not want workbooks created with TM1 9.4 MR1 to automatically rebuild and recalculate all slices and Active Forms when a server connect event occurs, you can set the TM1REBUILDOPTION value to 0.

Steps
1. In Excel, click Insert, Name, Define.
2. In the Define Name dialog box, select TM1REBUILDOPTION.
3. Set the value in the Refers to box to =0.
4. Click OK.

Using TM1REBUILDOPTION in Workbooks Created in Previous Versions of TM1
Workbooks created prior to TM1 9.4 MR1 do not include the TM1REBUILDOPTION workbook name variable. As noted above, when the TM1REBUILDOPTION is not present in a workbook, worksheets are not rebuilt/recalculated when a server connect event occurs.
You must add the TM1REBUILDOPTION name to any workbook created in a previous version of TM1 that includes an Active Form to force rebuild/recalculation upon a server connect event. If you do not add the TM1REBUILDOPTION name, you must manually rebuild (ALT + F9) the Active Form to activate the view and allow full functionality of the Active Form.

Steps
1. In Excel, click Insert, Name, Define.
2. In the Define Name dialog box, enter TM1REBUILDOPTION.
3. Set the value in the Refers to box to =1.
4. Click OK.


Regards,
Walter

This is not exactly new news.

If you want the default value for TM1RebuildOption to be automatically set to FALSE (0) so that you don't get this behaviour of a recalculation on opening a workbook and don't have to do this step then you need to change the parameter TM1RebuildDefault to a value of FALSE in the tm1p.ini file.
e.g.
TM1RebuildDefault = F
The tm1p.ini file is usually found in C:\Users\<user>\AppData\Roaming\Applix\TM1\

I'm pretty sure you would find topics on this in this forum as far back as 2010, maybe even earlier, just search for TM1RebuildOption or TM1RebuildDefault.