Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Dynamic Sorting in SSRS

Introduction

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 -

 

Implementation

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

1

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”

Conclusion

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.

Comments

Posted by bphipps-931675 on 30 December 2011

CREATE TABLE #test

(

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

DROP TABLE #test

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

--And--

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().

ie

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!

Leave a Comment

Please register or log in to leave a comment.