Date column sorting displaying wrong order

[saumil287]

Hi All,
I am having a DMR model, Datasource is csv file.
I am having a date column in Time dimension which is character length 16 data type and want to convert to date datatype.
On sorting the date column, it is displaying as follows:
4/1/2012
4/10/2012
4/11/2012
...
...
4/19/2012
4/2/2012

when soring as ascending order

I tried with various cast funcs, On validation It is showing no errors, but on running the report displaying errors.
I also tried various format of date but didn't worked.

Is there any way to sort these items or convert to date from report studio or framework manager which is in time dimension.

The source of date in time dimension is date time but its business key is in character length 16 format.
and day key also in the same format

Your guidance is appreciated
Thanks in advance.

[CoolP]

Hi,

Please look at any of the sample DMR FM models provided with cognos, they use business key to sort in the background, although the member caption is string.

Thanks
Prasan

[saumil287]

Hi,
I checked with d2g demo which is having Day as business key and is cast to date using cast(exp,date) which is supported by sql server db.
I am having CSV files which cast funcs is not supported.
I tried with 2-3 difff cast functions which is not working

Your guidance is appreciated
Thanks in advance.

[CoolP]

Hi,

Can you open the csv file in excel and apply the date format to this column and then use it as a source in FM?
I'm not sure of your circumstances though,whether you have that level of access to the source file.

Thanks
Prasan

[saumil287]

Hi,
I am having a QS containing date column which is date time showing in datatype,
but when I create a Regular dimension using the date column,it converts date time to character length 16.

Thanks

[CoolP]

Did you assign any of the roles (member caption, member description) to the Date Time column? If so, please leave it as an attribute and assign any other string attribute as member caption.

Also a screenshot will help us understand your situation in a better way.

Thanks
Prasan

[saumil287]

Hi coolp,
One is business_Key with date column in expression
and other is member_caption with date column and casting to varchar

Both are showing as character length 16 in data type
thanks

[CoolP]

Add the same date column as an attribute as well to the same level.(It's data type should be DateTime in FM).

Thanks
Prasan