Recalculating protect worksheets in v9.4.1.3 / Excel 2007

[Oratia623]

Hi,

I am testing TM1 9.4 MR1 FP3 and am having issues with password protected Excel 2007 worksheets.

Each time I recalculate a password protected worksheet via Shift+F9, I get presented with the Excel Unprotect sheet password dialog box! (the same box that is presented when you try to unprotect a sheet.)

Pressing OK gives me a runtime error (can only assume from the tm1p.xla which is pswd protected so can't see when it is doing)
Pressing Cancel allows a calculation as per normal - however subsequent Shift+F9 get the same password dialog box presented.

Calculating via pressing the 'Calculate Now' or 'Calculate Sheet' button within the Formulas ribbon, does not present the issue.
Pressing Shift+F9 on a sheet protected without a password also does not present the issue.
Any ideas what is causing this and how to avoid it?

Protecting sheets with passwords is part of the client’s deployment strategy for centrally maintained reports and this could be a show-stopper for their upgrade to 9.4.1.3, so any assistance would be greatly appreciated.

To replicate the issue:
open a new Excel workbook.
create a simple DBRW in a cell (for simplicity using hardcoded elements, not cell references)
protect the sheet with a password - using Excels standard default protection settings.
press Shift+F9

Environment:
64bit TM1 9.4 MR1 FP3
Fully updated Excel 2007.

Many thanks
Paul Williamson

[FARGEADX]

Hi
we just migrate from 9.1 to 9.5 and we have similar behavior in Excel
We use Excel template and DBS - DBSW formula to load data in TM1 and the formula are in a Hidden and password protected sheet
Each time we use the F9 function key it pop-up a windows to enter the password to unprotect the sheet before allowing calculation and data load.!

This seems to be new with 9.5 , but I am not 100% sure .
I asked if any security update have been made on or servers and Computer or if Windows - Microsoft updates have been applied and it does not seems to be the case.

Any idea on what could create this new behavior which is quite time consumming and totally not productive for people in charge of loading reporting file

Thanks in advance

PS : Excel 2003 SP3 -

[Oratia623]

Hi,

Ah HAH, I was starting to think it was just me!
Thanks for validating my sanity!

Further things to add:
1) The issue does not present itself in Perspectives 9.4 MR1, but does when Perspectives 9.4 MR1 FP3 is then installed on top. (I have not tried FP 1 or FP2)
2) If you actually enter the correct password in the dialog box, the sheet calculates and is left protected but with no password
3) The issue even presents itself on a blank password protected spreadsheet.

Cheers
Paul

[FARGEADX]

Shoul we escalate this as a BUG ?
and ask IBM - Cognos to look at it ?

[garry cook]

Haven't got into fully testing this version on Office 07 yet however if it really is a bug, it's a worrying one. My first instinct is probably that it's a regression bug otherwise from your SP as I would have thought it would have been picked up prior to this but may be wrong.

Without being able to test, I'd suggest that a possible workaround working in your current limitations (ie not going for an earlier stable SP) might be to capture F9/Shift F9 on an OnKeys event and force the calculation through VBA.

That's the way I've got round other broken functionality in the past. Not sure if it will work and bit of a pain but worth a shot if you're going to struggle to change SP.

Regards

[Oratia623]

Thanks Garry,

I also had the same thought of capturing the F9/Shift+F9 keystroke.
To then process the calculation I would need to call either the W_CALC or TM1AVIEWRECALC1 functions as these do not ask for the password.
The TM1RECALC1 (Shift+F9) TM1RECALC (F9) or TM1REFRESH all still asks for password and Activesheet.Calculate does not send DBS to TM1.
(interestingly neither W_CALC or TM1AVIEWRECALC1 are in the manuals....)

I am not relishing the idea of having to modify all the VBA code of every password protected model out there, but I suppose at a pinch we could install an add-in to each users Excel session that modifies the F9/Shift+F9 functionality, but I would just prefer the issue fixed to be honest.

I have seen the same issue reported in v9.5 on IBMs own user forum

I will escalate this as a bug.
FARGEADX can you do the same?

Thanks
Paul

[Oratia623]

Have received reply that from IBM that this bug is reported and is an open ticket item: APAR #PM10715.
No resolution or further details yet, but will post as we find out.

[FARGEADX]

I ask our integrator consutlting partner to create a ticket at IBM - Cognos
Thanks

[Oratia623]

IBM Have issued a HOT Fix 5 for TM1 9.4.1 FP3 which is intended to resolve this.

I have applied HF5 to the client and server (it contains replacement \bin files for both)
Initial testing has found that whilst it does fix the issue of getting asked for the password (and subsequent VBA errors if none is entered) on password protected worksheet containing straight TM1 formulas, I have still been able to make it get the same errors when a password protected sheet containing an Active Form is recalculated either by F9, Shift+F9, Alt+F9 or any of the corresponding VBA calls.
Why anyone would want to password protect an Active Form sheet, I am not quite sure, so this may never result, but something to keep an eye out for.

Paul


The list of the resolved issues from the HotFix installs instructions:
*********************************************
APAR Title
PM05523 TM1 dimensions with shared members across multiple hierarchies have very long preload times
PM10078 Connect tool very slow compared to ISB connect
PM10379 TM1 Server crash
PM10592 FP3 only : during first recalculation of a protected sheet, TM1 Workflow triggers a prompt to unprotect sheet
PM10649 Server does not prevent an old client from crash a newer server - this fix requires usage of a new configuration parameter - see below for more details
PM10671 Slow performance of TI initiated by Action Button on Websheet
PM11015 TM1 clients Architect and Perspectives crash when opening a view
PM11249 Export from a view is not exporting all of the data expected
*********************************************

[Gregor Koch]

Hi Oratia623,

Thanks for the info. Could you please point me to link where you get the install instructions?
Regards

[Oratia623]

We had it emailed to us direct from Cognos Support...
See attached.