Microsoft KB Archive/245245

= INF: Sorting Dimension Members in OLAP Services =

Article ID: 245245

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q245245



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: <pre class="fixed_text">  MonthNameAndNumber 01Jan 1998 02Feb 1998 .  .   12Dec1998 13Jan1999 </li> 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.</li>  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). </li></ol>

Keywords: kbinfo KB245245

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.