Excel Cell Disapearing

[dubs]

Hi All,

we are getting a bit of an issue with our active forms.

We've set up all our reports as excel worksheets and they work great yet there is a problem that they sometime lose cell values when your expanding the consolidations or navigating the sheet. to get them back you have to enter the cell, hit enter and then hit F9.

this is with auto recalc on or off, any ideas?

[Wim Gielis]

Hi

I experienced the same issue lately, and have not yet found the cause or the solution.

Here it occurs on my PC with Excel 2003, TM1 9.4 MR1 FP1, not tested with other 9.4 versions.

I have an active form, in the columns 3 measures. Normally, 1 of them shows up, the others disappear.

Doing an F9 does not help, nor all other variants with F9. Only F2 and Enter in for instance an empty cell.

FWIW, I dragged the formatting in the hidden rows to the correct columns, and updated the Named range.

Wim

[summerbrewgal]

If the data is not showing up when you do a rebuild of the form, but may sometimes show up when you click in that cell explicitly, that typically indicates that data was not found to support that cell in the underlying view supporting the active form. Have you added some add'l columns to the form or changed a column to point to a different title dim than the other columns that are working all the time?

The active form defines a view to support itself based on the TM1RPTView statement (found in a hidden row underneath the format rows) which includes reference to the cells containing the title dim elements, the TM1RPTRow statement(s), and then assumes any remaining dimensions are in the columns referenced by the DBRWs. If the first parameter of your DBRW statements are pointing to the TM1RPTView statement then it must follow the same set up as the primary form (i.e. it should be pointing to the same title elements referenced in the TM1RPTView statements that the others are.) It is possible to add in additional columns, either in the same cube but pointing at different dimension elements, or in separate cubes altogether, but their first parameter should not then be pointing at the same TM1RPTView statement. You can either include the cube reference directly in the other DBRWs or add in a separate 'View' statement (an individual active form does not support more than one TM1RPTView statements).

If that's not clear feel free to attach your sheet and I can take a look.

Thanks,
Rebecca

[Wim Gielis]

Many thanks Rebecca! I will try that one out and post my findings.

Regards,

Wim

[dubs]

i get different results using F9 and ALT+F9....

[Martin Ryan]

Dubs,

ALT-F9 doesn't do anything as far as I'm aware. I think the one you're after is Shift+F9. This will calculate only the active sheet. F9 calculates all open workbooks.

I think this is the problem you're having over the in the *KEY_ERR tracer thread too.

Cheers,
Martin

[Alan Kirk]

Dubs,

ALT-F9 doesn't do anything as far as I'm aware. I think the one you're after is Shift+F9. This will calculate only the active sheet. F9 calculates all open workbooks.

You're right, it doesn't... though [Ctrl]+[Alt]+[Shift]+[F9] is sometimes useful when calculation problems arise, since it rebuilds the dependency tree and then recalculates everything regardless of whether the cell is flagged as needing recalculation. I've occasionally had workbooks which needed that extra kick and worked normally again after they received it, though not ones with Active Forms obviously. (Being on 8.2.12 and all...)

[summerbrewgal]

Alt-F9 = rebuild of the active form (there is also a rebuild button on the toolbar). You need to do this if you've changed any of the parameters that would affect the view generation (anything in the TM1RPTView, TM1RPTRow or added new columns with a formula linked to the TM1RPTview statement). The active form will start back in the opening state.

F9 = standard recalc (recalc button on toolbar) ... will fetch new data in the form, show/hide rows that should/should not be there based on zero suppression if you've changed a title dimension element, but doesn't reset the form (i.e. if you've drilled for instance, it will remain in that state)

Rebecca

[lotsaram]

It's not clear from Rebecca's post but if you change a title element, chances are you will have to rebuild the active form as F9 or Shift+F9 will return blank values.
Alt+F9 should rebuild the form correctly and display values. This is new with 9.4 as are active forms.

[dubs]

hi

what im running is:

Application.CalculateFull

It's the weirdest thing, whats ive done to fix it is put that statement in the worksheet open and when one of my title dimensions is updated, what happens is that when the spreadsheet opens it only populates one of the columns, if i then go and change one of the title dimensions all the columns are recalculated.

if i go into another active form and hit alt+f9 it calculates certain columns but not all, the only way to get it populated is to enter and then exit the cell.... but if do that to a cell that actually was populated when the sheet opened it gives a key error

could it have anything to do with the dimension order in the cube?

[dubs]

ive cracked the key error issue but am still getting the calc issue- i think alt+shift+ctrl+f9 is the way forward but cant test this until tomorrow...

[Wim Gielis]

The quote below helped me.

Thanks Rebecca. Much appreciated.

Wim

It is possible to add in additional columns, either in the same cube but pointing at different dimension elements, or in separate cubes altogether, but their first parameter should not then be pointing at the same TM1RPTView statement. You can either include the cube reference directly in the other DBRWs or add in a separate 'View' statement (an individual active form does not support more than one TM1RPTView statements).

[Jessy]

Really helped a lot. Many thanks.
A further question, is there any code to click on the consolidation cell automatically?