I ran into a problem the other day where the requirement was to add several interactive sort options to a report. The report was a bit large and took more than just a second or two to run. Therefore each click of an interactive sort button took some time to run as well, decreasing the quality of the user experience. Then came the “can we also” request of making one of the columns sort descending on the first click of the interactive sort button. The report took long enough to run with each click that the users did not want to have to click twice in order to get to the sort they wanted.
SSRS Interactive default sort descending for integer.
I spent some time on Google and couldn’t find any way to change the default first click of the interactive button to descending. This is the sort of thing that I find frustrating, so I started thinking, and I continued digging on Google. Eventually I came across a forum where the answer simply said multiply the sort value expression by a negative 1.
=Fields!NumericFieldToSortBy.Value * -1
I thought, brilliant, if you can’t change the default sort in the software, change the values you want to sort so that they do what you want the first time out. OK, I got lucky. The column the user wanted to default to a descending sort was a numeric, so I had my answer. But, I thought there has to be a way to do this with dates, and strings as well.
SSRS Interactive default sort descending for date.
It didn’t take me too long with this line of thinking to come up with a solution for a date sort. Initially I thought that I would convert the date to a format of YYYYMMDD and multiply it by a negative 1 again and I would be done. It definitely worked, but it seemed a little awkward, so I kept at it. Eventually it struck me that DATEDIFF would give me an integer I could use as my sort expression.
It is a simple expression, and it works reliably. Granted, I am not including time values here. If you need to pursue sorting time values as well, you can follow the same trail with DATEDIFF but you will end up with a substantially smaller range of values that you will be able to sort.
SSRS Interactive default sort descending for text.
So, numeric and date sorts were out of the way. What about strings. That is a complicated problem, and unfortunately I don’t think I came up with a great answer. My solution will only work with the standard 26 character A-Z alphabet , and it does not include case sensitivity or accents. So if you have an NCHAR or NVARCHAR column with things such as ñ or à or ü or any of these types of characters you will get unexpected sorts in your results. My solution is to convert the first 5 characters of the text (few words have sort issues past the first 5 characters) to ASCII codes and then pull out the trusty negative 1 again and sort the resulting numbers. My first draft of the expression was something like this:
=CLNG(CSTR(ASC(UCASE(CSTR(Fields!LastName.Value)))) + CSTR(ASC(UCASE(MID(CSTR(Fields!LastName.Value),2,1)))) + CSTR(ASC(UCASE(MID(CSTR(Fields!LastName.Value),3,1)))) + CSTR(ASC(UCASE(MID(CSTR(Fields!LastName.Value),4,1)))) + CSTR(ASC(UCASE(MID(CSTR(Fields!LastName.Value),5,1)))) ) * -1
I converted everything to upper case for two reasons: Upper case letters are all 2 digit ASCII codes, and having one block of 2 digit codes would give more reliable sorts. I also had to convert the ASCII values to strings in order to concatenate the values together instead of adding them together. Finally I converted the result to a long and multiplied by -1.
But this had two problems. The first is that numbers don’t sort quite the same as letters. The second problem occurred when the data was fewer than 5 characters long I got #Error as a result. Fortunately both problems had the same solution: right fill the values with a lower value ASCII character. The @ character immediately precedes the A, so I ended up with this:
=CLNG(CSTR(ASC(UCASE(LEFT(CSTR(Fields!LastName.Value) + "@",1)))) + CSTR(ASC(UCASE(MID(LEFT(CSTR(Fields!LastName.Value) + "@@",2),2,1)))) + CSTR(ASC(UCASE(MID(LEFT(CSTR(Fields!LastName.Value) + "@@@",3),3,1)))) + CSTR(ASC(UCASE(MID(LEFT(CSTR(Fields!LastName.Value) + "@@@@",4),4,1)))) + CSTR(ASC(UCASE(MID(LEFT(CSTR(Fields!LastName.Value) + "@@@@@",5),5,1)))) ) * -1
Within my defined parameters, this expression sorts quite well.
I did find an SQL Function that would do a fair job converting characters from the Extended ASCII character set to the lower, original ASCII character set, but it was so costly in terms of performance that I chose not to pursue it. To give you an idea of the cost, in SSMS a query that returned roughly 34,000 rows went from 3 seconds to over 11 minutes. It was fine for maybe 100 rows, but much after that and it bogged down horribly.
I am sure some of you really sharp VB.NET of C# code folks out there can come up with a function we can embed into our reports to add extended character set sort functionality. I hope you read this, consider it a challenge and post your results back here.
In the meantime, I hope you find this tip helpful. Thanks for reading.