Implement Custom Sort Order in SSRS

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.

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.

Advertisements

9 thoughts on “Implement Custom Sort Order in SSRS

    • Hi Judy,

      Could you please elaborate? Are you trying to use the wild card match on the case statement or if you use % in the value your code is not working?

  1. I can’t get it to work when using case like “SECTOR%”. Any ideas on how to fix the code to do this?

    I tried something like below : but giving error
    Select Case Branch
    Case not like ‘%AGRI %’
    Return 1

    • Hi Koti,
      It looks like you would not be able to use wildcard match. If you do any work around let us know it will be a good learning for everyone..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s