SSRS [Sort Alphanumerically]: How to sort a specific column in a report to be [A-Z] & [ASC]

Question

I have a field set that contains bill numbers and I want to sort them first alphabetically then numerically.

For instance I have a column "Bills" that has the following sequence of bills.

- HB200
- SB60
- HB67

Desired outcome is below

- HB67
- HB200
- SB60

How can I use sorting in SSRS Group Properties to have the field sort from [A-Z] & [1 - 1000....]

Answer 1

In the sort expression for your tablix/table which is displaying the dataset, set the sort to something like:

=IIF(Fields!Bills.Value = "HB67", 1,  IIF(Fields!Bills.Value = "HB200", 2,  IIF(Fields!Bills.Value = "SB600", 3, 4)))

Then when you sort A-Z, it'll sort by the number given to it in the sort expression.

This is only a solution if you don't have hundreds of values, as this can become quite tedious to create if there's hundreds of possible conditions.

Answer 2

This should be doable by adding just 2 separate Sort options in the group properties. To test this, I created a simple dataset using your examples.

CREATE TABLE #temp (Bills VARCHAR(20))

INSERT INTO #temp(Bills)
VALUES ('HB200'),('SB60'),('HB67')

SELECT * FROM #temp

Next, I added a matrix with a single row and a single column for my Bills field with a row group.

In the group properties, my sorting options are set up like this:

So to get this working, my theory was that you needed to isolate the numeric characters from the non-numeric characters and use each in their own sort option. To do this, I used the relatively unknown Regex Replace function in SSRS.

This expression gets only the non-numeric characters and is used in the top sorting option:

=System.Text.RegularExpressions.Regex.Replace(Fields!Bills.Value, "[0-9]", "")

While this expression isolates the numeric characters:

=System.Text.RegularExpressions.Regex.Replace(Fields!Bills.Value, "[^0-9]", "")

With these sorting options, my results match what you expect to happen.