TM1 Perspectives support for Office 365 & Excel 2019

Post Reply
fleaster
Regular Participant
Posts: 167
Joined: Wed Mar 30, 2011 11:57 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: XL2010

TM1 Perspectives support for Office 365 & Excel 2019

Post by fleaster »

Hi all,

Apologies if this is a dumb question, but wanted to check if anyone has been able to get TM1 Perspectives to work with Office 365 & Excel 2019?

i.e. I know the (current) official word from IBM is that Perspectives would only be supported up to XL 2016, however, was curious if anyone was able to successfully connect Perspecties with O365/XL2019...?

Regards,

Matt

https://www.ibm.com/support/pages/offic ... rspectives
TM1 Perspectives
Perspectives is only supported on Microsoft Excel versions up to 2016. TM1 Perspectives is testing support for Microsoft Excel 2019 and current versions of Office 365. Support will be updated once testing is complete.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by Alan Kirk »

fleaster wrote: Tue Jun 02, 2020 7:56 am Hi all,

Apologies if this is a dumb question, but wanted to check if anyone has been able to get TM1 Perspectives to work with Office 365 & Excel 2019?

i.e. I know the (current) official word from IBM is that Perspectives would only be supported up to XL 2016,
Until testing is done, according to the quotes below.
fleaster wrote: Tue Jun 02, 2020 7:56 am however, was curious if anyone was able to successfully connect Perspecties with O365/XL2019...?

Regards,

Matt

https://www.ibm.com/support/pages/offic ... rspectives
TM1 Perspectives
Perspectives is only supported on Microsoft Excel versions up to 2016. TM1 Perspectives is testing support for Microsoft Excel 2019 and current versions of Office 365. Support will be updated once testing is complete.
I've been using O365 Excel for well over a year now. (Note to self; must update my profile.) Rarely have I encountered a problem with it.

The problem with O365 vs Excel 2016 and Excel 2019 (that is, the perpetual licence versions) is that the former is always a work in progress, getting regular updates. The latter are more static; bug fixes but no new features.

There was one occasion when an update to O365 during the 2016 era broke the add-in, but it was rectified within a couple of weeks. There is also a problem with using dimension Excel sheets under 64 bit.

Beyond those, I've not had any problem. However if you're on the O365 route, I would advise against allowing automatic updates to avoid a repeat of the first issue. Test first, do a general update later.
"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.
fleaster
Regular Participant
Posts: 167
Joined: Wed Mar 30, 2011 11:57 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: XL2010

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by fleaster »

Thanks for the info!

So it seems like from what you're saying, that TM1 Perspectives can work with O365, even though the official company line from IBM is that "it's tba" (presumably because they see it as a legacy module and want to push PaX instead..??)

...either way, I guess we'll have to install everything, do some testing and see :)

Cheers,

Matt

P.S. on a random note re: your avatar - my wife and I rewatched NCIS seasons 1-4 recently... it's still good after all these years :)
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by Alan Kirk »

fleaster wrote: Tue Jun 09, 2020 3:05 am Thanks for the info!

So it seems like from what you're saying, that TM1 Perspectives can work with O365, even though the official company line from IBM is that "it's tba" (presumably because they see it as a legacy module and want to push PaX instead..??)
Certainly that would be their preference, but the last time I tried to install it, it installed, it ran, but I couldn't even get it to talk to itself. That was on a sandbox Windows Server machine which was isolated from the main network and using a local admin login. Then earlier this year, just before the entire planet virally crashed, I got a networked box with Windows Server 2019 on it which I hoped would get around the pain in the backside connection string issue... except that it has no external network access (why? Who knows) meaning that I couldn't install Docker on it and the IT department has been too busy supporting remote working to deal with that. If you missed the memo, I'm fed up to here {gestures to throat} with PAX. It's frapping ridiculous to have to get advanced knowledge in Linux and networking just to get a subset editor working. On which subject, name me ONE serious application other than TM1 where you NEED to go to a command line constantly and execute scripts for installation and/or functionality failures in the year 2020. I can't think of one. Not one.

However... I don't think that's the key problem with O365. The key problem with that is what I mentioned above; it's a moveable feast. With O2016 or O2019 you can test and you know what you'll get. There may be fix packs every few months, but you can test them too. With Office 365, on the other hand, functionality could suddenly change with an update. So... at what point do you certify compatibility, when it may be different next week or next month? It's not an easy problem to resolve.
fleaster wrote: Tue Jun 09, 2020 3:05 am ...either way, I guess we'll have to install everything, do some testing and see :)
Indeed. Depending on how your licencing is set up you may be able to regulate updates via Microsoft Endpoint Configuration Manager or similar.
fleaster wrote: Tue Jun 09, 2020 3:05 am P.S. on a random note re: your avatar - my wife and I rewatched NCIS seasons 1-4 recently... it's still good after all these years :)
I must confess that I've not seen a single episode since season 10, primarily because of "too much to watch and do, too little time". I believe it's up to season 17 now, the implication of which (when it comes to the passage of time) is truly depressing.
"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.
mivers
Posts: 6
Joined: Tue Nov 20, 2018 12:59 pm
OLAP Product: IBM Cognos TM1 Perspectives
Version: 10.2.2
Excel Version: 2010

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by mivers »

Hello,
How did you get the add-in to work with Office 365 versus Office 2016? I use the Perspectives add-in. Not sure how to get this to work with 365.

Thanks for any assistance.
Matt
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by Alan Kirk »

