Number-Formating for Text-Attribute

[Willi]

Hi,

we use TM1 9.5.2 with Excel 2007 and 2010 as Frontend.

I have a Dimension with a Text-Attribute called "Hierarchy" where users should enter a hierarchy in the Format "01.02.03". So 3 2-digit-numbers with a dot as Separators. The Excel-Cell is formatted as "Standard" and in it there is a DBRW für the Attribute-Cube. But if the user enters "01.01.01" the Content is immediately changed in "36892". If I Change the formatting of the Cell to "Text" the formula is treated as text and just shown as it is. So how can I do this?

Thx and best regards,
Willi

[declanr]

Hi,

we use TM1 9.5.2 with Excel 2007 and 2010 as Frontend.

I have a Dimension with a Text-Attribute called "Hierarchy" where users should enter a hierarchy in the Format "01.02.03". So 3 2-digit-numbers with a dot as Separators. The Excel-Cell is formatted as "Standard" and in it there is a DBRW für the Attribute-Cube. But if the user enters "01.01.01" the Content is immediately changed in "36892". If I Change the formatting of the Cell to "Text" the formula is treated as text and just shown as it is. So how can I do this?

Thx and best regards,
Willi

I might be misinterpreting your question here but it sounds like you have a string cell in TM1; when entering the data in excel - if formatted as general, excel assumes it is a date... if you format it as text, they can enter it correctly. So you have answered your own question with the answer being to change the excel cells formatting to text. As such I assume I am misinterpreting what it is you are asking?

[Willi]

Thx for the replay. And you're not misinterpreting my question. But if I define the Cell (actually it's a column in an active form) as "Text" the formula in the cell itself is displayed and not interpreted anymore.

[Alan Kirk]

Thx for the replay. And you're not misinterpreting my question. But if I define the Cell (actually it's a column in an active form) as "Text" the formula in the cell itself is displayed and not interpreted anymore.

Quite so. Unfortunately this is Excel being a pain in the posterior and "helping" you in a way that can't be turned off. It annoys the becheezes out of people and is something that MS should have been focussed on rather than impenetrable ribbons, but when software companies get fixated on "smart" features it can be difficult to prize their fingers back to reality.

The short version is this; if you type something that looks like a date into an Excel cell (whether the cell has a TM1 formula or not) then Excel will 'help' you by trying to convert it to a date. And dates are just serial numbers (as discussed here), which is why you get 36892 as the input value. And there is not a single thing you can do to stop this behaviour.

You therefore have two options:
(a) House train your users to type a single quote mark ahead of the date, which tells Excel "Hey idiot, this is a string, got it? A S-t-r-i-n-g. Don't 'help' me by changing it to what you think it is." Obviously this is not ideal, but neither is the other option.
(b) You create an input form which has the input cell empty and formatted as text, and use a DBSS() formula to send that value up to the cube. Obviously this doesn't work on raw slices. If users do a lot of slicing out of the cube you have no option but to house train them to type the quote mark first.

[Willi]

Thx. It's not what I wanted to hear but that's not your fault