Interacticely sorting columns in SQL Server 2005 Reporting Services
September 17, 2009 2 Comments
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 easily create drill-down and drill-through reports with just a few mouse clicks.
A drill-down report allows your users to see more or less detail information by clicking on a row of data and having the report expand or collapse supporting detail rows. Drill-through reports take the user from one report to another supporting report via a mouse click. For example, by clicking on a Product Number in a Sales Order, the user will be taken to the Product Information report for that product.
You can also create reports that provide interactive sorting. On a report that uses a table data region, users can click on a column heading and have the report auto-magically sort by that column. 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.
We want to allow users to sort the list by the First Name or the Last Name column. In the Layout view, click the table First Name header cell to select it, then right click to open a context-sensitive menu, and choose Properties as shown in the following illustration.
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 that your users may want to sort by. In this example, I’ll enable interactive sorting all three columns, however in practice there’s probably little reason someone would want to sort by the Phone column. 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 order of the 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.
Got other techniques for providing a greater level of interactivity in your reports? I’d love to hear about them so feel free to share in the comments section below.