SSRS: Sorting in Particular Order in a Report

This week I had the need to order values in a matrix in SSRS in a particular order. The names need to be ordered in alphabetical order, except for one name that always needs to come first. I’ll show you how I did it with a sorting expression in the Group.
I’ve taken a (light-weight) version of AdventureWorks for the examples.

Let’s take the Sales.ProductCategory and Sales.Product tables as an example. I’ve counted the number of Products of the 4 main (parentless) ProductCategories:


The sorting in the Row Group Name is automatically done from A-Z. If you want it in any other order you’ll have to set it explicitly. You can either pick a(ny) column and set it to sort A-Z/Z-A or you can enter an expression. We’re now going to implement the latter.
When you click on the arrow of the Row Group Name in Row Groups box at the bottom of the screen and click on Group Properties… you’ll end up with the following screen:


We want to leave the existing sorting expression as it is, because we can use it later. Add another row and click on the fx button of the new row to open up the expression dialog box:


By entering the formula you see in the picture above, you’re saying to the sorting engine: Whenever the value of the field Name is equal to “Clothing”, give it a sort order of 0, otherwise give it a sort order of 1. When ordering from A-Z 0 comes before 1 so “Clothing” always comes before the rest. You can adjust this to your needs ofcourse to make a specific item come last, or sort all the items in particular order by nesting IIF statements.
When you click OK and click on the up arrow to sort on this expression first you end up with the following sorting options:


Sorting on the Group is first done on the expression, so “Clothing” comes before anything else, second alpabetically on the Name, so the rest of the values are placed after ”Clothing” from A-Z. This gives me the same values as before, but now sorted differently:

Manual sorting in PowerPivot

When you have your own row labels and would like to sort them in your own order you can create a set and have them always in that order, also after refreshing the data.

This is the starting point, we have (string) labels of the length of an employment. PowerPivot sorts them in this order, from 0 - 1 - 2 to 5.

What we would like to have is this:

This can be achieved by creating a Set for the Row Labels. You can find the option on the (PivotTable Tools) Options ribbon, under "Fields, Items & Sets".

After selecting "Create Set Based on Row Items..." you can sort the items manually and give the set a relevant name.

After creating the set it also appears in the PowerPivot Field List under Sets: