How to: Sort Data in a Data Region (Report Builder 3.0 and SSRS)

To change the sort order of data in a data region when a report first runs, you must set the sort expression on the data region or group. By default, the sort expression for a group is automatically set to the same value as the group expression.

• In a tablix data region, set the sort expression for the data region or for each group, including the details group. If you have only one details group in a tablix data region, you can define a sort expression in the query, on the data region, or on the details group and they all have the same effect.

• In a chart data region, set the sort expression for the Category and Series groups to control the sort order for each group. The order for colors in a chart legend is determined by the sort expression for the data points in the Category group.

• In a gauge data region, you do not typically need to sort data 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 the sort expression for the group.

For more information, see Filtering, Grouping, and Sorting Data (Report Builder 3.0 and SSRS).

For a tablix data region, you can also add an interactive sort button to the top of a column header to provide the user with the ability to change the sort order of groups or detail rows. For more information, see Interactive Sorting (Report Builder 3.0 and SSRS).

Note

You can create and modify report definitions (.rdl) in Report Builder 3.0 and in Report Designer in Business Intelligence Development Studio. Each authoring environment provides different ways to create, open, and save reports and related items. For more information, see Designing Reports in Report Designer and Report Builder 3.0 (SSRS) on the Web at microsoft.com.

To sort data in a Tablix data region

1. On the design surface, right-click a row handle, and then click Tablix Properties.

2. Click Sorting.

3. For each sort expression, follow these steps:

   1. Click Add.

   2. Type or select an expression by which to sort the data.

   3. From the Order column drop-down list, choose the sort direction for each expression. A-Z sorts the expression in ascending order. Z-A sorts the expression in descending order.

4. Click OK.

To sort values in a group, including the details group, for a Tablix

1. On the design surface, click in the tablix data region to select it. The Grouping pane displays the row groups and column groups for the Tablix data region.

2. In the Row Groups pane, right-click the group name, and then click Edit Group.

3. In the Tablix Group dialog box, click Sort.

4. For each sort expression, follow these steps:

   1. Click Add.

   2. Type or select an expression by which to sort the data.

   3. From the Order column drop-down list, choose the sort direction for each expression. A-Z sorts the expression in ascending order. Z-A sorts the expression in descending order.

5. Click OK.

To sort x-axis labels in alphabetical order on a chart

1. Right-click a field in the Category Field drop-zone and click Category GroupProperties.

2. In the Category Group Properties dialog box, click Sorting.

3. For each sort expression, follow these steps:

   1. Click Add.

   2. Select the expression that matches your grouping field. You can verify the expression for the grouping field by clicking Grouping.

   3. From the Order column drop-down list, choose the sort direction for each expression. A-Z sorts the expression in ascending alphabetical order. Z-A sorts the expression in descending alphabetical order.

4. Click OK.

To sort the data points in ascending or descending order on a chart

1. Right-click a field in the Category Field drop zone and click Category GroupProperties.

2. In the Category Group Properties dialog box, click Sorting.

3. For each sort expression, follow these steps:

   1. Click Add.

   2. Select the expression that matches your data field. In most cases, this is an aggregated value, such as =Sum(Fields!Quantity.Value).

   3. From the Order column drop-down list, choose the sort direction for each expression. A-Z sorts the expression in ascending order. Z-A sorts the expression in descending order.

4. Click OK.

To sort data in ascending or descending order for display on a gauge

1. Right-click the gauge and click Add Data Group.

2. In the Gauge Panel GroupProperties dialog box, click General if necessary.

3. In Group expressions, click Add.

4. In Group on, type or select an expression by which to group the data.

5. Repeat steps 3 and 4 until you have added all the group expressions you want to use.

6. Click Sorting.

7. For each sort expression, follow these steps:

   1. Click Add.

   2. Select the expression that matches your grouping field. You can verify the expression for the grouping field by clicking Grouping.

   3. From the Order column drop-down list, choose the sort direction for each expression. A-Z sorts the expression in ascending order. Z-A sorts the expression in descending order.

8. Click OK.

For more information about how data is grouped in a gauge, see Gauges (Report Builder 3.0 and SSRS).

See Also

Tasks

How to: Specify Consistent Colors across Multiple Shape Charts (Report Builder 3.0 and SSRS)

Concepts

Report Builder 3.0 Help for Dialog Boxes, Panes, and Wizards (F1)

Charts (Report Builder 3.0 and SSRS)

Formatting Axis Labels on a Chart (Report Builder 3.0 and SSRS)