SSRS Custom Sort Order

Question

I have a simple question. In SSRS, I have a stored procedure feeding me total units for our Product Classes. I received an interesting request for new sorting. The request was to sort in desc order units, and then force a particular Product Class to always appear at the bottom, regardless of their rank in the desc units aggregate. I can easily write an expression to make that class appear last, so long as I'm only sorting on the Product Class name. Does SSRS allow for this option? In the example below, the Other class would actually be the second class listed if I was only using the class as a sort field. But the request is to always have it at the bottom. Any ideas?

Sort Example

Thank you all in advance!

Answer 1

What I would do is use an expression for the sorting of that tablix (or group if these are row groups) to something that sets the Total Units of Other to -1 for the purposes of the sort. If the normal categories can can have values lower than 0, then set it to some large negative number that no category could ever go below, if possible. This will not change what is displayed next to Other in the Total Units column, but it will change the sorting so that Other is always at the bottom.

It should not matter if you are displaying this in a tablix, or with row groups, or in a chart. Wherever you are setting the Sort by column, instead of using a column, use an expression like this:

=IIf(Fields!ProductClass.Value = "Other", -1, Fields!TotalUnits.Value)

And sort it Z to A. If this will go in a tablix or a chart with any kind of grouping though you may have to modify that slightly to account for the aggregate.

An alternative could be to have 2 layers to your sort: the first layer being an expression such as =IIf(Fields!ProductClass.Value = "Other", 0, 1), Z to A, and then the second layer simply sorting on TotalUnits, Z to A. This would first make sure Other gets placed at the bottom, while the rest of the categories are above it, and then it would sort the rest of the categories. I'm not sure if there would be a noticeable performance difference between these two methods or not, but maybe one might make more sense than the other or be easier to implement for whatever reason.