Active Form shows 0 with F9 when title element changes

[Timmy]

According to the documentation F9 should update data values in the current form.
In fact, when a title element was changed directly, i.e. without using the Subset Editor, only zero values are returned with F9/Recalculate/TM1RECALC.

It works fine with Alt+F9/Rebuild/TM1REFRESH, but this isn't exactly what I was expecting and it reverts any drill down/up to the last saved state.

The following suggestion didn't work:
http://www.tm1forum.com/viewtopic.php?f ... 912#p14883

edit the TM1RptView formula and completely remove the TM1RptTitle reference to the month dimension

Interestingly the numbers can correctly be refreshed by Excel as suggested here (equivalent to CalculateFullRebuild in VBA): http://www.tm1forum.com/viewtopic.php?p=15027#p15047

[Ctrl]+[Shift]+[Alt]+[F9] will do a full calculation, and also rebuild the workbook's calculation dependency tree.

But if I press F9 directly afterwards, it updates to zero values again.

There is no problem if Excel Calculation is set to Automatic or with VBA Application.Calculate!

It was suggested that this is common behaviour of TM1: http://www.tm1forum.com/viewtopic.php?f=3&t=5611#p23434:

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.

My questions are:

1. Is this a known bug?
2. I have to trigger calculation from VBA, no matter if it is an active form or standard report, so as it seems I have to use TM1REFRESH instead of TM1RECALC.
   Would it be better to use Application.Calculate? Or CalculateFullRebuild? Or Calculation=xlCalculationAutomatic and back to Calculation=xlCalculationManual?
3. Any other suggestion?

P.S.:
With every F9, TM1 extends the used range (last cell) of the workbook by the amount of rows in the data table minus 1.

[lotsaram]

Possibly Mr Moby could help you out with some documentation but I think that what you are seeing here is just "expected behavior". I recall seeing a technote at some point of changes to active form recalculation that allowed for retrieval of values with F9 in the "current configuration" with a change to title element (meaning if the rowset has been expanded or contracted from the starting point) provided that the title element is a SUBNM formula (note Shift+F9 doesn't work). I just tested this again and it is exactly as you describe - the active form retrieves values provided not only that the title element change is a SUBNM but also that the change was made via the subset editor. Quite possibly to IBM it never occurred that anyone might want to change a SUBNM value via anything other than the subset editor, but we know that in the real world things can be different.

It is worth reporting, but I think it is 50:50 whether IBM see it as a bug or "expected behavior" (maybe with the exception of the last used cell movement on F9, that's weird and it has to be a bug, albeit a harmless one and therefore one that is unlikely to be fixed.)

On a side not I would highly question any piece of Excel interface development that was so highly VBA dependent. It consigns the UI to being only Excel and not Web and is the VBA automation really necessary or coudl it be substituted with features that come in the box plus some user training in how to use them?

[jstrygner]

It works fine with Alt+F9/Rebuild/TM1REFRESH, but this isn't exactly what I was expecting and it reverts any drill down/up to the last saved state.

Just to add, as this was not mentioned here.
I am not sure if you are aware of this one, and even if not, if this would be a worthy alternative, but in TM1Web you can use F9/Shift+F9 calculation and rows do not get rebuilt to original setting, they only get (properly) recalculated.