SSRS not sorting details as expected

Question

I have a report with a tablix that is grouped on a supplier group. I have a details group that contains a Sequence, Block and Product number. I need the report to sort based on the Sequence column (first column in the details group). My problem is that even with the sort applied at the dataset, and details group level I am getting a sort that goes 1,4,5,6,7,8,9,10,11,2,3 etc. I have 32 sequences. I would understand if the sort went 1,10,11 etc but it is jumping over 2 and 3. The field is defined as an integer so I can't figure it out. When I look at this in the query in the dataset the sorting is correct. I am at a loss. I have tried applying the sort at every level within the report and also not applying the sort at all. Anyone have any ideas?

Answer 1

Can you share which version of SSRS and also include an image of the report output, tablix, and row groups?

I would recommend you remove the ORDER BY from your SQL in the data set. In my experience, SSRS can sort this more efficiently in your tablix than SQL Server.

Also, I prefer to focus any sorting at the closest level of user visibility (I only think of sorting as useful for users). Therefore, I recommend you apply the sorting to the row group. Also, if the sequences you are using, they appear to be integers, ever convert to text, make sure you convert the field back to a number in your sort expression. I suggest you convert this to an integer even if you are certain that it is already an integer, at least for testing.