Create SSRS custom sort order

If you have a column (or row) group called City with values of Adelaide, Brisbane or Cairns, you can sort A-Z, Z-A or perform a custom sort. Yes you can handle custom sorting via T-SQL but there are going to be cases where it’s simpler to perform the sorting at the report: such as working with MDX against the cube which doesn’t have a dimension for sorting.

Let’s say you want the order in your matrix to be Adelaide, Cairns, Brisbane. To perform this custom sort, right click the column group (or row group) and select Group Properties. Click the Sorting tab and then the Fx button. Use the following expression:

=IIF(Fields!City.Value="Adelaide","1", 
IIF(Fields!City.Value="Cairns","2", 
IIF(Fields!City.Value="Brisbane","3","")))

Click OK when finished and simply sort A-Z. SSRS will sort on the numbers you input rather than the city names. Your matrix/tablix will place Adelaide first followed by Cairns followed by Brisbane.