TM1Web vs Excel Formatting

[jacktuckerman]

Morning all,

I'm currentley working with a client who are using TM1 9.5.1 installed on VMWare Server 2008, 64-bit, 32g RAM who (until now) interact with their TM1 model via various Excel input templates.

One thing that they are keen to do is to facilitate some of their interaction with TM1 via the TM1Web interface, which has been set-up on a VMWare box that matches the specifications of the TM1 server.

Many of their Excel templates allow a user to amend dates, which are DBRW references back into the relevant cubes, as serial date numbers, and the Excel UI formats these numbers as dates. This allows the user to enter any recognised Excel date format in the Excel template and the numeric value is returned to the TM1 cube.

I've not had a great deal of interaction with TM1Web, and the above approach is exactly the way that I would deal with dates where they drive calculations in a TM1 model to avoid using strings.

However, I've noticed that TM1Web is unable to repropduce Excels' date formatting, meaning that when one of these Excel sheets is published via TM1web, if a user enters a date format into a DBRW cell reference, TM1Web returns an error "xxxx is not a number" etc.

Researching TM1Web i noticed that IBM claim that TM1Web supports "all" data validation apart from Custom validation in Excel. Along these lines I attempted to create a in cell picklist based on serial dates formatted as dates, so that a user can select from a pre-defined series of date values. In TM1web, this data validation does not return a list of formatted dates, but serveral lines of "Applix.TM1web.TimeDateFormat" lines. Further to this, attempting to format the serial dates in the data validation reference for the pick list using =TEXT(cellref,"*date format*") is inconsistent to say the least, where different formats return mixed results, typically parsing one element of the date (i.e. =TEXT(cellref,"DD-MMM-YY") returns "DD-JAN-YY", correct on the month value only in TM1Web.

My frustration here is my own inexperience in TM1Web, and I can't believe that this isn't something that hasn't been a problem for people before. My client already has a rich suite of Excel templates which they use to interact with their TM1 model, and it seems that lots of those sheets are almost rendered useless when published in TM1Web.

Any thoughts would be greatly appreciated.

Many thanks in advance,

Jack.

[jacktuckerman]

Just a quick update for anyone that also has issues along these lines:

TM1web will recognise a serial date if I use TM1's custom formatting in the dimension attributes themselves, but I get some strange behaviour in TM1web with any other format other than dd/mm/yyyy. With any elements formatted in that manner, a user can change that numeric value as a date format and succesfully change the serial date in the TM1 cube. Other date formats tend to not format properly in TM1web, with digits disappearing off the year.

Furthermore, it's inconsistent in active forms, an active form can reproduce the behaviour for the very first row, and then all subsequent rows return the numeric serial date value rather than the date format if a user interacts with the data, much like issues with conditional formatting disapearing after the first, or first few, rows of an active form.

Just wanted to add where I'm at, should anyone ever have similar problem.

[lotsaram]

TM1 Web has problems with date formats, I have posted on this before and have had the same issues you are facing now. Other than reverting to storring first as a string value and then validating and converting to numeric index in rules with secondary measures I have not found any workaround. This issue has been with TM1 web for a very long time and obviously does not have a high development priority.

[jacktuckerman]

Thanks lotsaram,

The technique I referred to before, using TM1 attribute formats as a custom dd/mm/yyyy works in TM1web and allows a user to interact with an Excel websheet and change a serial date number in the corresponding cube - just unit tested some sheets changing dates this way and can confirm TM1web supports this if you're careful.

Active forms don't like it, well anything past the first row, also found some inconsistencies in inheriting these TM1 custom formats in Excel generally if slicing a view where the measures are rows, and where elements are strings next to numerics and so forth.

Jack

[William Bockstaele]

I would say there is also a problem in TM1 client, not only in TM1web.
I have a dimension where the elements have a text attribute containing dates as follows dd.mm.yyyy.
We upgraded to 9.5.1 recently and now have the problem with the date in excel templates in the cell where the date is written to the attribute.
The formula used is: =DBRA("tm1server:ERPLN_invoice numbers";$C$6;"DATE")
If I enter a date in the cell containing this formula, the date is converted to a number and the number is sent to the attribute
That did not happen in our previous applix version
If I enter the date dd.mm.yyyy manually in the attributes of the dimension, it is saved correctly and if I actualize the same template again, it shows the date in the exact format dd.mm.yyyy.
The moment I try to change the date from the excel template again, then it is converted to a number again.

Does anyone know what this is? Can it be solved?

[BigG]

We are CX 9.5 which is TM1 9.5.1 and cannot use date formatting for data entry in Tm1web websheets. We have been in contact with IBM and this is hopefully going to be resolved through a CX hot fix, I would have thought it would also be resolved in Tm1 too. In the meantime we have to use strings for dates in TM1 websheets

[sg2012]

Even I am facing same problem while accessing websheet.
when I access websheet through Perspective,it is working perfectly fine.
But if I try to view same websheet using Tm1web, one part of sheet shows 0 value.

The websheet has cube View with no date formats.