Sometimes we need to display Date dimension members in descending order. For example, Client ask to show Calendar Year in DESC order.
But Analysis Services doesn't provide a way to sort dimension members in Descending order. SSAS provides only Ascending order.
However, we can achieve this sort functionality by using a simple trick. I will take an example to display Calendar Year member in DESC order. Below are the steps to achieve our goal:
Step1: Go to Data Source View (DSV) and right click on Date dimension in DSV diagram. Click on New Named Calculation... to create a new member.
Step2: Now enter CalendarYearSort in Column name and 0-CalendarYear in Expression textbox. This additional column will be used to sort calendar year in descending order. Click OK to proceed. Here I considered that CalendarYear is existing column in the dimension.
Step3: Go to Dimension Structure of Date dimension and drag and drop new member "CalendarYearSort" in attributes list.
Step4: Click on Attributes Relationship tab. Right click on Calendar Year member and select New Attribute Relationship... It will open Create Attribute Relationship wizard.
Step5: In Create Attribute Relationship, select Related Attribute as Calendar Year Sort and Relationship type as Rigid. Click OK to proceed.
Step6: Again go back to Dimension Structure tab and click on Calendar Year attribute. Go to properties and set following things as shown below:
OrderBy: AttributeKey and OrderByAttribute: CalendarYearSort
Step7: Now Calendar Year is set in decending order. Just save and process the cube.
Note: Before processing the cube, you can set AttributeHierarchyVisible = FALSE because we should not show this attribute in cube and AttributeHierarchyEnabled = False to reduce cube space and increase the performance.
