TM1 Forum

Hello!
As help says, DATEPART returns an integer. But TI process sees it as float. How to solve this newbie trouble? I need to use items as elements of dimensions.

If you can't set the items to text on the variables data tab then use NumberToString and SubSt or Delet and maybe Scan too to get to the bit of the string you need from the converted number.

Cheers,

wowow! of course they set to string! another way i can't use them as elements of dimension in process!

Steve Rowe wrote:If you can't set the items to text on the variables data tab then use NumberToString and SubSt or Delet and maybe Scan too to get to the bit of the string you need from the converted number.

Cheers,

in Russia we call this solution "The Crutch" cos it does not solve reason but mask it and cost lot of machine resources and may cause to reworking if some conditions change. I believe best solution exists, but i can't find it myself.

ioscat wrote:As help says, DATEPART returns an integer.

What help would that be? Not TM1, since that's a SQL function.

ioscat wrote:But TI process sees it as float.

That's because as far as TM1 is concerned there are only two data types, floating point numeric and text. The concept of an integer does not exist. If TI detects only numeric characters in a field of a data source, it is going to assume the data is numeric and therefore show it as floating. There is no way around this. You really have only two options; 1) convert the field to text using NumberToString (my recommendation) or 2) trick TI into thinking the data is string by concatenating a letter to the end of field (as part of your SQL statement) and then deleting the character in the TI by using the SUBST function. I don't know why anyone would want to try option 2, I just threw it out there for kicks. As to option 1, I use it all the time, don't see what the big deal is.

tomok wrote: What help would that be? Not TM1, since that's a SQL function.

you are right, i mean http://msdn.microsoft.com/en-us/library/ms174420.aspx

tomok wrote: That's because as far as TM1 is concerned there are only two data types, floating point numeric and text. The concept of an integer does not exist. If TI detects only numeric characters in a field of a data source, it is going to assume the data is numeric and therefore show it as floating. There is no way around this. You really have only two options; 1) convert the field to text using NumberToString (my recommendation) or 2) trick TI into thinking the data is string by concatenating a letter to the end of field (as part of your SQL statement) and then deleting the character in the TI by using the SUBST function. I don't know why anyone would want to try option 2, I just threw it out there for kicks. As to option 1, I use it all the time, don't see what the big deal is.

=-(

Best guess would be to force the result to string in the SQL statement and then set the result to string in the variables tab.

This sort of feels most right in terms of process. This might not work since TI might still guess that they are numbers and convert them to floating point again, not sure.

It's open to debate if that's anymore efficient than converting the numeric to a string in the TI but I guess if you do the type conversion in the SQL then you shouldn't need to drop the ".00000"

You are going to need to do the type conversion somewhere though.....

Try something like It works for me and comes back with no decimal point nor trailing zeros. Whether plain old varchar is the most efficient form is anyone's guess, but there it is.

I am using SQL Server 2005 and the SQL Native Client ODBC driver into TM1 9.5.2.

it works! thank you, Duncan!