mivers wrote: Fri Sep 30, 2022 5:46 pm How did you get the add-in to work with Office 365 versus Office 2016? I use the Perspectives add-in. Not sure how to get this to work with 365.

Thanks for any assistance.
Note the dates on the previous messages in this thread; 2020.

Now, Microsoft being Microsoft and loving to rename things just to hack customers off, what was THEN called Office 365 (and the thing that we were talking about in that thread) is now part of "Microsoft 365". That is, a subscription service where you install the applications on your Windows device and run them locally.

(Actually the name had changed a couple of months before that but Microsoft don't pay me enough to keep up to date with the latest brain flatulence of their marketing 'droids.")

Confession time; I do still often refer to this as "Office 365" even to this day, since I generally want to refer to the Office applications and not to extended sets of applications that I may not give a flying fig about. With the "broad enough to be meaningless" name "Microsoft 365" there is now no neat way of doing that. (Microsoft: "Uuuuuh, duh, we didn't fink of that!") What makes it worse is when they recycle a name that used to mean one thing to refer to something else, as has happened here.

What is NOW Office 365 is what was THEN known as Office for the Web (or "Office on the Web", or any of half a dozen similar names). This is not the thing that we were talking about, but I presume it's what you're talking about. In which case the answer is... you can't.

You will have already noticed that there is nothing in File => Options that lets you add add-ins. (In fact there is damn near nothing in Options compared to the real Excel.)

However the killer can be found in this article in Microsoft's support pages:
File formats that are supported in Excel for the web
  • Excel workbook files (.xlsx)
  • Excel 97-2003 workbook files (.xls).
  • Note: When you open this file format, Excel for the web will convert it to a newer .xlsx file. You can always download the original by going to File > Info > Previous versions.
  • CSV (Comma delimited) file (.csv)
  • Note: When you open this file format, Excel for the web will convert it to a newer .xlsx file. You can always download the original by going to File > Info > Previous versions.
  • Excel binary workbook file (.xlsb)
  • OpenDocument Spreadsheet file (.ods)
  • Excel macro-enabled workbook (.xlsm)
  • A workbook in this format can be opened but macros do not run in a browser window.
(My emphasis.)

Since Perspectives is essentially a macro-based workbook, no macros means no Perspectives.
"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.
User avatar
vovanenok
Posts: 88
Joined: Mon Jun 23, 2014 4:54 pm
OLAP Product: TM1
Version: 2.0.9
Excel Version: Office 365
Location: Toronto, Canada
Contact:

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by vovanenok »

I've been also using TM1 Perspectives with Microsoft Office 365 no problem.

Unlike the previous Office versions, you are getting Office 365 updates more often and obviously that would be challenging for IBM to catch up and commit to support each next one. Also IBM is focused more on PAfE, so I wouldn't expect much support for TM1 Perspectives
----------
TeamOne Google Sheets add-on for IBM Planning Analytics
Let's connect on LinkedIn
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by Alan Kirk »

vovanenok wrote: Tue Oct 04, 2022 7:13 pm I've been also using TM1 Perspectives with Microsoft Office 365 no problem.

Unlike the previous Office versions, you are getting Office 365 updates more often
As I wrote above, OFFICE 365 is now the name that Microsoft is using for the WEB BASED version of Office, and has been for two years.

The subscription-based, LOCALLY INSTALLED version with regular updates is now called MICROSOFT 365.

The problem appears to be that Matt is trying to get Perspectives to run in the former, not the latter.
"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.
gabi
Posts: 25
Joined: Thu Jun 19, 2008 12:44 pm

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by gabi »

Hello

There was a time Excel 365 was supported by IBM for Perspectives and it worked good enough.
Recently, I got many calls from customers running Perspectives on Excel 365 - Perspectives fails to load with the familiar run-time error 429.
It seems Microsoft changed the way, Excel add-in dll's registration so regsvr32 tm1prc.dll tm1xl.ocx not solving 429 error.
Opening Excel 365 as Administrator and then open Perspectives temporary solves the problem and allows working with Perspectives using Excel 365.
Few days/weeks later, 429 error returns (probably due-to Microsoft 365 updates).

I planned to figure out new ways to register Perspectives dll but I couldn't find the time yet.
I'll appreciate any help allowing permanent solution for Perspectives on Excel 365.

Thank you,
Gabi
Gabi Cohen
Biconix International
User avatar
WilliamSmith
Posts: 37
Joined: Tue Dec 13, 2022 8:54 pm
OLAP Product: TM1 / PA / PAx / PAW
Version: TM1 11
Excel Version: 365

Re: TM1 Perspectives support for Office 365 & Excel 2019

Post by WilliamSmith »

Throwing in my 2 cents... we've largely migrated to the Planning Analytics for Excel add-in. Using the code snips below that include the .Dirty() method that I stumbled upon, we've been able to suspend normal calculation, trigger the TM1 batch call update, then return to manual calculation mode. It refreshes recursively all formula chains that splinter off from the ranges that were marked as dirty. Meaning, e.g. if you have a report workbook where your TM1 calculations all tie back to a date range, if you mark the date range as dirty, all TM1 calls that point to that range should batch update.

Code: Select all

Sub RecalcSelection()
    
    calc = Application.Calculation
    
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    Selection.Dirty
    
    Application.Calculation = calc

End Sub

Code: Select all

Sub RecalcSheet()
    
    calc = Application.Calculation
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Application.EnableEvents = False
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    
    ws.Cells.Dirty
    
    Application.Calculation = calc
    
End Sub
Post Reply