Order of regular dimension elements in Analysis Studio

[ykud]

An obvious thing, still I can't find answer anywhere:

How can I change regular dimension elements order for Analysis Studio?

I have a time dimension in dwh table, set up a regular dimension on it and get a real mess in Analysis studio (2004 before 2002 and etc). I don't want to set up order by applying "sort by" in every Analysis report, I'm seeking a way to set up order so that in every report it'd appear ordered.

Any ideas? What am I missing?

[praveennb]

I have also encountered similar problem, what i did was on the FM model for the year key i  have 2 fields
one is year character field  another is integer field, both fields will have year data

for the year character field i have set the property to _membercaption
for the year integer field i have set the property to _businesskey

this seem to fix the problem.

[ykud]

Thank you very much BI007.

It really does help, but not everywhere )
Sorting  works ok on business keys for all dimension levels, except bottom one.
I have no idea why...

[praveennb]

Thank you very much BI007.

It really does help, but not everywhere )
Sorting  works ok on business keys for all dimension levels, except bottom one.
I have no idea why...

yes the lowest level for some odd reason doesn't get sorted, if you happen to use SQL Server you could create a Cluster index
on the lowest level field then the dimensions are getting sorted, let me know if u happen to find a solution.

what i noticed was very strange behavior in Analysis Studio
if you were to publish a table named yeartime with just one column year character data type
with 7 years value in it, from 2000 to 2007
then the dimensions get sorted, but for testing purpose i added duplicate years value(like 100s of year values  2002,2006,2004,2003,2001,2007)  into the yeartime table the sorting failed!!

[ykud]

yes the lowest level for some odd reason doesn't get sorted, if you happen to use SQL Server you could create a Cluster index
on the lowest level field then the dimensions are getting sorted, let me know if u happen to find a solution.

We've got sorting on lowest level by adding "order by" to dimension query and switching it to Pass-Through SQL.
And lost a lot of speed, since cognos doesn't apply filters to pass-through.