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.

Solution 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.

Solution 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)))))))

-

Solution 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.

Comments

[Hannover Fist]

I fixed my first expression, I missed where D was last, not first.

[Aritra B]

Hi, the problem is that the MyField values get dynamically populated. In order to explain the scenario I have used hard coded values.

[Aritra B]

After you sort with =IIF(Fields!MyFIeld.Value = "D", 2, 1) , could you please explain what would be your second sort logic?

[Aritra B]

hi alejandro, it worked. Actually I had renamed the field with something else and was sorting with some other field. Thanks.

[Hannover Fist]

@AritraB - for the second part "other elements are to be sorted in asc", I would have a second SORT by the field (Fields!MyFIeld.Value) in ASC order.

[Aritra B]

Thank you for taking your time out and looking into this. Appreciate your help. I have already fixed this, would also give your solution a try!

[Tscott]

I believe if you have a results like the example (letters), this solution works great. It helped fix the issue I was having.