Reporting Services - Custom Sort Orders in Tables

SSRS allows you by default to sort on a value  either by A to Z or Z to A, which is fine for most scenarios however what happens if you want to sort in a specific way.

Example, you have 3 values and you want them sorted as per below:

Low

Medium
High

A to Z would give you

High

Low
Medium

Z to A would be a reverse so neither of these would achieve the required sort order.

The answer is so add a IFF Statement to the Sort Expression and select A to Z as the Order, so for example

=iif(value=”Low”,”1”, iif(value=”Medium”,”2”, iif(value=”High”,”3”,””)))

 

The order is then sorted on the numeric value as opposed to the Alphabetical value, thus giving you your desired sort order J