[OBIEE 10g/11g] Aggregation Rules And Functions

 

Aggregation Rules and Functions

The following list describes the aggregation rules that are available for measure columns in the "Layout pane" and for columns in the "Edit Column Formula dialog: Column Formula tab". The list also includes functions that you can use when creating a calculated item.

Default — Applies the default aggregation rule as in the Oracle BI repository or by the original author of the analysis. Not available for calculated items.

 
Server Determined — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed within Presentation Services for simple rules such as Sum, Min, and Max. Not available for measure columns in the Layout pane or for calculated items.

Sum — Calculates the sum obtained by adding up all values in the result set. Use this for items that have numeric values.

Min — Calculates the minimum value (lowest numeric value) of the rows in the result set. Use this for items that have numeric values.

Max — Calculates the maximum value (highest numeric value) of the rows in the result set. Use this for items that have numeric values.

Average — Calculates the average (mean) value of an item in the result set. Use this for items that have numeric values. Averages on tables and pivot tables are rounded to the nearest whole number.

First — In the result set, selects the first occurrence of the item for measures. For calculated items, selects the first member according to the display in the Selected list. Not available in the Edit Column Formula dialog.

Last — In the result set, selects the last occurrence of the item. For calculated items, selects the last member according to the display in the Selected list. Not available in the Edit Column Formula dialog.

Count — Calculates the number of rows in the result set that have a nonnull value for the item. The item is typically a column name, in which case the number of rows with nonnull values for that column are returned.

Count Distinct — Adds distinct processing to the Count function, which means that each distinct occurrence of the item is counted only once.

None — Applies no aggregation. Not available for calculated items.

Server Complex Aggregate — Applies the aggregation rule that is determined by the Oracle BI Server (such as the rule that is defined in the Oracle BI repository). The aggregation is performed by the Oracle BI Server, rather than within Presentation Services. Not available for calculated items.

 
Report-Based Total (when applicable) — If not selected, specifies that the Oracle BI Server should calculate the total based on the entire result set, before applying any filters to the measures. Not available in the Edit Column Formula dialog or for calculated items. Only available for attribute columns.

Examples: