INF: Sorting Dimension Members in OLAP Services (245245)

MORE INFORMATION

Consider the following example. If you have "Month" as a level in a time dimension and the month column in the fact table contains month names, then months is sorted and displayed based on alphabetical order. So, you would see months displayed in the order April, August, and so on rather than in the chronological order January, February, and so forth.

There are at least two methods you can use to work around this default behavior.

Method 1

1. Create another column in the time dimension table that has the sequence number for each month. So, the dimension table for "Time" dimension will have these two columns:

      MonthName	MonthNumber
      Jan 1998	01
      Feb 1998	02
      .
      .
      Dec 1998	12
      Jan 1998	13
      .
      .
   					

2. In time dimension, base the month level on the MonthNumber column. This causes both the "Member Name Column" and "Member Key Column" properties of the month level to be based on MonthNumber column.
3. Using the dimension editor, edit the "Member Name Column" property (under the Advanced Tab) so that it is based on the MonthName column.
4. Set the Order by Key property (under the Advanced Tab) to True. This orders members based on the member key column. The preceding steps display the month names to display in chronological order.

There is yet another method which may be useful in some situations. The advantage of this second method is that there is no need to create another column (MonthNumber).

Method 2

1. Change the Month column by appending the month sequence number before the months in month column as follows:

      MonthNameAndNumber
      01Jan 1998
      02Feb 1998
      .
      .
      12Dec1998
      13Jan1999
   					

2. In time dimension, base the month level on this column. Both the "Member Name Column" and "Member Key Column" properties will now be based on this column.
3. Edit the "Member Name Column" property in the dimension editor to display only the month and year. For example, if the dimension table is in SQL Server 7.0, then you can use the following Transact-SQL (TSQL) right function.

   right("Time"."Month",8)
   						

   This code removes the first 2 digits from display and displays only the month and year (such as Jan 1998).