A nice feature that appeared first in SQL Server 2008 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!