TM1Web Application with ActionButton

[MadWombat]

Hi there,

For about a year now i have been publishing TM1 reports and templates through TM1 web, which is a mostly satisfying solution. I also tend to use action buttons to let users recalculate the sheet or run 1 or 2 specific processes - nothing fancy. The point is, this has worked like a charm, and now it just...doesn't. Not a single file with any single action-button i upload as application will open. (Error occurred while converting to XML, server threw an exception, exception from HRESULT)

The moment i remove the action button there is no problem. I have tried to:

- Find a solution on Google/TM1 forum. Obviously unsuccessful.
- Publish old, previously working versions. These alse generate the same error message.
- Build up a blank XLS sheet, with 1 cell with text (no issue). Added 1 action button or hyperlink to run process/recalculate/do nothing and it generates an error
- Empty INETPUB/wwwroot/TM1WEBEX/Excelsheet, USER dir
- Reset IIS
- Reboot ExcelService/TM1 service

Nothing seems to help. Perhaps worth mentioning: 2 out of 3 clients recently upgraded from Office 2007/TM1 9.5.1 to Office 2012/TM1 9.5.2. Third client (myself) upgraded months ago, but this did not seem to give any problems at that time.

This completely has me puzzled. Does anybody have any other suggestions?

Any help is highly appreciated!

Regards,

R

[Duncan P]

If this is Web then your client version of Office/Excel is unlikely to have anything to do with it. Did the server on which IIS and the TM1 Excel Service resides silently "upgrade" it's version of the .Net framework? Can you pinpoint the time at which it happened and isolate any MS updates that occurred at the same time?

[rmackenzie]

You mention that peoples desktop environments have recently been upgraded, but what has happened in terms of changes on the server? Has the Excel version on the server also been changed? I can imagine that perhaps when the workbook is being converted to HTML an error is occurring around permissions on reading objects from 3rd party libraries (i.e. action buttons).

[MadWombat]

@rmackenzie and Duncan P

Thanks for your quick reply. I have some authorization on our server, but I am not entirely sure what upgrades have been done recently, since it is formally supported by our ICT department (which in fact means that they can do what they want, and if it doesn't work they can blame me, being an obviously ignorant user)

What i do know is that it did NOT upgrade office (has been running Office 2007 32-bit from the get-go). I am not sure of any .Net updates, i will look into that one. Any .Net versions with known issues I should check?

As to the point in time where the errors start occurring: i am not sure about that. I do have some more complex reports/templates which sometimes *just* go corrupt. A broken reference, 2007 vs. 2003 compatibility issues, and so on. I generally just fix it, or rebuild it until it works. Looking back i cannot say for sure when this specific error started popping up.

Hope this helps :-s

[rmackenzie]

What i do know is that it did NOT upgrade office (has been running Office 2007 32-bit from the get-go). I am not sure of any .Net updates, i will look into that one. Any .Net versions with known issues I should check?

I don't think a .NET upgrade would impact TM1 Web functionality. You should be on 3.5 already and the relevant SPs and 4.0 framework should be supported. Perhaps you could check if there were any generic security patch updates which might affect the automation of Excel. Your ICT guys should be able to advise you on this - it is a relatively standard practice to just let these be applied as time goes on. The error message types you mention are definitely linked to the rendering of an Excel workbook into a HTML page, and the HRESULT type errors that occur during this are part of the under-the-hood process that talks to Excel in the background. You could also ask your ICT guys about anything that might have changed the DCOM settings on your server which is part of the configuration on your server about the way one application can automate another.

[Duncan P]

The other piece of software that's involved in the process of generating the web rendering is ASP.NET. Look for upgrades there as well. It's worth looking at the details of all Microsoft (R) patches to see what they claim to have changed.

[MadWombat]

Ok thanks for your help so far. I checked the updates on the server, and could only find updates which took place last year, so that shouldn't be the problem i suppose.

I played around a bit more and i did get *some* templates with buttons to open. It seems to help if the action button is not on that sheet of the report/template that opens by default. So, let's say i have a report a sheet 1 with some text, and a sheet 2 with an button that only refreshes.

1) If i save it with sheet 1 visible and then upload (therefore sheet 1 opens by default in TM1Web) it seems to be fine.
2) if i then save it with sheet 2 visible and update the same file, it doens't open anymore. TaskMgr shows an Excel process, and will keep showing this process untill i kill it.
3) if i then save it again with sheet 1 visible and update it on TM1web: still no success.
4) if i reboot TM1ExcelService it works fine again.

Would this make any sense?

[tomok]

When you are doing these changes and publishing to TM1 Web are you saving it as a new web sheet or choosing to update an existing web sheet? I have found that the update routine is not always foolproof. For this reason, when I update a web sheet I always pull it up and test. If I have any problems I delete the web sheet and then publishing the same Excel file as if it were new report. This usually solves the problems, unless of course the report has errors in it.

[MadWombat]

@tomok

What do you mean by deleting the websheet? As in removing it from c:\inetpub\wwwroot\excelsheet on the server? If so, how do you identify which exact file/folder to remove?

[tomok]

What do you mean by deleting the websheet?

Go to the Applications folder, navigate to where the web sheet is stored and hit the Delete key on your keyboard.

[rmackenzie]

After, or during,the error is occuring, it is worth checking the TM1 Web log which has for me, on occasion, helped find the answer. The location is: SOMEWHERE\Inetpub\wwwroot\TM1Web\log\tm1web.log depending on how your server was set-up but typically inetpub is in C: root.

I often see this error and would tend to suspect it is a red herring in your case, but you never know:

Code: Select all

Last AR data row object was null, check for off 'used range' reference

[declanr]

I am aware this topic is over a year old but thought I would revive it as I have experienced the same issue and been going mad while trying various things to fix it on my laptop:

Windows 7 - 64 Bit
TM1 10.1.1
Excel 2010

The issue was resulting from a windows update that arrived on the 10th April for me (sorry I didn't get the number of it) but it was an update against Excel, the second I uninstalled the update; my websheets with action buttons started opening again.

Just decided to post in case anyone else experiences this in the coming days/weeks after doing their windows updates.

Edit - It was:
Security Update for Microsoft Excel 2010 (KB2597126) 32-Bit Edition
Update type: Important
A security vulnerability exists in Microsoft Excel 2010 32-Bit Edition that could allow arbitrary code to run when a maliciously modified file is opened. This update resolves that vulnerability.

[RSK]

I have the same issue also, I don't suppose you've had any further success with this?

[whitej_d]

A bit of a stab in the dark, but is it worth trying the usual windows update solution:

delete the *.exd files from the user folders and re-register MSCOMCTL.ocx on the server.