Advanced Reports: Sections (v2021.1+)
The sections of an Advanced Report define its appearance and pattern. Specifically, sections determine how frequently cell contents are repeated.
The appearance of a report is based on data that is not necessarily known at the time it is made. Reports are usually not fixed designs—the data, and the relationships it describes, are dynamic. Knowing which sections are suitable for different types of content is crucial for designing the best possible report.
Types of Sections
The cells in a static section do not repeat according to the report data. Static sections are best used for information that is not related to data rows. For example, defining column headers that describe the type of data in each field, or a page number at the bottom of each page.
The cells in a dynamic section repeat according to the data that is returned to the report. Dynamic sections are suitable for showing content related to that data. For example, cells in a Detail section repeat for every composite data row in the report.
Report Headers, Page Headers, Page Footers and Report Footers are categorized as static because they do not repeat according to the data in the report; although they may repeat by other means. These sections are best used for report metadata. They can also be useful for boilerplate elements, such as logos, copyright notices, titles or disclaimers.
A typical report may have a report header with the title of the report, a page header with labels for each column, a page footer that uses the
PageNumber() function or
@[email protected] parameter to number the pages, and a report footer with summary information.
Page Header & Page Footer
The rows in the Page Header section appear at the top of every page of a report, and the rows in the Page Footer section appears at the bottom of every page of a report. Typically, the Page Header section is used to designate column headers for a report, and the Page Footer section is used to display the page number and/or confidentiality notices for a report.
Page Headers and Page Footers are not intended to perform calculations or display data fields. For this reason, a Page Header populated with a data field will only return the first line of data in that field; a Page Footer will return only the last line of data.
Page breaks are usually caused when the data in dynamic sections overflows the length of the page, determined by the Page Size and Orientation settings in the report’s General Options. Manual page breaks may also be added.
Excel and CSV export types have no pages. Page sections function the same as Report sections for these export types.
Even though page sections repeat, they are not considered dynamic sections because their repetition does not directly depend on the data. Thus they are not suitable for displaying data fields. Since there is no way of knowing ahead of time which data will appear on which page, these sections are not suitable for summarizing data with aggregates or visualizations.
A report may only have one page header and one page footer section, but each section may consist of multiple rows.
Report Header & Report Footer
Report Header and Report Footer sections appear once each, at the start and end of the report, respectively. Use these sections for introductory and concluding information. The report footer is ideal for summarizing all of the report data with aggregate formulas and visualizations.
Report Headers appear below Page Headers.
A report may only have one report header and one report footer section, but each section may consist of multiple rows.
Groups, Repeating Groups and Detail sections are categorized as dynamic because they repeat according to data in the report. These sections are best used for displaying and grouping data from data fields. Dynamic sections comprise the bulk of most tabular reports. Charts, maps, and gauge visualizations reference data fields in the dynamic sections.
The key for knowing how to use dynamic sections is knowing how the data will repeat in relation to the rest of the report.
The rows in the Detail section repeat for each data row retrieved from the Data Object, and is the main section of most reports.
Detail sections are typically used for showing the actual data values. This is the main area of display for the bare report data. Aggregate formulas usually reference cells in the Detail section.
A report may only have one detail section, but it may consist of multiple rows. To have more than one detail section, use Repeating Groups.
Group Header & Group Footer
Groups are the primary way in which data is assembled into related categories that each share a unique attribute. Detail rows are arranged into the groups they belong to. The common attribute is defined by a sort.
The rows in a Group Header section will appear above the Detail section for each unique value of the sorted Data Field, or a formula. Typically, Group Header sections are used to display data as labels.
The rows in a Group Footer section will appear below the Detail section for each unique value of the sorted Data Field. Typically, Group Footer sections are used to calculate subtotals.
For each sort, the group can change on either on either the Data Object/Unique Key or the Sort Field.
- When grouping on a Data Object, the group breaks at each new instance of the data object’s unique key.
- When grouping on a Sort Field, the group breaks at each new value of that field.
For example, instead of grouping by each Product, you may want to instead group by product quantity, so that products that are low or out of stock are grouped together. First, add a sort on the Products.Quantity field, then group by that field.
There may be multiple group sections, to add additional levels of stratification to the report. Each group requires a corresponding sort.
The order of the sorts has an effect on the way the data is arranged. Data is sorted by the first field, then any ambiguities are sorted by the second field, then any remaining ambiguities are sorted by the third, and so on. In general, order group headers by their associated sorts, and group footers in inverse order.
Repeating Groups are a way to organize and display data that contain multiple one-to-many relationships. Each repeating group has data from “many” category, grouped by the “one” category. The following diagrams demonstrate how this works.
Two Data Objects, X and Y, with a one-to-many relationship from X to Y, will display like so in a Detail section:
If there is a third category, Z, with a one-to-many relationship from X to Z, there is not necessarily a relationship between Y and Z. So there may be blank rows with only Y or only Z.
There are two potential solutions. If there is a relationship between Y and Z, Advanced Joins can impose additional constraints so that only the rows with both Y and Z are shown. For more information, see Joins.
On the other hand, there may be no relationship between Y and Z, only between X and Y, X and Z. Or there may be a relationship, but it does not matter for this data set. Repeating groups can ignore that relationship and simply organize the data by X instead.
To do so, create two repeating groups for X, and put Y in the first, and Z in the second:
Each repeating group section has its own group header, detail, and group footer sections. These follow the same principles as their main counterparts.
Create, Modify, Delete Sections
- To add a new section either:
- click the Add Section button
- click a Section Menu icon then hover over Add Section
- right-click anywhere on a Section Header and hover over Add Section
- To change the sort that a group header, group footer or repeating group breaks on, either:
- click the Section Menu icon on the section to be changed, then click Modify Section or Edit Group.
- right-click anywhere on the Section Header of the section to be changed, then click Modify Section or Edit Group
- To re-arrange the order of group headers, group footers or repeating groups, either:
The order of the sorts as they appear in the Sorts dialog have an effect on the way the data is arranged. Moving the sections up and down will only change where in the report that data appears, not change its grouping/sorting order.
Report Headers, Page Headers, Detail, Page Footers and Report Footers have fixed positions.
- click the Section Menu icon on the section to be changed, then click Move Section Up or Move Section Down
- right-click anywhere on the Section Header of the section to be changed, then click Move Section Up or Move Section Down
- To delete an existing section, either:
- click the Section Menu icon on the section to be removed, then click Delete Section
- right-click anywhere on the Section Header of the section to be removed, then click Delete Section
Reprinting Group Headers
If the detail section of any given group spans multiple pages, it may be desirable to have the Group Headers repeated at the start of the next page.
To reprint a Group Headers when the detail section continues onto another page:
- Open the Row Menu by either:
- clicking the Row Menu icon on the row to be repeated
- right-clicking anywhere on the row number of the row to be repeated
- Check the Repeat Row checkbox.
- Repeat steps 1–2 for any additional row that should repeat.
When rows are set to repeat, two blue stripes will appear on the left side of the row number. For example:
For additional information about the Row Menu, review the Rows Section of the Advanced Reports: Design Grid article.
Section shading creates alternating background colors for repeating elements in a section. Alternating colors applies to Detail and Group sections; for other sections, only one color can be used.
To add section shading:
- Open the Section Menu by either:
- clicking the Section Menu icon on the section to be changed
- right-click anywhere on the Section Header of the section to be changed
- Click Section Shading to open the Section Shading dialog.
- Click New to add a new shading color. Any number of colors may be added.
- Click the Color Chooser icon, or enter a hexadecimal HTML color code to select a shading color.
- Use the up Move Item Up and Move Item Down icons to rearrange the order of the colors.
- Click the Delete icon to remove a shading color.
- Click Okay to close the dialog and apply the color settings.
When Section Shading is applied to a section, a small indicator with the shading colors will be shown above the Section Menu icon. For example: