converting Number date from TM1 cube to dd mm yyyy format

[Siyad]

Hi gurus,

I am using report studio 10.1 here

I have a date column from a TM1 cube and I am in need to access the member instead of an attribute for a date field and report studio displays the number for the date field from TM1 cube. The number is the number of days from 01-01-1900, eg:- 41306 for Jan 17 2013

anybody know the way i can convert this number easily to a date format

Thanks in advance

Siyad

[BariAbdul]

Hi gurus,

I am using report studio 10.1 here

I have a date column from a TM1 cube and I am in need to access the member instead of an attribute for a date field and report studio displays the number for the date field from TM1 cube. The number is the number of days from 01-01-1900, eg:- 41306 for Jan 17 2013

anybody know the way i can convert this number easily to a date format

Thanks in advance

Siyad

"

What is the back end database,is it Oracle or SQL Server? You have to manipulate through date functions of your native database.Thanks

[Siyad]

Thanks Abdul for the quick reply,

we are using the Oracle data base as the backend. Would you mean that I need to add a logic to convert the number into a date or does the database holds a function to convert the number to a date

Thanks,
Siyad

[David Usherwood]

I'd agree that SQL is generally more functional than TM1 for date manipulation, but the requirement you have is quite doable by TM1. The DAYNO function (usable in rules or TI) returns the number of days since 1 Jan 1960. Add 21916 to get the 'Excel' daycount since 31 Dec 1899.

[Siyad]

Thanks David...I reckon the question is a bit mis-interpreted. I already have the date number (number of days from 01/01/1960) in the TM1 cube which is been done by the TM1 develpers. Now I am writing the report in Cognos report studio and looking for a way to convert into the normal date field

Thanks,
Siyad

[lotsaram]

Look in the FAQ section for the pointer to handling dates and times. From your description it sounds like you would be better off passing this back to the TM1 development team as it seems like the best design in this case would be to convert the TM1 date index value to a date string inside TM1 (which is easily done) so that you can just simply call the formatted date string value in Cognos reports.

[whitej_d]

I'm pretty sure you could use a query calculation in Report Studio to do the manipulation and conversion on the BI side if you really needed to, but I agree that it would be more efficient performance wise to do it in TM1.

In Report Studio, you could use an expression:

_add_days ( 1960-01-01 , [daysfrom01/01/1960fromTM1] ), which should then give you a date stamp which you can manipulate in the usual ways.

[Siyad]

Thank you everybody, this has worked