Create a Report Part 4: Groupings

In this article we'll be discussing grouping your data. While this is optional, it can improve the readability and value of your report.

While a flat list of data is useful, you may wish to organize the data into groups such as:

• Functions grouped by event
• Functions grouped by date
• Events grouped by month/year
• Payments grouped by account

To group your data, you'll add another band to the report: a Group Header. Adding the header will group the data, but we will also add fields to the header so that the structure of the grouping can be seen and understood.

To group your data:

1. In Properties Actions, click the Insert Group Header Band icon. The band will be added to the report above the detail band. Note that the group header cannot be above the page header.
2. We've added the group header band; now, we need specify what to group by. To group by event, we're going to use the event unique ID. We could use the event name, but if there are events with the same name their functions would be grouped together. We also want to make sure that the event groups are in chronological order. There is no "sort groups" functionality; instead, we add additional layers of Group by fields (this is similar to how we sorted the detail band in part 2).
   
   Select the group header band. In the sidebar, be sure you're in Properties and click the arrow by Group Fields.
3. Click the + twice to add two levels of grouping. In the Group By field select start_date_time (the event start date) and in the Then By field select event_id.
4. Next, let's add fields to the header — we'll add them in the same way we added them to the detail band. Click on the field list icon in the right nav. Hold your control/command key down and select the fields in the order you wish them to appear in the header, then release the control/command key and drag the fields to the band.
5. If you preview the report, you'll see that the functions are now grouped, but there are some formatting issues we need to clean up:
   • The column headers are currently in the page header, but they would look better under the grouping header.
   • Since the event name is in the group header, we don't need that column any more.
   • The start and end dates need to be formatted - see part 2 for instructions. We'll also be discussing a way to format this with a format string in part 5.
   • Since we only have the boolean is_definite for the status, we'll need to format that as a calculated field. We'll discuss that in part 5.
   • 
   Let's return to the designer and handle the first two items.
6. As we mentioned earlier, the page header band cannot be under a group header band. So instead of moving the entire band, we'll simply move those cells into the group header and remove the page header band completely.
   1. Click in the margin of the page header band and drag to the cells. This will select the entire table of cells.
   2. Drag the table to the group header band and place it under the group header fields.
   3. Click in the margin of the page header band to select it. Click the X in the header or the delete key to remove it from the report.
   
7. To remove the event name column from the detail band and the header:
   1. Select the name cell and click the X in the header or the delete key to remove it from the report.
   2. To redistribute the column width, select the entire table by clicking in the margin of the band and dragging to the table. Under Actions, click the Distribute Columns Evenly icon.
   3. Repeat the above two steps for the column header row in the group header band.
   
8. Preview the report to confirm that everything is working as expected. Don't forget to save!

Next up in Part 5, we'll be discussing calculated fields and format strings. We'll use these to format the status field and to also improve the look of the grouping header. See you there!