Business Intelligence Developers Studio (aka BIDS) for Microsoft SQL Server 2005 Reporting Services makes creating engaging and dynamic reports nearly trivial in some respects. You can create drill-down and drill-through reports with just a few mouse clicks.

You can also create reports that provide interactive sorting. Users can click on column headings and have the report automatically sort by that column. This question regularly comes up as I'm leading training sessions and occasionally in the online forums. So, let's walk through a graphical example to demonstrate.

Here's an example of a contact phone list report created in BIDS. It lists contacts from the sample Adventureworks database using a table data region.

Let's provide a way for our users to sort the list as they wish. In the Layout view, click the table header cell, right click, and choose Properties from the context menu.

This opens the Textbox Properties window as shown below. Notice the 6th tab is called Interactive Sort. Check the "Add an interactive sort action to this textbox" option. Then click the down arrow in the Sort Expression combobox. Choose the field on which you like to sort when the user clicks this textbox. In our case this this Fields!FirstName.Value expression.

Repeat this process for each of the columns in the table data region.

Once completed, preview the report. The report is still sorted in the default order. But you'll notice the little arrows beside each column header.

By clicking on the little arrow beside the column header text, our users can change the sort report. Clicking the same arrow for a second time reverses the sort order.

And there you have it. With only a few mouse clicks, we've enriched the experience our users have while viewing this report. Of course, this technique only works when the report is rendered in formats that support interactivity. When being viewed as a TIFF, for example, the user will not be able alter its presentation.

Cheers!

Joe