Sorting Data in a Report
To control the sort order of data in your report, you can sort data in a dataset query, or define a sort expression for a data region or group. You can also add interactive sort buttons to tables and matrices to enable a user to change the sort order of report data.
All three types of sorts can be combined in the same report. User-initiated interactive sorts are applied after sort expressions, and sort expressions are applied after the query sort.
Most aggregate calculations for report items are not dependent on the sort order of data, with the following exceptions: First, Last, and Previous. For more information, see Using Built-in Report and Aggregate Functions in Expressions (Reporting Services).
Sorting Data in a Dataset Query
Include sort order in the dataset query to pre-sort data before it is retrieved for a report. By sorting data in the query, the sorting work is done by the data source instead of by the report processor.
For a Microsoft SQL Server data source type, you can add an ORDER BY clause to the dataset query. For example, the following Transact-SQL query sorts the columns Sales and Region by Sales in descending order from the table SalesOrders: SELECT Sales, Region FROM SalesOrders ORDER BY Sales DESC. For more information, see Sorting Rows with ORDER BY.
Note
Not all data sources support the ability to specify sort order in the query.
Sorting Data with Sort Expressions
To sort data in the report after it is retrieved from the data source, you can set sort expressions on a Tablix data region or a group, including the details group. The following list describes the effect of setting sort expressions on different items:
Tablix data region. Set sort expressions on a table, matrix, or list data region to control the sort order of data in the data region, after dataset filters and data region filters are applied at run time.
Tablix data region group. Set sort expressions for each group, including the details group, to control the sort order of group instances. For example, for the details group, you control the order of the detail rows. For a child group, you control the order of group instances for the child group within the parent group. By default, when you create a group, the sort expression is set to the group expression and to ascending order.
If you have only one details group, you can define a sort expression in the query, on the data region, or on the details group to the same effect.
Chart data region. Set a sort expression for the category and series groups to control the sort order for data points. By default, the order of data points is also the order of the colors in the chart legend. For more information, see Formatting Series Colors on a Chart.
Gauge data region. You do not typically need to sort data for a gauge data region because the gauge displays a single value relative to a range. If you do need sort data in a gauge, you must first define a group, and then set a sort expression for the group.
Interactive Sorting
To enable a user to change the sort order of report data, add interactive sort buttons to columns. Users can toggle a table to sort between ascending and descending based on the column value. Interactive sort is supported in rendering formats that allow user interaction, such as HTML.
You add interactive sort buttons to a text box. By default, every Tablix data region cell contains a text box. In the text box properties, you specify which part of a table or matrix data region to sort (the parent group values, the child group values, or the detail rows), what to sort by, and whether to apply the sort expression to other report items that have a peer relationship. For example, if a table and a chart that provide views on the same dataset are contained in a rectangle, they are peer data regions. When a user toggles the sort order in the table, the sort order for the chart also toggles. For more information, see Adding Interactive Sort to a Data Region.
For step-by-step instructions, see How to: Add Interactive Sort to a Table or Matrix (Reporting Services).