Sorting Data in a Report

The data within each of the data regions (table, matrix, and list) can be sorted by fields and expressions. Sort capability is added to a report before it is published. You can configure a report to support end-user sorting, allowing users to interactively change the sort order while viewing a report. Alternatively, you can use parameters to sort data, or sort data during data processing by specifying an ORDER BY clause in the query.

Interactive Sorting

You can set properties on column headings to provide interactive sorting in a published report. Sorting is specified through textbox properties. You can specify sorting for multiple columns in the same table, list, or matrix; and for nested or grouped data.

To specify interactive sorting

1. Right-click a column heading (for example, a column in a table header), and select Properties to open the Textbox Properties dialog box.
2. Click the Interactive Sort tab.
3. Select Add an interactive sort action to this text box.
4. To specify a sort expression, select the field that corresponds to the column for which you are defining a sort action (for example, for a column heading named "Title", choose =Fields!Title.Value). Specifying a sort expression is required.
5. Select the data region and scope for the sort. This step determines whether the sort action applies to all of the data regions in a report, is limited to the data region that contains the textbox, or is scoped to some other set of data regions that you choose.
6. Click OK.

To verify the sort action, you can preview the report. Columns that support interactive sorting have arrow icons to indicate sort order. To toggle the sort order between ascending and descending order, click the column heading.

Parameterized Sorting

You can use report parameters to sort data in a report based on user input by using a parameter to change the sorting properties of a data region. You can also use different methods to supply the parameter to the report.

Sorting within a Data Region, Dataset, and Grouping

You can supply a parameter value to a sort expression in a data region, dataset, or grouping. For example, you could change the sort expression for the Product table on the report, so that it sorts by product name or price.

The parameter in this example is called SortOrder and contains a list of available values: Name and ListPrice. These values correspond to the fields in the field list. The parameter also includes a default value of Name, so that the report sorts products by name by default when it is run. For information about report parameters, see Working with Parameters in Reporting Services.

Note

The values in the available values list must be the names of the fields in the fields list, not the columns in the database. In many cases, these names are similar, but they can differ. If you are sorting on a grouping, you can specify a sort order on aggregated values. Sorting on aggregated values is not supported for datasets or data regions.

The sort expression for the Product table in the report includes the SortOrder parameter. The following expression, when used in the sort expression, sorts the data in the table by the value in the SortOrder report parameter:

=Fields(Parameters!SortOrder.Value).Value

Using URL Actions to Sort on Report Columns

If you create a parameter and use it in a way similar to the previous examples, the user will see a list box from which they can choose the parameter value. When the user runs the report, the data is sorted by the chosen value. However, instead of providing a list box, you may want to provide an up or down arrow the user can click to sort the data. To do this, add a URL action to the text box that contains the text you want the user to click (for example, the text box in a column header).

The following expression, when used in a URL action, provides a link that sorts the data in the table by list price. Global properties are used to construct the URL based on the location of the report on the report server when it is run. This is useful if you do not know where the report will reside on the server. Also included is a parameter that turns off the toolbar. This hides the parameter lists, but it also hides page controls.

= Globals!ReportServerUrl & "?" & Globals!ReportFolder & "/" & Globals!ReportName & "&SortOrder=ListPrice&rc:Toolbar=false"

Note

This link may function only after the report is published to a report server. You may experience unexpected results when it is used in Report Designer preview.

For information about using URL actions, see Adding Links to a Report. For information about constructing a URL that runs a report, see URL Access.

See Also

Tasks

How to: Sort Data in a Table (Report Designer)
How to: Sort Data in a Matrix (Report Designer)
How to: Sort Data in a List (Report Designer)

Concepts

Working with Data Regions

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
14 April 2006	Changed content: Updated query to work with AdventureWorks instead of AdventureWorks2000.