Grouping and Sorting
Updated over a week ago

One of the many ways Report Designer allows you to control and organize information is by creating and sorting Groups within the report.  

Before we go any farther, let’s define what groups actually are. A group allows you to categorize and group information based on identical values. It’s helpful to think about this in terms of specific examples, so let’s look at one.

Bob is creating a report based on the information in his Contacts table, but he wants to organize his contacts based on city. For example:

Sacramento
Ben Blackstone
Tim Roth

Kansas City
Jim Thompson
Tammy Wilson 

New Orleans
Mary Ingalls

The group option allows Bob to create a report that displays his contact list broken down into cities. Bob could even use multiple groups to further organize this list by using even more criteria - sales rep, active status, or company name, just to list a few.  

In addition, Bob can use a secondary function called sorting to organize the information in each group in ascending or descending order (alphabetically, numerically or chronologically).  

This probably sounds pretty complicated, so let’s start with a basic example of how to create and use a group within a report.  Once we have that down, we’ll address how to use more than one group, and then we’ll talk about how to use the sort function to put the finishing touches on your super-organized report.

How to use a single group to organize a report

To keep with tradition, let’s use our example from creating a new report: a basic contact list. As in the above example, we will group the contacts by city.

1. Drag the Name and ShipAddressCity fields into the Details section of the report.
2. At the bottom of the screen in the Designer tab, click on Add a Group.
3. You will be prompted to choose a field to associate with this group: choose ShipAddressCity. This will group your contacts based on city, but it will not display the city in the report.  This means you have two choices:

  • If you wish to list the contact’s city in a second column next to the contact name, drag BillAddressCity from the Field List into the Detail section.  This will display as such (values are examples only):

               Ben Blackthorne  Sacramento
               Tim Roth               Sacramento
               Jim Thompson    Kansas City
               Tammy Wilson    Kansas City
               Mary Ingalls        New Orleans

  • If you wish to title each by-city group, drag ShipAddressCity from the Field list into the Group Header section.  This will title each section as such (to format these titles, see Styling):SacramentoBen BlackstoneTim RothKansas CityJim ThompsonTammy WilsonNew OrleansMary Ingalls

4. If, for some reason, you want your sections titled and you want the city listed next to the contact, you can drag ShipAddressCity into both the Group Header and Detail sections. This might appear redundant, but hey - you’re allowed to if you want to!

5. And that’s it!  If you wanted to, you could stop right there with a functional (but not very pretty) report grouping all your Method contacts by city.  But what if, like Bob, you want more from your report?  Let’s add a second group and see how that influences the final product.

How to use multiple groups to organize a report

Let’s continue on where we left off in the last section (if you haven’t followed those instructions yet, do so now).  Now let’s presume you want to add another criterion to your report.  You already have your Contact Name and City - let’s try adding a field like ShipAddressState so we can see how versatile multiple groups can make your report.

1. From your Field List, scroll down and drag over ShipAddressState into your Detail band.  You can leave it next to Name for now.  
2. Add a second Group Header either by following the steps from above, or by right-clicking in the Detail band and clicking Insert Band > Group Header.  This will add a new header below the first.
3. Associate the ShipAddressState field with the new group header by following the same steps from above.  
4. Drag the ShipAddressState field into the new group header band.  Style as desired.

Congratulations!  You have successfully implemented a second group in your report!  But if you take a look in the preview section, you will see your contacts are now divided into groups first by city, and then by state.  But what if you wanted to sort the information in these groups in a way that made more sense - say, first by state, then by city in that state?  Let’s take a look at the next section.

How to sort groups

In Method Report Designer, adding groups can categorize the data you want to display.  In the examples above we first grouped the contacts list by city, and then added a second group to further group the cities by state.  In practical terms this means there are two layers of categorizing on your contacts list.  The order you're about to see obviously isn't practical (usually, state would come before city) but follow along - you'll learn how to change this in a moment.  City > StateThat means the data is expressed first by city (in alphabetical order, by default), then by state (also alphabetical)  Here’s an illustration to clarify:

Los Angeles
California
Don Johnson
Bill Bailey

Kansas City
Missouri
Jim Thompson
Tammy Wilson

Sacramento
California
Ben Blackthorne
Tim Roth

See how the cities are represented alphabetically, with the states as a secondary header underneath?  That’s what we’re talking about when we talk about layers of filtering: from most important (city) to least important (state).  But what if you wanted to change the way these groups interacted?  What if you wanted the report to show your contact list based first on state, then on city, like this:

California
Los Angeles
Don Johnson
Bill Bailey

Sacramento
Ben Blackthorne
Tim Roth
Missouri

Kansas City
Jim Thompson
Tammy Wilson

Is there a way to rearrange groups in this way in Report Designer?  Of course there is!  In the Group and Sort section, under Field Name, you will see the fields that are active in this report.  To change their order, simply select the field and click Move Up or Move Down.  This will automatically change the grouping priority based on the formatting you have in place.  Keep in mind that formatting for one order may not look the same if you change the order, so be sure to refer to the Preview view often to view changes in real time.  You can see how this makes more sense than a City > State order - now all the cities are grouped by state!

How to sort report details

Within each group, you have the option of sorting details by ascending or descending order, which means alphabetical or numerical (or reverse order for each).  To change the way your data is sorted, select the field from the Field Name in Group and Sort and click on Ascending (the default setting).  Change the order by clicking on Descending.  It is important to note that if you have given priority to different groups (see above), this can change how the ascending/descending order will appear in the report.  For example, if you have given priority to total balance, you cannot also order the contact name alphabetically.  

Did this answer your question?