INF: Sorting Dimension Members in OLAP Services

ID: Q245245

The information in this article applies to:

Microsoft SQL Server OLAP Services version 7.0

SUMMARY

When you create a dimension using the wizard in OLAP Manager, the column you select for a level is assigned to both the "Member Name Column" and "Member Key Column" properties of the level. OLAP Services, by default, orders members in a dimension level alphabetically based on the "Member Name Column" property of the level. However, there are times when you many not want this default behavior.

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

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
   .
   .

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.

Using the dimension editor, edit the "Member Name Column" property (under the Advanced Tab) so that it is based on the MonthName column.

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

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

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.

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).

Additional query words:

Keywords : kbSQLServ kbOLAP700
Version : winnt:7.0
Platform : winnt
Issue type : kbinfo