TM1Web 9.4 MR1 Websheet formatting

Ideas and tips for enhancing your TM1 application
Post Reply
User avatar
George Regateiro
MVP
Posts: 326
Joined: Fri May 16, 2008 3:35 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP3
Location: Tampa FL USA

TM1Web 9.4 MR1 Websheet formatting

Post by George Regateiro »

I have searched the docs and did not see anything related and from what I could see. We just got our 9.4 MR1 test environment set up and the first thing that we noticed was all our formatting on the websheets went away. I have uploaded a few test templates that are nothing more then formatted text and cells of various colors and nothing come accross on the web. Does anyone have any ideas as to what could be causing this?

If I open them up through the client the formatting is there, this is an issue only affecting TM1Web.
Last edited by Martin Ryan on Wed Jul 01, 2009 4:11 pm, edited 2 times in total.
Reason: made sticky, can see this catching a lot of ugradees out
User avatar
George Regateiro
MVP
Posts: 326
Joined: Fri May 16, 2008 3:35 pm
OLAP Product: TM1
Version: 10.1.1
Excel Version: 2007 SP3
Location: Tampa FL USA

Re: TM1Web 9.4 MR1 Websheet formatting

Post by George Regateiro »

Ok figured I would post and update.

Turns out this upgrade added a few new folders in the TM1 Web IIS folder structure. During the install it sets this up to be an annoymous login site. Since our security was based on intergrated login we did not give the IIS default user access to anything on the server. This was corrected and all formatting is appearing now. It is just kind of strange that they broke off into two folder branches with this upgrade and did not put anythng in the install docs we had that let you know that they were not kept in synch with regards to security.
harrytm1
Regular Participant
Posts: 226
Joined: Thu Apr 02, 2009 2:51 pm
OLAP Product: IBM Planning Analytics
Version: Latest version
Excel Version: 2003 to 2019

Re: TM1Web 9.4 MR1 Websheet formatting

Post by harrytm1 »

Speaking of formatting in TM1 Web, I'm trying to develop an input template for overseas users who can only access TM1 through web.

As there can be quite a lot of data to load through the web Excel template, which are essentially DBRW cells, I created a pure Excel template i.e. without any TM1 formulae. This is meant to allow overseas users to fill them in offline.

