Create a Custom Sort Order in SSRS and Paginated Reports

There are times when alphabetical or numerical sorting just will not cut it. Whether you are designing dashboards for executive review or producing print-ready Paginated Reports in SSRS, sometimes the way data is sorted can completely change how a report is interpreted. This is where custom sorting comes in; a simple but powerful technique that gives you full control over the presentation of your grouped data.

Why Use Custom Sort Orders?

Out of the box, SSRS and Paginated Reports allow you to sort groups in ascending or descending order. But what if your business logic calls for something different? For example, your stakeholders may expect to see product categories in a particular operational sequence, or you might need to highlight high-priority regions at the top of a matrix regardless of alphabetical order.

While you can often handle custom sorting in SQL (such as through a CASE statement or joining on a lookup table) that is not always an option. If you are working with MDX queries against an Analysis Services cube, for instance, or your dataset is already locked down, then applying the sort at the report level is often the simplest and most effective solution.

Example: Custom City Order

Suppose you have a column group named City with values: Adelaide, Brisbane, and Cairns. By default, SSRS will sort them alphabetically. But let’s say your reporting audience wants the order to be:

1. Adelaide
2. Cairns
3. Brisbane

Here is how to implement that custom sort directly in your report:

Step-by-step Instructions

1. Right-click the column group (or row group) in your Matrix or Tablix.
2. Select Group Properties.
3. Click the Sorting tab.
4. Click the Expression (Fx) button and enter the following:

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

5. Click OK to confirm and ensure your group sort is set to A to Z.

SSRS will now sort based on the numeric values you assigned in the expression, resulting in the cities appearing in your custom sequence rather than alphabetical order.

Final Thoughts

Custom sort orders in SSRS and Paginated Reports are a subtle but impactful way to improve report usability and align output with business expectations. Whether you are working with cubes, stored procedures, or flat datasets, sorting at the report layer can save time, reduce complexity, and increase flexibility.