Tuesday, November 18, 2014

SSAS Sorting Attribute Member by Secondary Attribute


So you have an attribute like Month that contains values of "January", "February", etc.  By default this attribute will sort by text order, but this can be changed to sort off another attribute (i.e. Month Value).

After both the Month and MonthValue attributes have been added to your dimension, you can disable the MonthValue (if desired) by:

  • Under the "Dimension Structure" tab and in the "Attributes" pane, select the "MonthValue" attribute and press F4 to get to the properties window
  • Set AttributeHierarchyEnabled to False
  • Set AttributeHierarchyOptimizedState to NotOptimized
  • Set AttributeHierarchyOrdered to False
Next, select go to the "Attribute Relationships" tab and create a relationship between Month and MonthValue by:
  • Right-click the "Month" attribute and select "New Attribute Relationship"
  • Leave the "Source Attribute" to Month, but change the "Related Attribute Name" to MonthValue
Finally, go back to the "Dimension Structure" tab and select the Month attribute.  In the Properties window, set the following:
  • OrderBy to "AttributeKey"
  • OrderByAttribute to "MonthValue" (or whatever attribute relationship you set)
Process and enjoy!

Thanks to MS Documentation!

No comments:

Post a Comment

Please only include comments that add to the substance of the Blog Post:
- Question or request for clarification
- Idea for improvement
- Identifying an issue

Please refrain from promotion.