Microsoft SQL Server 2005 Reporting Services

Interactive Sorting ^{NEW in 2005}

SSRS 2005 adds a new, interactive sort action that allows users to sort an HTML-rendered report.

The RDL that describes interactive sorting is as follows :

<UserSort> <SortExpression>=Fields!Name.Value</SortExpression> </UserSort>

A developer can set up this type of sorting though a text box's property UserSort or through the Interactive Sort tab of a text box's property sheet (see Figure 12.2).

To determine the proper position of an interactive sorting control, visualize the output and decide what location makes sense for the interaction to occur. For example, to sort values within a group , a cell in a group header should contain an interactive sort control.

The following are the components of an interactive sort:

• Sort expression (required) Normally, is a column expression for the column to be sorted. For example, if the designer needs to sort a column that contains the expression =Fields!FirstName.Value , the same expression is used for sorting. In a few cases, the designer might choose to use different expressions for sorting; for example, the designer might decide to use =Fields!UserId.Value if UserId is sequenced in the same sort order as FirstName .

• In data region or grouping to sort current scope Enables sorting on the repeated rows in the data region that contains the sort item. The scope must be the current scope, a peer scope that is a data region, or an ancestor scope. The Choose Data Region or Grouping option allows you to select a specific data region or group (for example, if a developer wants the sort to affect additional data regions or data regions other than the one that contains the sortable item).

• Evaluate sort expression in this scope Specifies which data regions or groups are sorted. Setting these options is useful if you are targeting a specific data region and want to further specify where sorting occurs within that data region.

• Detail scope Sorts only the repeated rows of the selected data region.

• Data region or grouping Selects a specific data region or group on which to sort.

There is nothing magical about interactive sorting behind the scenes. If you look at the rendered report's source, you will notice the following pieces of the puzzle were added to your field to be sorted:

[View full width]

[View full width]

ImageName is initially unsorted.gif and changes to sortAsc.gif or sortDesc.gif , depending on the direction of the sort. This is internal functionality to SSRS and you do not want to mess with it. It reveals that the "magic" is simply scripting. You can further explore this functionality by adding more interactive sort fields and observing changes in rendered HTML and the report's URL.