Creating Group-Level Aggregate Elements
You can use the data model editor to aggregate data at the group or report level.
For example, if you group sales data by Customer Name, you can aggregate sales to get a subtotal for each customer's sales. You can only aggregate data for at the parent level for a child element.
The aggregate functions are:
-
Average - Calculates the average of all the occurrences of an element.
-
Count - Counts the number of occurrences of an element.
-
First - Displays the value of the first occurrence of an element in the group.
-
Last - Displays the value of the last occurrence of an element in the group.
-
Maximum - Displays the highest value of all occurrences of an element in the group.
-
Minimum - Displays the lowest value of all occurrences of an element in a group.
-
Summary - Sums the value of all occurrences of an element in the group.
- Drag the element to the Drop here for aggregate function field in the parent group.
The figure below shows creating a group-level aggregate function in the G_DEPT based on the SALARY element.
Once you drop the element, a new element is created in the parent group. By default, the Count function is applied. The icon next to the name of the new aggregate element indicates the function. Pause your cursor over the icon to display the function.
The figure below shows the new aggregate element, CS_1. with the default Count function defined.
- To change the function, click the function icon to view a list of available functions and choose from the list, as shown below.
- To rename the element or update other properties, click the element's Action menu icon.
On the menu, click Properties. The Properties dialog is shown below.
Note:
Be careful when renaming an element as it can have dependency on other elements.
Set the properties described in the table below as needed.
Property Description Column Name
The internal name assigned to the element by the BI Publisher data model editor. This name cannot be updated.
Alias (XML Tag Name)
Oracle BI Publisher assigns a default tag name for the element in the XML data file. You can update this tag name to assign a more user-friendly name within the data file.
Display Name
The Display Name appears in the report design tools. Update this name to be meaningful to your business users.
Function
If you have not already selected the desired function, then you can select it from the list here.
Data Type
BI Publisher assigns a default data type of Integer or Double depending on the function. Some functions also provide the option of Float.
Value if Null
If the value returned from the function is null, you can supply a default value here to prevent having a null in your data.
Round
By default, the value is rounded to the nearest third decimal. You can change the round value, if needed.
Do Not Reset
By default, the function resets at the group level. For example, if your data set is grouped by DEPARTMENT_ID, and you have defined a sum function for SALARY, then the sum is reset for each group of DEPARTMENT_ID data, giving you the sum of SALARY for that department only. If instead you want the function to reset only at the global level, and not at the group level, select Do Not Reset. This creates a running total of SALARY for all departments. This property is for group level functions only.
