Report Based Total in OBIEE

In this scenario column "Amount Sold by Region" is a level based measure.In the column "Amount Sold by Region", amount sold is calculated at the region level.In the below figure even though the country name is changing, the region is Europe(constant) and hence Europe will have the same "Amount Sold by Region" for different country names.

With a lowest query grain, each query that requests these column will return the amount aggregated to its associated levels (in our case by region)



To obtain the good total, you have to uncheck the aggregate option : Report-Based Total (when applicable)

How Will Report-Based Totals Created in Previous Releases Be Upgraded? In previous releases, you had the ability to create report-based totals in table views. Because report-based totals are handled slightly differently in this release, you might notice a difference in totals as follows: *      If the previous table included all report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.     *   If the previous table view included a mix of report-based totals and non-report-based totals, then all measure columns and attribute columns in the upgraded table will use the Default option with the Report-Based Total option.       You can work around the upgraded totals manually. If you want to use the same measure value as in the previous release, then duplicate the measure column in the table and use the Aggregation Rule menu to specify a non-report-based total.      *   If the previous table view included all non-report-based totals, then all measure columns and attribute columns in the upgraded table will continue to use non-report-based totals.

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.

Some Examples: