Changing data sorting and grouping

About this topic:

  • Audience: System administrators, SSRS Report administrators

  • Conditions/restrictions: None

You can use one or multiple fields as a basis for sorting report data. You can also organize report data by grouping it according to a specific field(s). Grouping allows you to have your report data presented as collections that are related.

Sorting data

The instructions documented are for SQL Server 2016. Your steps may be different if you are using a different version of SQL Server.

You can sort data by a single field or by multiple fields. When you sort using multiple fields, SSRS uses a hierarchical method to sort it. You can sort data first by one field and then by another field(s).

To sort report data by one or multiple fields, follow these steps.

  1. Open your report in Report Builder.

  2. Left-click on the needed Row Group and select Group Properties.

    image1

  3. Click Sorting.

  4. Click Add.

  5. In the Sort by field, click the down arrow to display a list of available fields for the report.

  6. Select the field you want to use as a basis to sort the data.
    To add more sorting fields, repeat Steps 4 through 6.

  7. Under Order, select one of the following options:

    • A to Z to sort data in ascending order

    • Z to A to sort data in descending order

  8. Click OK.

  9. Click Run.

  10. Save your changes to the report.
    Note: Be sure you are saving the changes to the copy of the template.

Grouping data

The instructions documented are for SQL Server 2016. Your steps may be different if you are using a different version of SQL Server.

To have your report data presented as collections that are related, use grouping.

To group data by a specific field:

  1. Open your report in Report Builder.

  2. Left-click on the needed Row Group and select Add Group > Parent Group.
    The Tablix group window opens.

    image2

  3. In the Group by field, click the down arrow to display a list of available fields for the report.

  4. Select the field you want to use as a basis to group the data.

  5. If needed, select the Add group header or Add group footer check boxes.

  6. Click OK.
    When a field by which you want to group data is currently in your report, delete the current field to avoid duplication.

  7. Click Run.

  8. Save your changes to the report.
    Note: Be sure you are saving the changes to the copy of the template.