SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Dynamic Sorting in SSRS


Many times while developing any report we come across a situation where we need to SORT the TABLE columns or the columns in the GROUP dynamically. There are many well known ways to achieve this. However, here I will demonstrate a not-so-known way -



Assuming that the sorting is to be done on 3 columns – Col1,Col2 & Col3. But the ORDER is undecided until runtime. Now, what we can do is -

  1. Define 3 Report parameters. Say - @SortCol1Name,@SortCol2Name & @SortCol3Name of STRING type.
  2. Pass the name of the columns which need to sorted in the required order. Say -
    • @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”
    • OR @SortCol1Name = “ColumnY”,@SortCol2Name = “ColumnZ” and @SortCol3Name = “ColumnX”
    • OR any preferred order
  3. In the Sorting Option of the Properties dialog box of Table or Group, set the sort expression as under -
    • =Fields(Parameters!SortCol1Name.Value).Value
    • =Fields(Parameters!SortCol2Name.Value).Value
    • =Fields(Parameters!SortCol3Name.Value).Value


This finally gets converted to –Fields!ColumnX.Value,Fields!ColumnY.Value & Fields!ColumnZ.Value if we have the passed the following values for the parameters - @SortCol1Name = “ColumnX”,@SortCol2Name = “ColumnY” and @SortCol3Name = “ColumnZ”


With this trick, we can easily set the sort expression dynamically. However, there is a limitation that we can not set the Sort Direction dynamically. I could not find out a way for it. If you have any trick to get it done with this, please leave your suggestion as a comment to this post.


Posted by bphipps-931675 on 30 December 2011



sortCol INT

,Descrip varchar(10)


INSERT INTO #test VALUES ( 0,' row 0')

INSERT INTO #test VALUES ( 1,' row 1')

INSERT INTO #test VALUES (2,' row 2')

INSERT INTO #test VALUES (3,' row 3')

INSERT INTO #test VALUES (4,' row 4')

INSERT INTO #test VALUES ( 5,' row 5')

INSERT INTO #test VALUES ( 6,' row 6')

INSERT INTO #test VALUES ( 7,' row 7')

DECLARE @sortOrder int

SET @sortOrder = 1  --asc

SET @sortOrder = 2  --desc

SELECT * FROM #test AS t

ORDER BY CASE WHEN @sortOrder = 1 THEN sortCol END ASC

,CASE WHEN @sortOrder = 2 THEN sortCol END DESC


Posted by JasonBudd on 30 December 2011

The following would work, though not very efficient.

For each data column that you want to allow sorting on, add two Row_Number() columns in the query.

Row_Number() Over(Order By SortCol1 asc) as SortCol1Asc


Row_Number() Over(Order By SortCol1 desc) as SortCol1Desc

Add a direction parameter for each sorting parameter with Asc and Desc as the options.  Then concatenate the direction parameter selection to the sort column chosen:

=Fields(Parameters!SortCol1Name.Value & Parameters!SortCol1Direction.Value).Value

Or you could approach it as bphipps-931675 suggested above and have two CASE options per field you allow sorting on.  In each of your SortColumn parameters have two options for each field, ex: field1 asc and field2 desc.

Posted by vinaypugalia on 30 December 2011

@ bphipps-931675 and JasonBudd, thanks for your suggestions and both of them are workable when we have a control over the Data Source and have the liberty to play with it.

Assume, that we do not have any control over the Data Source and want to sort dynamically. How would we tackle the Sort Direction in this case ?

Posted by sqlcentral2 on 26 November 2012

The only way I can think of of getting the sort direction done purely on the report is to have an ascending and descenting sort pairs specified for each dynamic sort column and use a sort direction parameter to ignore one of the sorts for each pair.

So there'd be 6 sorts defined for the example in the blog post

You'd have a @sortType param for each sort column and your expression would be wrapped in in iif().


for the ascending sort you'd have this in the expression

=iif(Parameters!SortTypeCol1.Value = 'ascending',Fields(Parameters!SortCol1Name.Value).Value,Nothing)

for the descending sort type you'd have

=iif(Parameters!SortTypeCol1.Value = 'descending',Fields(Parameters!SortCol1Name.Value).Value,Nothing)

disclaimer: I don't have ssrs in front of me at the moment so I can't test that this'll actually work. Maybe Nothing won't be allowed as a sort type. If not then use constant like 1 true or something

Posted by vinaypugalia on 26 November 2012

Hey, that seems to be a cool trick and I think that will work for sure.

Thanks for sharing your idea!

Posted by nicklarge1973 on 19 May 2017

The following worked for me ...

Create the dropdown parameter in the report to contain the field names in the "value" column, then, in your tablix detail sort expression use the following ...

=IIF(Parameters!SortItem1.Value <> "NONE", Fields(Parameters!SortItem1.Value).Value, "")

NB. I set "NONE" as the value of the parameter dropdown that is where the user does not want to sort.

I hope this helps.  BTW, The solution did utilize this thread so, thanks for the post!


Posted by nicklarge1973 on 19 May 2017

Actually, this was the final expression that worked ...

=IIF(UCASE(CSTR(Parameters!SortItem1.Value)) <> "NONE", Fields(Parameters!SortItem1.Value).Value, "")

Posted by nicklarge1973 on 19 May 2017

OK, so there was issues with testing on the last post.  The following is the fully tested expression, however, you should replace the text {default_sort_field} with your default sort field for that column ...

=Fields(IIF( UCASE(CSTR(Parameters!SortItem1.Value)) = "NONE", "{default_sort_field}", CStr(Parameters!SortItem1.Value))).Value

so, for example, if none of your field list is selected, how should it sort ?  In this example EMPLOYEE_ID is the default sort field ...

=Fields(IIF( UCASE(CSTR(Parameters!SortItem1.Value)) = "NONE", "EMPLOYEE_DATE", CStr(Parameters!SortItem1.Value))).Value


Leave a Comment

Please register or log in to leave a comment.