Sorting order in DMR

[johny.cbi]

I encountered some issues with DMR Model
there is one time dimesnion Year/Month, it was showing the hirerachy level in wrong direction
eg: 2009/Dec, 2009/Mar, 2009/Feb etc....
If  I apply sorting order it displays in alphabetical order April, August, Feb etc...
is there any option to change settings at package level (DMR)
I want the Month to display in Jan, Feb, March, Apr etc....

Environement : Cognos 8.4.1 DB: SQL server
Thanks for your ideas

[MFGF]

Hi,

Go into the properties of the Time Regular Dimension in Framework Manager, onto the 'Members Sort' tab, and enable sorting for both Metadata and Data.  Select the 'Always (OLAP Compatible)' option, then either manually define the sort item for each level, or use the 'Detect' link at the top and modify as appropriate.  For the month level, you will need to sort on whichever attribute contains the year and month number (not the month name).

Republish your package once you have done this, and it should fix the problem.

Regards,

MF.

[IceTea]

Select the 'Always (OLAP Compatible)' option...

Why not chose the "Only as Default Report Sort" option? I don't know where's the real difference and the benefit (advantages/disadvantages) of the two options. Can you help me?

[johny.cbi]

I have the month name(attribute) in the package. what is the solution for this ?

[MFGF]

I have the month name(attribute) in the package. what is the solution for this ?

What are you using as the businesskey for the Month level?

If Month Name is the only attribute you have at the Month level, add another attribute containing the year and month number as an integer value, and sort on this.

MF.

[johny.cbi]

sorry... I have the Year/Month attribute but it displays as 2008/dec, 2008/Jan etc.....how to work on this

Thanks for your ideas

[MFGF]

If you trace this attribute back to where it was read in to your model as an item in a query subject, is it still in the same form, or has it been derived from a date column somewhere in FM?

If it is being physically read in from the database in this form, you could either:

a) look whether there are other month-related columns in the same table that might contain the year and month as a number, or year and month number columns separately, and add these to your model and bring through to your Regular dimension as a new attribute.
b) use a calculation to change your yyyy/mmm item into a yyyymm integer item, and bring this through as an attribute of your Month level in the Regular Dimension

To get an integer year you could code

cast(substring([your month item],1,4),integer)

To get an integer month, you could code
if (substring([your month item],6,3) = 'Jan') then (1) else if (substring([your month item],6,3) = 'Feb') then (2) else if ...   ...else if (substring([your month item],6,3) = 'Dec') then (12) else (0)

To get an integer year-month value to sort on, you could combine the above two as follows:

[your integer year value] * 100 + [your integer month value]

Regards,

MF.