• Article

How to: Sort Data in a Data Region (Reporting Services)

To control 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.

  • 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 Grouping Data in a Data Region and Sorting Data in a Report.

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 Adding Interactive Sort to a Data Region.

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.

    Note

    If the Grouping pane is not visible, select Grouping Pane on the View tab.

  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, and 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 Adding Data to a Gauge Data Region.