THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Stacia Misner

Applying Interactive Sorting to Multiple Columns in Reporting Services

A nice feature that appeared first in SQL Server 2005 (not SQL Server 2008 - I mis-remembered!) is the ability to allow the user to click a column header to sort that column. It defaults to an ascending sort first, but you can click the column again to switch to a descending sort. You can learn more about interactive sorts in general at the Adding Interactive Sort to a Data Region in Books Online. Not mentioned in the article is how to apply interactive sorting to multiple columns, hence the reason for this post!

Let’s say that I have a simple table like this:

To enable interactive sorting, I open the Text Box properties for each of the column headers – the ones in the top row. Here’s an example of how I set up basic interactive sorting:

Now when I preview the report, I see icons appear in each text box on the header row to indicate that interactive sorting is enabled. The initial sort order that displays when you preview the report depends on how you design the report. In this case, the report sorts by Sales Territory Group first, and then by Calendar Year.

Interactive sorting overrides the report design. So let’s say that I want to sort first by Calendar Year, and then by Sales Territory Group. To do this, I click the arrow to the right of Calendar Year, and then, while pressing the Shift key, I click the arrow to the right of Sales Territory Group twice (once for ascending order and then a second time for descending order). Now my report looks like this:

This technique only seems to work when you have a minimum of three columns configured with interactive sorting. If I remove the property from one of the columns in the above example, and try to use the interactive sorting on the remaining two columns, I can sort only the first column. The sort on the second column gets ignored. I don’t know if that’s by design or a bug, but I do know that’s what I’m experiencing when I try it out!

Published Wednesday, March 16, 2011 12:06 PM by smisner
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

David Wimbush said:

Wow! Thanks for sharing that one, Stacia. My users are going to love that. It's surprising how often something really good like this comes to light that just got left out of the documentation. Did you just find it by accident or something?

March 17, 2011 4:42 AM
 

GrumpyOldDBA said:

that's one I didn't know! btw interactive sorting was in sql 2005 SSRS and your trick works in ssrs2005 too - very useful - thankyou

March 17, 2011 9:19 AM
 

smisner said:

GrumpyOldDBA - thanks for letting me know. I looked at the versions associated with the topic in BOL and didn't see 2005 listed, and didn't have an instance handy. I guess I should have looked in my own book since I would have covered it!

David - I was teaching a class a few years ago (which now that I think of it was most probably an SSRS2005 class!) and one of the students pointed it out to me. I was not the one to uncover this. Frankly, none of my client implementations take advantage of this feature. It doesn't work well for all layouts, especially the layouts that my clients want!

March 17, 2011 10:32 AM
 

Fenix33 said:

I love you smisner!  works a treat

July 2, 2012 7:05 PM
 

smisner said:

Happy to help! :)

July 2, 2012 7:26 PM
 

sia said:

Thanks, nice and simple..;)

September 3, 2012 11:49 AM
 

Gabszig said:

Wos, this is great, good find!

November 14, 2012 4:08 AM
 

Prasad said:

What Abuout 2 option?"Groups"

March 26, 2013 7:58 AM
 

Girish said:

That is fine man... but how we sort to all columns at a time...!!

November 7, 2013 8:10 AM

Leave a Comment

(required) 
(required) 
Submit
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement