Advanced Reports: Sections (pre-v2021.1)

The sections of an Advanced Report define the appearance and pattern of the report. Specifically, sections determine how frequently their cell contents are repeated.

The appearance of a report is based on data that is not necessarily known at the time it is made. The data, and the relationships it describes, are dynamic. As such, tabular reports are usually not fixed designs. Instead, you are essentially describing how the report structures itself around a set of unpredictable data. Knowing which sections are suitable for different types of content is crucial for designing the best possible report.

Types of Sections

There are two general types of sections: Static and Dynamic. The difference is in the repetition of their cells.

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. Therefore, it is most suitable for data fields, of which you will want to see each entry per row.

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, you could use a Page Header to define column headers that describe the type of data in each field.

Manipulating Sections

A report can contain any variety of sections. To add, remove, or change sections, click a section title, to the left of the row numbers in the report grid.

 

To add a section, hover over Add Section, then choose the section to add. If you add a group section, choose which data category or field to group by.

To change the category or field for an existing group section, click Modify Section.

To change the order of sections, click Move Section Up or  Move Section Down.

To delete a section, click Delete Section. This will delete every row in the section.

To add alternating background colors for each row in a section, click Section Shading. See Section Shading below for more information.

Static Sections

Sections categorized as static do not repeat according to the data in the report; although they may repeat by other means. These sections are best used for information about the report and about the data in the report. They can also be useful for boilerplate elements, such as logos, copyright notices 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 v2017.2 function or @pageNumber@ parameter to number the pages, and a report footer with summary information.

Page Header/Footer

 

Page sections repeat for each page. Page breaks are usually caused when the data in dynamic sections overflows the length of a page. The length of pages is determined by the Page Size and Orientation lists in the Report General Options window. The data on each page is consistent between the paged output types.

You can add page breaks manually by clicking the row number to add a break, then selecting Page Break from the menu. Page breaks added for a row in a dynamic section will make a new page every time the data changes.

Excel and CSV output types have no pages. Page sections function the same as Report sections for these output types.

Use these sections for information to repeat on every page, such as the title of the report, column headers, and company logos. You can also use the PageNumber() function to number each page of the report.

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.

Report Header/Footer

 

Report header and footer sections appear once each. Use these sections for introductory and concluding information.

The report footer is where you would summarize all of the report data with aggregate formulas and visualizations.

Dynamic

Sections categorized as dynamic repeat according to the 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 gauges must reference data fields in 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.

Detail

 

The rows in the Detail section repeat for each data row retrieved from the database. By default, there are exactly enough Detail rows to show all the data fields in all the categories on the report.

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.

You can use duplicate suppression to limit the number of rows to only visible data fields. You can also use Filtersto limit the rows by certain criteria.

Users with advanced knowledge of the data relationships can also use advanced join logic to limit the Detail rows to only the relevant relationships.

Group Header/Footer

 

Group sections are the primary way in which data is grouped into “buckets” that each share a unique attribute. Detail rows are arranged into the groups they belong to. The common attribute is defined by a preexisting sort.

For example, if you wanted to group a set of rows by each Product, first add a sort on the Products.ProductName field.

 

The main difference between the header and footer is that the data comes after the header, but before the footer.

When you add a group section, you are asked which sort to base the group on. For each sort, you can choose to group on either the sort field or the sort category.

• If you group on a field, the group breaks at each new value of the field.
• If you group on a category, the group breaks at each new instance of the category’s unique identifier.

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.

You can have multiple group sections, if you wanted 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, you should order group headers by their associated sorts, and group footers in inverse order.

Repeating Group

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 categories, X and Y, with a one-to-many relationship from X to Y, will display like so in a Detail section.

Category X has duplicates suppressed – this is similar to grouping on X.

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, then you can use Advanced Joins to 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. You can use repeating groups to 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. These follow the same principles as their general purpose equivalents, but within the repeating groups. For example, to calculate aggregate data for Y for each X, use an aggregate formula in the footer for the repeating group with the Y data.

Reprinting Group Headers

As of v2018.2+, you have the option to reprint Group Header rows at the top of the page if the detail section of any given group spans multiple pages. This option is only available on Group and Repeating Group Header sections. To reprint a Group Header row when its detail section continues onto another page, click the row number in the Report Designer and select Repeat Row. If the Header has been set to repeat, two blue lines will display next to the row number.

Section Shading

Section shading allows you to specify 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:

1. Click a section title to open a menu for that section. Select Section Shading.
2. Click  New to add a new shading color.
3. For each shading color, select a color with the color picker, or enter a hexadecimal color code.
4. Use the up Move Item Up and Movie Item Down icons to rearrange the order of the colors.
5. Click the Delete  icon to remove a shading color.
6. Click Okay.

Section shading is overridden by the cell background color and by Conditional Formatting.