Implement Custom Sort Order in SSRS

Yogi / June 27, 2012

Let us see how do we implement the custom sort order in table using custom code in SSRS.

Following image contains the report data.  It contains the sales details based on city and you can see that the data is not ordered in any fashion.

If you want to order the data in ascending or descending order then we can achieve this directly by adding the sort order in the dataset query. There are other ways in which we can achieve this and this is very straight forward.

If the customer needs the information sorted in some customized order then how do we achieve this? Let us say following is the sort order provided by the customer and they wanted to see the data in this order.

1. Chennai
2. Bangalore
3. Hyderabad
4. New Delhi
5. Mumbai

There might be multiple ways we can achive this, Let us do this with the help of Visual Basic function.

Following is the function which order the data in the customer required format.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

Public Function CustomSortOrder(ByVal City As String) as Integer      Select Case City         Case = "Chennai"            Return 1         Case = "Bangalore"            Return 2         Case = "Hyderabad"            Return 3         Case = "New Delhi"            Return 4         Case = "Mumbai"            Return 5      End Select End Function

Let us see how do we use this function and sort the data in the customized order.

Place the source code in Code section, Where do you find Code section? Go to Report menu and Select Report Properties.  Please refer to the image below

Now you have the function and it is ready to use, Let us call the function sort the data.

1. Click on the column City in the reports.
2. Right Click on the Row Groups Column
3. Select Group Properties, Refer to the image below

4. Under Sorting section Add a Sort by column
5. Click on the expression fx and it opens the expression window
6. Enter the expression as “=Code.CustomSortOrder(Fields!City.Value)” (Without Quotes) refer to the image below

7. Click on Ok
8. Render the report and the data is sorted based on the custom sort order, Refer to the image below

In similar fashion we can use the same function to sort the data in Charts as well.. Let me know if there is any other way of achieving this functionality by leaving a comment.