SSRS Report Custom Sort Order

ssrs sorting not working
ssrs default sort order
ssrs sort column in matrix
ssrs sort by group total
ssrs interactive sort not working
ssrs switch
ssrs sort parameter values
ssrs iif

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.

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.

SSRS Report Custom Sort Order, Yes you can handle custom sorting via T-SQL but there are going to be cases where it's simpler to perform the sorting at the report: such as  Create a custom sort order in SSRS. If you have a column (or row) group called City with values of Adelaide, Brisbane or Cairns, you can sort A-Z, Z-A or perform a custom sort.

Create SSRS custom sort order – Mike250, To change the sort order of data in a data region when a report first runs, you must set the sort expression on the data region or group. The Sorting In SSRS is similar to the SQL ORDER BY Clause. Sorting is used to Sort the records as per the given Expression in Tablix properties and then Sorted data will display in the final SSRS Report. In this article, we will show you how to sort the data at the Table level (or tablix level) in SSRS with an example.

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.

Sort Data in a Data Region (Report Builder and SSRS), You can also add an interactive sort button to a table or matrix cell to enable a user to interactively change the row sort order for groups or rows  Custom Sorting in SSRS Reports. To sort rows in a dataset you can choose a column in the dataset in the properties of the tablix or other report element. Or if you need a custom sort where the sorting isn't alphabetical or numerical you can add code to the report and then call the code in an expression in the sorting property of the tablix.

Filter, Group, and Sort Data (Report Builder and SSRS), Let's say you want the order in your matrix to be Atlanta, Charlotte, Birmingham. To perform this custom sort, right click the Column Group (or Row Group) SSRS will sort on the numbers you inputted rather than the city names. Return a message such as "No Data" if your Report does not Return Results. Implement Custom Sort Order in SSRS 1. Click on the column City in the reports. 2. Right Click on the Row Groups Column. 3. Select Group Properties , Refer to the image below. 4. Under Sorting section Add a Sort by column. 5. Click on the expression fx and it opens the expression window. 6.

Create a Custom Sort Order for a Column or Row Group, Let us see how do we implement the custom sort order in table using custom code in SSRS. Following image contains the report data. If you notice from the screenshot, first one is A to Z (Asc) and the second one is Z to A (Desc). So we are basically telling SSRS to sort by the param or not based on the order by option and it chooses the right order by (ASC/DESC). I think this was easier in SQL 2000 SSRS 🙂 Well now you should be able to test your report and try to sort orders.

Implement Custom Sort Order in SSRS, Custom SSRS Alphabetical Sort Expression. Hi there, I have a SSRS report expression that uses =Switch to perform a sort of specific your question, I can do something like order by that custom sort and then by the column. For more information, see Sort Data in a Data Region (Report Builder and SSRS) and Lookup Function (Report Builder and SSRS). Adding Interactive Sorting for the User To enable a user to change the sort order of report data in a table or matrix, you can add interactive sort buttons to column headers or group headers.

Comments
  • hi alejandro, it worked. Actually I had renamed the field with something else and was sorting with some other field. Thanks.
  • I fixed my first expression, I missed where D was last, not first.
  • Hi, the problem is that the MyField values get dynamically populated. In order to explain the scenario I have used hard coded values.
  • After you sort with =IIF(Fields!MyFIeld.Value = "D", 2, 1) , could you please explain what would be your second sort logic?
  • @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.
  • 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!