TM1 Forum

Using TM1 9.4 and Excel 2003:

Excel will crash when a DBRW formula uses ROW() and COLUMN() functions as arguments.

Can anyone speculate as to why? I guess that there are also many other functions where this occurs.
Will it happen with other TM1 related function? (DBR, DBS etc)

Yes you are quite right.

The official line is TM1 9.4 is fully compliant under Excel 2007 and has a status of 'supported' under Excel 2003. Obviously 2003/2007 are uniquly different for a range of reasons (max rows/colums, etc..).

nhavis wrote:Using TM1 9.4 and Excel 2003:

Excel will crash when a DBRW formula uses ROW() and COLUMN() functions as arguments.

Can anyone speculate as to why? I guess that there are also many other functions where this occurs.
Will it happen with other TM1 related function? (DBR, DBS etc)

Yeah, this looks like a singularly nasty one. It looks like (wild speculation) the bug is in some way integrated into TM1's taking over of the change events. I was able to crash Excel by simply copying the formula to the right (with automatic calculation OFF, naturally). Even saving the sheet (after moving off the formula cell) was enough to crash Excel.

(Not that "Microsoft Application X has stopped Working" is an unusual sight in Vista; I see it at least a couple of times per day, often with Windows Explorer (rather less frequently with non-Microsoft products, oddly), but in this case it did indeed seem to be tied to the formulas.)

Just to add to the fun, I had saved a copy of the file with the formulas. Actually, I saved a couple of DIFFERENT files with DBRW formulas incorporating row() functions. (These files were obtained from autorecover.)

If I didn't open those files, I was able to log into TM1 normally (via Server Explorer) when I restarted the Excel session. If I DID have one of the files open, however, I'd get an error message "Error in macro Excel_TM1ServerConnectSCODE = 800010105. The server threw an exception". The server login would appear to proceed, but the Excel session would be as good as hung; as soon as I did anything it would be locked up.

Have you reported this to IBM?

belair22 wrote:Yes you are quite right.

The official line is TM1 9.4 is fully compliant under Excel 2007 and has a status of 'supported' under Excel 2003. Obviously 2003/2007 are uniquly different for a range of reasons (max rows/colums, etc..).

Excel 2007 also crashes under these conditions...


Alan Kirk: No I haven't reported this as of yet.

Alan Kirk wrote: Yeah, this looks like a singularly nasty one. It looks like (wild speculation) the bug is in some way integrated into TM1's taking over of the change events. I was able to crash Excel by simply copying the formula to the right (with automatic calculation OFF, naturally). Even saving the sheet (after moving off the formula cell) was enough to crash Excel.

(Not that "Microsoft Application X has stopped Working" is an unusual sight in Vista; I see it at least a couple of times per day, often with Windows Explorer (rather less frequently with non-Microsoft products, oddly), but in this case it did indeed seem to be tied to the formulas.)

Just to add to the fun, I had saved a copy of the file with the formulas. Actually, I saved a couple of DIFFERENT files with DBRW formulas incorporating row() functions. (These files were obtained from autorecover.)

If I didn't open those files, I was able to log into TM1 normally (via Server Explorer) when I restarted the Excel session. If I DID have one of the files open, however, I'd get an error message "Error in macro Excel_TM1ServerConnectSCODE = 800010105. The server threw an exception". The server login would appear to proceed, but the Excel session would be as good as hung; as soon as I did anything it would be locked up.

Have you reported this to IBM?

Did we get any solution from IBM for this?

I and some of the users here are also facing the same issue. Not being logged into TM1, I opened an excel file and then tried to log in. After waiting several minutes, I received this message below.

"Error in macro Excel_TM1ServerConnectSCODE = 800010105. The server threw an exception".

hi all,

I'm hitting this tm1server connect error as well whenever I open a specific Excel file. Funny thing is, my DBRW formulae do not contain ROW() and COLUMN() arguments.

Anyone successful solve this error? Many thanks in advance.

This is resolved with the hot fix released recently- TM1 9.4 FP2 HF16

Hello,
Has anyone been getting this error recently? we are using 9.4.1 fix pack 3 and are getting this with excel 2007. It happens when a macro file open and when we try to login to TM1 it crashes..
I've asked IBM but want to see if anyone has experience of it - we has hot fix 16 on Fix Pack 2 but were still getting the problem so we moved to fix pack 3.
thanks,
Fionnuala

Yes - we're getting the same issue on FP2 HF16 (crashes if you have the file open, then log into TM1)

Had a reply from IBM on this - answer is to not run Excel 2007 in 'compatability mode'.

Problem is (for those unfamiliar with Excel 2007), compatability mode automatically clicks in whenever you open a pre-2007 Excel file (xls extension). If you resave the file as Excel 2007 (xlsx extension), the problem seems to go away (hae done a quick test but not a rigourous one).

Where I am, they are currently migrating to 2007 => old Excel files are all over the place and will be for a long time to come, so turning off compatability mode is a poor workaround at best.

Have gone back to IBM for more details (is this on a list of fixes for future versions?)

M

We upgraded to TM1 9.4.1 FP3 from 9.4.1 MR1 and applied SP2 to Excel 2007 and started getting the "Error in macro Excel_TM1ServerConnectSCODE = 800010105. The server threw an exception" error. We do not use the ROW() and COLUMN() functions and all of the workbooks that crashed worked in 9.4.1 MR1. In trying to resolve the issue, I noticed that the files will open in excel and only when logging in TM1 will excel crash. Taking a hint on the earlier post in this thread, I decided to open up the file and not login TM1. When the file opened I did a Find and Replace of DBRWs to DBRs and saved the file. I logged in TM1 and Excel did not crash. I repeated this procedure with other spreadsheets that crashed and the procedure worked.

What I think may be causing the crash is some sort of Error handling in TM1 that addresses DBRW errors. There is one thing I noticed that was common in all of our scenarios. The spreadsheets that crashed had DBRW formulas referencing an old cube structure. Specifically, the worksheet formula references fewer dimensions than what the current cube structure has. Once the DBR formulas were corrected everything worked.

APAR PM17042 is to address this.

the bug is in some way integrated into TM1's taking over of the change events.

Alan, we have an error in 9.5 related to this, We use worksheet protection to stop our users from messing around with the formulas and structure of our excel 2003 reports (mix of active forms and slices) and this is fine in 9.4.

In 9.5 this protection stops the TM1REFRESH call working that we use on change events and it prompts for the worksheet password whenever it tries to rebuild the form. I opened a call with IBM who have confirmed that it is not a bug but a feature they have introduced into 9.5.

Sorry this is OT but just thought I would warn everyone that the changes in how TM1 handles change events in 9.5 causes problems for reports which work in 9.4.