Grouping and Sorting Data (Report Builder)
Groups are used to organize data so that the report is displayed in a visual hierarchy and so that you can obtain subtotals. After the data is grouped, you can then sort the data within each group in ascending or descending order to make data easier to locate. For example, suppose you create a sales report that contains information about your customers, the sales territories that these customers are located in, the sales person for each sales territory, and the sales amounts. You can group the data by Sales Territory and then Customer to see total sales for each territory and each customer. Sorting on the sales territory and the sales person fields will make the data easier to read. Report Builder lists customers alphabetically within their respective sales territories and provides the sales totals for you. The data in the sales territory group can be sorted into alphabetical order, and then the data in the other groups can be sorted so that they appear in alphabetical order as well. In addition, you can sort on totals as well. For example, you can sort territories by the total sales for each territory.
Grouping Data
As you drag fields and entities to the design area, you are also organizing the data into groups. In Report Builder, groups are indicated by the group tabs that typically appear along the top edge of the report layout. Typically, the broadest group is placed along the outside edge of the report with the data getting more specific as you read across the report. Using the example above, the broadest group in common to the data appearing in the report is the Sales Territory, the most specific data is the customer and sales amount.
Two types of groups are created: Value groups and Entity groups. A value group is a group on a field. The field name appears in a value group tab. When the report is run, the value group returns a single instance of the group for each distinct value. For example, suppose you add the Country Name field to the report. The country name only appears once within the report. An entity group is a group on an entity. The entity name appears in an entity group tab. When the report is run, the entity group returns a single instance of the entity. For example, if you have a Territory group, each territory is listed only once within the report.
.gif "Group header tabs")
When grouping your data, note that:
- You can create as many groups in the report layout template as you want.
- To add an item to a group or to change the current grouping, drop the field on the group tab.
- You can re-arrange the groups by dragging and dropping a group tab on another group tab in the report layout.
- When the group is the entity group for a field, the field is added to that group when it is dropped to the right side of the report layout template. If no entity group exists, a new group for the field is created.
- A new group for a field is created when the field is added to the left side of the report layout template. If the field is an identifying field for the entity, an entity group is created when you add the field to the left side of the report layout template.
- When you add an entity to the design area, Report Builder creates a group that uses the entity's name. Adding multiple fields from an entity to the design area also groups the fields using the entity's name.
- When you add a single-value field from a related entity to the right side of the report layout template, the field is added as an additional detail to the existing group.
- When you add an aggregate field (a field containing a total over a set of values) to the report layout template, the field becomes an additional detail to whichever group it is added.
- If there is a one-to-many relationship between two entities, the primary entity in the Explorer pane changes when you add the entity to the design area and then add the second entity. For example, if you add the Customer entity to the design area, the Customer entity becomes the primary entity in the Explorer pane. If you add the Sales Order entity to the design area, the primary entity changes to Sales Order in the Explorer pane.
- If you want to create a value group within a particular field, select the field and drag it to the left of the original group. A new group is created that is named after the field.
- If you add one or more aggregate fields to the report layout template and there are no groups available to associate with the aggregate, a Total group is created.
- When a value or entity group is added to a query with only a total group, the total group is removed and the aggregate fields are added to the value or entity group. If all the value or entity groups are removed from the report layout, the aggregate fields revert to a total group.
Sorting Data
Data within each group can be sorted by any field in the group, or any field for which subtotals are displayed. Each group can be sorted by multiple fields. For example, suppose your report contains a First Name and Last Name field within the Customer Name group. If you sort Last Name from A to Z, individuals with the same last name will appear grouped together in the report but they will be listed in the order that Report Builder retrieves the data. If you also sort First Name from A to Z, individuals with the same last name will appear grouped together in the report but they will be sorted in alphabetical order by their first names as well.
To sort the data in the report, use the Sort and Group dialog box. Select the group that contains the data that needs to be sorted, and then use the Sort by and Then by drop-down lists to specify a sort order for each field within the group.
Allowing Readers to Sort Data within a Report
By default, individuals who read your report can change how the data is sorted after running the report. After running the report, individuals reading the report can sort on a specific field using the up and down arrows displayed beside each column name. Clicking the sort arrows once sorts the containing group by that field in ascending order. Clicking the icon again sorts in descending order. Report viewers can sort rendered reports multiple fields by pressing and holding the Shift key while they click the sort icon. To disable this functionality, you can clear the Allow users to sort the report data when they view it check box in the Report Properties dialog box.
See Also
Tasks
How to: Add or Remove a Page Break Between Groups (Report Builder)
How to: Sort Data (Report Builder)
Concepts
Controlling Page Breaks, Headings, and Columns (Report Builder)
Working with Fields (Report Builder)
Working with Report Layouts (Report Builder)
Other Resources
Sort and Group Dialog Box (Report Builder)
Report Properties Dialog Box (Report Builder)