Aggregates
You use an aggregate function, such as sum or count, to calculate the value of a specified column up to the occurrence of a break. In RBF, an aggregate must appear in a footer for a report, page, or break. RBF calculates the aggregate value each time a break occurs in the specified footer.
The cut‑off point for data to be included in the calculation of an aggregate depends on whether the aggregate is simple or cumulative. For more information, see Simple and Cumulative Aggregates.
The following table lists all of the available aggregate functions:
Aggregate Function: | Returns: |
|---|---|
Any | 1 if any data exists, 0 if none exists |
Average | Mean average of all values in the column |
Count | Count of all the values in the column |
Minimum | Smallest value in the column |
Maximum | Largest value in the column |
Sum | Arithmetic sum of all the values in the column |
Note these restrictions:
• If the aggregate column is numeric, all of these functions are available.
• If the aggregate column is a character or date data type, the any, count, minimum, and maximum aggregate functions are available. Additionally, you can take a sum of a date data type column, but only if date intervals (such as 1 day, 2 hours) are stored in the column. Taking a sum on absolute dates (such as December 13, 1988) results in an error.
Simple and Cumulative Aggregates
Aggregates can be simple or cumulative.
A simple aggregate is determined by the type of footer in which you specify it. For example:
• If you specify the sum aggregate for the budget column in a report footer, then the aggregate contains the sum of the values in the budget column for all rows in the report.
• If you specify the sum aggregate for the budget column in the page footer, the aggregate contains the sum of the values in the budget column for the rows printed on each page of the report.
• If you specify the sum aggregate for the budget column in the break footer for the Department column, the aggregate contains the sum of the values in the budget column for all rows in each department.
A cumulative aggregate is a running total. It contains the aggregate of all the rows processed since the start of the report and does not depend on the location of the aggregate.
Unique Aggregates
You can create unique aggregates for sort (break) columns only. Unique aggregates are calculated by using only the unique values of the sort columns. You cannot specify a unique aggregate for a non‑sort column.
If you change a column that has a unique aggregate associated with it from a sort to non‑sort column, RBF gives you the following options:
• Deleting the unique aggregate
• Changing the unique aggregate to non‑unique
The unique aggregates are described in the following table:
Unique Aggregate | Returns: |
|---|---|
Average (Unique) | Mean average of all the unique values in a sort column. |
Count (Unique) | Count of all the unique values in a sort column. |
Sum (Unique) | Sum of all the unique values in a sort column. |
Guidelines for Creating an Aggregate
When creating aggregates, you:
• Must create an aggregate in a footer (break, page, or report)
• Can create an aggregate on any column (the column does not have to be a sort column)
• Can create more than one aggregate in a footer and more than one type of aggregate for a particular column
For example, in a table containing the columns Project, Employee, and Salary, you can create a break footer for the Project column (Project_Break_Footer). Then, you can create Average and Sum aggregates (non‑cumulative) for the Salary column in the Project_break_footer. This instructs RBF to:
• Break each time it finds a new value in the Project column
• Calculate the total salary and average salary cost of each project
After creating an aggregate, you can edit it in the same way you edit columns. You can:
• Change its display format
• Edit it or the associated headings
• Move it to another footer section
• Delete the aggregate
Create an Aggregate
To create an aggregate
1. Place the cursor in the position in the footer section where you want to create the aggregate, and select the Create operation from the Report Layout frame menu.
(If you must create a footer section, see Create Break Headers, Footers, and Other Report Sections.)
The Create submenu appears.
2. Select the Aggregate operation.
The Create an Aggregate pop-up appears. The pop-up contains a list of the columns in your report.
3. Position the cursor on the column for which you want the aggregate, and choose the Select operation.
The Selecting Aggregates pop-up appears. This pop-up contains a list of the aggregate functions that are available for the column to be aggregated.
4. Place the cursor on the desired aggregate function and choose the Select operation.
The Cumulative Aggregation pop-up appears.
5. Select Yes to make the aggregate cumulative or No to make the aggregate simple (non‑cumulative). For details, see Simple and Cumulative Aggregates.
6. Choose the Select operation to finish creating the aggregate.
You are returned to the Report Layout frame.
Last modified date: 11/28/2023