Overview: Techniques for sorting data within SQL Server 2008 R2 Master Data Services (MDS). The examples below use the Chart of Accounts sample model included when you install MDS at: <drive>:\Program Files\Microsoft SQL Server\Master Data Services\Samples\Packages.

Sorting in Explicit Hierarchies

One of the benefits to using an Explicit Hierarchy is that the members can be sorted in whatever order you need. This is permitted because an Explicit Hierarchy is manually maintained. However, using the interface to do the sorting can be just a bit tricky.

In this example, we want to move the “Cash Equivalents-Book Value” to be displayed after Cash Clearing rather than before.

First, highlight the item to be moved and click the Ctrl key:

With the Ctrl key still down, drag the selected member on top of an existing member, and release. The relocated member will be placed just after the item you’re dropping it on top of. You’ll see the plus sign when you know it’s a valid drop location:

Now we have the members re-sorted:

A few additional thoughts:

If you relocate a member to a different parent, and you drop it onto the parent, it will immediately be placed last in the list of members underneath the parent. You can use the above technique to resort under the same parent, or relocate under a different parent.
If you utilize a subscription view to export this data to another system (such as a data warehouse), there is a field called “ChildSortOrder” exposed by the “Explicit Parent Child” view format. This is great, because then subscribing systems can also utilize the same sorting seen within MDS.
There’s also a clipboard method of moving members – this works well when moving members between parents. See this link for how to use the clipboard method. It’s very useful because you can use multi-select checkboxes.

Sorting in Derived Hierarchies

Because Derived Hierarchies are maintained by relationships in the data, sorting isn’t an easy thing to do. In a Derived Hierarchy, all members are sorted by the Code field. This behavior cannot be changed within the MDS interface.

There is a workaround – a very tedious one that I can’t recommend unless you have a very small dataset which doesn’t change often – if your MDS data is exported to another system through a subscription view. In the MDS model, you could create a free-form attribute to house Sort Order, and utilize it in the subscribing system.

Setting up the new field in System Administration:

Updating the field in Entity Explorer:

With quite a bit of data, using an attribute for sorting could get ugly really fast. However, it’s an option in a low-volume circumstance.

If you utilize a subscription view to export this data to another system (such as a data warehouse), you could utilize the sort order attribute you created in the downstream system. Your field would be exposed by the “Leaf Attributes” view format.

Finding More Information

MSDN: Derived Hierarchies (Master Data Services)

MSDN: Explicit Hierarchies (Master Data Services)

MSDN: How To: Move Members within a Hierarchy (Master Data Services)

MDS Training Guide: Managing Hierarchies