Excel Cell Disapearing

Post Reply
dubs
Posts: 131
Joined: Fri May 22, 2009 10:43 am
Version: 9.4
Excel Version: 2003

Excel Cell Disapearing

Post by dubs » Tue Jun 09, 2009 9:10 am

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
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Excel Cell Disapearing

Post by Wim Gielis » Tue Jun 09, 2009 9:50 am

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
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

summerbrewgal
Posts: 4
Joined: Tue Mar 10, 2009 9:36 pm
Version: 9.4
Excel Version: 2003

Re: Excel Cell Disapearing

Post by summerbrewgal » Tue Jun 09, 2009 2:11 pm

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
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Excel Cell Disapearing

Post by Wim Gielis » Tue Jun 09, 2009 2:31 pm

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

Regards,

Wim
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

dubs
Posts: 131
Joined: Fri May 22, 2009 10:43 am
Version: 9.4
Excel Version: 2003

Re: Excel Cell Disapearing

Post by dubs » Wed Jun 10, 2009 10:51 am

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

User avatar
Martin Ryan
Site Admin
Posts: 1958
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Excel Cell Disapearing

Post by Martin Ryan » Wed Jun 10, 2009 10:56 am

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
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer

User avatar
Alan Kirk
Site Admin
Posts: 6163
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2 Classic (PAW-free zone)
Excel Version: 2010 and 2016
Location: Sydney, Australia
Contact:

Re: Excel Cell Disapearing

Post by Alan Kirk » Wed Jun 10, 2009 11:32 am

Martin Ryan wrote: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...)
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.

summerbrewgal
Posts: 4
Joined: Tue Mar 10, 2009 9:36 pm
Version: 9.4
Excel Version: 2003

Re: Excel Cell Disapearing

Post by summerbrewgal » Wed Jun 10, 2009 11:54 am

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
MVP
Posts: 3416
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Excel Cell Disapearing

Post by lotsaram » Wed Jun 10, 2009 12:50 pm

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
Posts: 131
Joined: Fri May 22, 2009 10:43 am
Version: 9.4
Excel Version: 2003

Re: Excel Cell Disapearing

Post by dubs » Wed Jun 10, 2009 2:01 pm

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
Posts: 131
Joined: Fri May 22, 2009 10:43 am
Version: 9.4
Excel Version: 2003

Re: Excel Cell Disapearing

Post by dubs » Wed Jun 10, 2009 2:57 pm

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
MVP
Posts: 2487
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1
Version: PAL 2.0.8
Excel Version: Office 365 - latest
Location: Brussels, Belgium
Contact:

Re: Excel Cell Disapearing

Post by Wim Gielis » Wed Jun 17, 2009 9:13 pm

The quote below helped me.

Thanks Rebecca. Much appreciated.

Wim
summerbrewgal wrote: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).
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 112 TM1 articles and a lot of custom code
Newest blog article: Updating process line numbers with AutoHotKey

Jessy
Posts: 38
Joined: Wed Mar 25, 2009 7:35 am
OLAP Product: TM1
Version: 9.4.1
Excel Version: 2003

Re: Excel Cell Disapearing

Post by Jessy » Fri May 07, 2010 12:11 am

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

Post Reply