Data in a report can be sorted and organized with one or more groups subdividing the information. Groups can include one or more Group Header lines, where information about the group, particularly identification information, is presented. Groups can also include one or more Group Footer lines, where information about the group, particularly aggregates like Sum and Average, is presented.
For example, when reporting on Salespersons, it might be nice to see them sorted first by territory, then by salesperson so that one could see subtotal sales for all the salespersons in each territory. Groups are ideal for this kind of report presentation.
Adding a Group
You add a group by specifying a group number in the Order By / Grouping grid:
When you do that, the key for the first group is set to the column on that row. Two new tabs, labeled GrpHdr 1 and GrpFtr 1 are created at that time. Here is where you can specify group header and footer information.
To add another group, specify group number 2 in the Group column of the Order By / Grouping gridview. When you do that, two more tabs will appear (GrpHdr 2 and GrpFtr 2). Group number 1 represents the most major grouping. Group 2 subdivides each Group 1, etc.
If a group is defined by two columns, you can specify the same group number in consecutive rows.
Adding a Group Header
To create the header for the group, click on the GrpHdr 1 tab. You will see a panel like this:
Click the Page Break checkbox if you want each new group to start on a new page.
Click on the Display on Every Page checkbox if you want the group header to display at the top of each page even when the group key does not change. This option is helpful when a single group spans more than one page. Each page that contains details for that group will have the group header at the top. When a group with this option overflows onto a new page, "Continued from the previous page" appears in the report heading.
In most cases, you would add one or more lines to the group header -- however, it is not necessary to add any lines.
To add a line, click on the + sign shown. You will see the columns laid out to match the size and order from the Body tab, to facilitate column alignment. Then you can drag a column or something else from the treeview to a row on the line tab. Notice that SLM_TERR is much larger than the 3 position ID column on the Body line. In group headers, group footers and report footers, any column that has Align "Left" can use space from the next column(s) if they are not occupied. You may add additional lines to the group header by pressing the + next to the last line tab.
Adding a Group Footer
Group footers are generally used for subtotals. Click on the GrpFtr 1 tab and then add a line (note that you may have more than one line in a group footer). Then drag one or more columns to rows in the grid.
Notice the text ":Sum" that follows the column names. The default aggregate for columns in the group footer (like the report footer) is Sum. If you want a different aggregate (or none at all), click on the Info icon. It will bring up a dialog where you can change the aggregate:
Select the aggregate that you desire from the list. The following is a list of the aggregates and what they do:
- Avg: Returns the average of all non-null values from the specified expression.
- Count: Returns a count of the non-null values from the specified expression.
- CountDistinct: Returns a count of the distinct values from the specified expression.
- First: Returns the first value from the specified expression.
- Last: Returns the last value from the specified expression.
- Max: Returns the maximum value from all non-null values of the specified expression.
- Min: Returns the minimum value from all non-null values of the specified expression.
- StDev: Estimates standard deviation based on a sample. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Null values are ignored.
- StDevP: Calculates standard deviation based on the entire population. The standard deviation is a measure of how widely values are dispersed from the average value (the mean). Null values are ignored.
- Sum: Returns a sum of the values of the specified expression.
- Var: Estimates variance based on a sample. Null values are ignored.
- VarP: Calculates variance based on the entire population. Null values are ignored.
Add additional text, columns, etc., and add additional lines as desired.
Save and run the report. Your report will look something like this:
The first red box surrounds the group header. Notice it has a pale gray background.
The second red box contains the group footer.
Deleting a Group
If you find it necessary to delete a group that you have defined, you must use the Clear Groups... button next to the Order By / Grouping grid. That is to say, you cannot delete individual groups -- if you want to delete a group, you must delete all groups and all lines associated with them.