SSRS Report Custom Sort Order

Question

I am having some problem for a custom sorting required for one of row groups that I have in a SSRS table.

The logic for the custom sort order -

If the row group value contains a particular value then it should always be displayed at the bottom and all the other values have to be displayed in the ascending order.

For e.g. -

Suppose from the list of values A,E,G,D,C,and F, "D" should be always be displayed last and the other elements are to be sorted in asc order.

So, the above list should be sorted in the following order - A,B,C,E,F,G,D

Or if the list of elements is - P,J,M,N,D,C,K the required sort order is - C,J,K,M,N,P and D.

This logic has to be implemented for the row group data which gets displayed in the report.

Would appreciate if someone can help me out on this.

Thank you.

Answer 1

Try using the following expression in the Sorting setting.

=IIF(
Fields!YourField.Value="D","ZZZZ" & Fields!YourField.Value,
Fields!YourField.Value
)

This will sort your group if you don't have groups whose four first letters are ZZZZ.

Let me know if this helps.

Answer 2

I use an IIF (or multiple IIFs) to do custom sorts like this.

For your situation:

A,E,G,D,C,and F, D should be always be displayed last and the other elements are to be sorted in asc

I would first do a custom sort:

=IIF(Fields!MyFIeld.Value = "D", 2, 1)

This would sort the D first.

Then add second Sort that just uses the field (Myfield) to sort the rest by the field.

For the second situation:

if the list of elements is - P,J,M,N,D,C,K the required sort order is C,J,K,M,N,P and D

Then I would make a single custom sort with multiple IIFs:

=IIF(Fields!MyFIeld.Value = "C", 1, 
 IIF(Fields!MyFIeld.Value = "J", 2, 
 IIF(Fields!MyFIeld.Value = "K", 3, 
 IIF(Fields!MyFIeld.Value = "M", 4, 
 IIF(Fields!MyFIeld.Value = "N", 5, 
 IIF(Fields!MyFIeld.Value = "P", 6, 
 IIF(Fields!MyFIeld.Value = "D", 7, 8)))))))

-

Answer 3

I created a tablix with totals and I was able to sort by alphabetic order, total(ascending), total(descending). First I create a Dataset like this:

Select 'Name' as Order_Col, 1 as Order_Num
union 
Select 'Ascending' as Order_Col, 2 as Order_Num
union 
Select 'Descending' as Order_Col, 3 as Order_Num
order by 2

Then in the column group section, group properties I insert the following expression in the sorting options:

   =Switch(Parameters!SortOrder.Value = 1,Fields!Name.Value
    ,Parameters!SortOrder.Value = 3,(Fields!TtlRef.Value)*-1
    ,Parameters!SortOrder.Value = 2,Fields!TtlRef.Value)

Create a Parameter named SortOrder where the Value is Order_Num and Label is Order_Col. You can make a default using a value of 1.