This "pure" Excel template contains Validation drop-down lists to allow users to select n-elements in various dimensions. I use validation list to prevent users from entering wrong codes. In TM1 Web, users are allowed to copy from normal Excel sheet and paste into DBRW cells in a TM1 Web Excel sheet (which you can't if you do it purely in Excel).

However, I noticed that, if I paste the values of Validation List into the corresponding TM1 Web Excel sheet, it somehow adds a linebreak in the cell. Thus, although it appears to be a valid n-element code for a dimensioin in the TM1 Web cell, it will not be recognosed. So when I tried to submit the data in the cells, it will not be successful and the data will be erased when the web Excel sheet refreshes.

So is this a bug or pasting from Validation List is never supported? One way to get around is to first copy-paste value only into another blank Excel sheet first, then paste into TM1 web again. This additional step will clean up the value.
Planning Analytics latest version, including Cloud
User avatar
rollo19
Posts: 80
Joined: Wed May 28, 2008 2:42 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Perth, Australia

Re: TM1Web 9.4 MR1 Websheet formatting

Post by rollo19 »

harrytm1> sounds like an interesting workaround to the offline/remote problem. Let us know how you get on!
If you have a large number of Contributors, perhaps you could use Cognos Enterprise Planning for the bottom up stuff..
olapuser
Posts: 40
Joined: Fri Jan 29, 2010 1:55 am
OLAP Product: Cognos TM1
Version: 9.5
Excel Version: 2007
Contact:

Re: TM1Web 9.4 MR1 Websheet formatting

Post by olapuser »

same problem with George Regateiro, running vista ultimate and it works on xp can someone help me?

planning sample on vista
vista tm1web.jpg[attachment] planning sample on xp [attachment=0]xp tm1web.jpg
Attachments
xp tm1web.jpg
xp tm1web.jpg (150.21 KiB) Viewed 18898 times
vista tm1web.jpg
vista tm1web.jpg (131.31 KiB) Viewed 18908 times
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: TM1Web 9.4 MR1 Websheet formatting

Post by BigG »

I Know this is an old post but we have a new install 9.4 TM1 through the Cognos Express 9.0 install. We cannot resolve the formatting of websheet after even giving 'everyone' group permissions as well as the default IIS user permissions to the Tm1 web install folder(C:\Inetpub\wwwroot\TM1Web\).

Has anyone else had this issue or are there more specifics on how to potentially resolve this?

thanks in advance
GG
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: TM1Web 9.4 MR1 Websheet formatting

Post by lotsaram »

This issue applies to 9.4 and 9.5. It is caused by a combination of security permissions on the TM1WebEx directory and TM1WebEx pointing to an incorrect location for the TM1Web virtual directory.

Generally these symptoms show when a non-default location outside of the C drive is selected as the inetpub location in which to install TM1Web. The install process sets everything up correctly for the TM1Web directory but leaves TM1WebEx pointing to the default location for the TM1Web virtual directory.

To fix
1. In IIS manager point TM1WebEx to the correct location of Tm1Web
2. in windows explorer set the TWebEx folder to inherit the security permissions of the TM1Web folder
3. restart IIS

This should fix the issue you describe and give you websheets with formatting and images.
Marcus Scherer
Community Contributor
Posts: 126
Joined: Sun Jun 29, 2008 9:33 am
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2016
Location: Karlsruhe

Re: TM1Web 9.4 MR1 Websheet formatting

Post by Marcus Scherer »

This annoying formatting issue also appears with Netegrity Siteminder 6 for access management in place. IIS will create an additional web.config file directly in \wwwroot which is somehow overlaying the original web.config in the standard installation folder. Settings are TM1 9.5.1 64bit, Windows Server 2008 R2, IIS 7, Cognos 8. Trying to merge both files but no solution in this case yet...
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: TM1Web 9.4 MR1 Websheet formatting

Post by BigG »

We resolved this with IBM based on supported environment for Cognos Express 9.5 windows 2008R2 (which we have just upgraded too).

The IBM response was
setting the absolute path explicitly in the web.config file to your location, this is why I had requested your web.config file.

To do this, update the following line from the web.config file;

<add key="UseSeparateVirtualCache" value="Y;Url=;AbsolutePath=" />to: <add key="UseSeparateVirtualCache" value="Y;http://localholst/tm1webex=;AbsolutePath=C:\Program Files\IBM\Cognos Express\Xcelerator\TM1Webex" />

This is more typically an issue if not installed into the default location and that is not the case for this install but v9.0 was to a different location so I believe you should give this a try.
also another check from IBM:
confirm the properties for TM1webex have the expected local path
TM1webex_properties.doc
(34.5 KiB) Downloaded 884 times
GG
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:

TM1Web 9.5.2 Websheet formatting

Post by Alan Kirk »

I can't believe that this many versions in the act of installing in a non-standard location still causes grief. I mean seriously, considering the number of servers out there where the thing is installed on a partition with C:\ being reserved for the system disk...

Anyhoo, I had exactly the same symptoms on my Windows 7 Ultimate 64 bit test box. Web sheets showed, number formats showed, colours didn't. Then I found this thread. I'll add my own fix in case it's of use to someone in the future.

I right clicked on the Computer and selected Manage, Services and Applications, Internet Information Services. In the Connections panel I opened the node for the computer, then Sites, then Default Web Site.

Clicking on the TM1WebEX folder yielded an error message that the path could not be found. Right clicked on it, selected Manage Virtual Directory -> Advanced Settings. The physical path showed as one in C:\aspdir whereas the correct path was F:\inetpub\wwwroot\TM1WebEx. I changed that, kicked over IIS and the Excel service and voila, colours in the web sheets again.

This thread's getting FAQed.
"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.
u970700
Posts: 13
Joined: Wed Nov 24, 2010 3:27 am
OLAP Product: TM1
Version: PAL 2.0.9.2
Excel Version: Excel 2016
Location: Darwin, Australia

Re: TM1Web 9.4 MR1 Websheet formatting

Post by u970700 »

Can I correct BigG's IBM response, there are a couple of errors with the explicit UseSeparateVirtualCache settings. It's missing the "Url=" component , and "localholst" is suppose to be "localhost", I found out the hard way!
<add key="UseSeparateVirtualCache" value="Y;Url=;AbsolutePath=" />to: <add key="UseSeparateVirtualCache" value="Y;http://localholst/tm1webex=;AbsolutePath=C:\Program Files\IBM\Cognos Express\Xcelerator\TM1Webex" />
The correct syntax should be:

<add key="UseSeparateVirtualCache" value="Y;Url=;AbsolutePath=" />to: <add key="UseSeparateVirtualCache" value="Y;Url=http://localhost/tm1webex=;AbsolutePath=C:\Program Files\IBM\Cognos Express\Xcelerator\TM1Webex" />

More details can be found in the TM1 Operation Guide, search for "UseSeparateVirtualCache", version 9.5.1 onwards.
TM1 9.5.2 FP3, Windows 2008 R2 64bit, Excel 2003 SP3
Extirpator
Posts: 6
Joined: Fri Nov 23, 2012 7:11 pm
OLAP Product: TM1
Version: Planning Analytics 2.0.7
Excel Version: Excel 2016 64-bit
Location: Dallas, TX

Re: TM1Web 9.4 MR1 Websheet formatting

Post by Extirpator »

This isn't the perfect place to post my solution; however, the screenshots above perfectly described my situation after installing TM1 10.1.1 FP1 and searching here for a solution. My websheets were created when running TM1 9.5.2 FP2.

My values and number formats were coming across just fine but the cell shading and borders (the pretty stuff) was missing. The formatting is stored in the TM1WebEx folder, so for me the fix was to make sure the the TM1WebEx virtual directory in IIS has the proper authentication type (equal to how the TM1Web virtual directory is set, since you can access TM1Web... just not the formatting that is accessed through TM1WebEx).

The details:
(I am running Windows Server 2008 R2 64-bit. The path you take to accomplish this might be a variation of this.)

Open Server Manager >
Roles >
Web Server (IIS) >
Internet Information Services (IIS Manager)

Then in the Connections pane, drill down:
[Your server name] >
Sites >
Default Web Site >
TM1WebEx (select it with a single left mouse click)

Then in the big pane with /TM1WebEx Home for a title:
(Verify that Features View on the very bottom of the screen is selected)
In the IIS section in the center, double click Authentication.

You will see a variety of authentication types. In my case, all were disabled.

I am using Active Directory / Windows Authentication, so I enabled it. (My Tm1s.cfg files have IntegratedSecurityMode=5.)
Post Reply