Total Columns

Report totals are pretty common, so it makes sense that OBIEE offers many options for totaling. If your report calls for total or sub-total rows, those are best left to the "Grand Total" and "Total By" buttons in Answers. However, if your report calls for Total Columns, those typically need to be created in the repository as opposed to creating them in Answers (on a report by report basis).

There are two methods I use for creating Total Columns in the Repository, depending on whether I want the Total Column to reflect only the filtered values in a report, or if I want the Total Column to aggregate across an entire dimension, indepedent of the values in the report.

Here's an example of each method:

In the following dataset, the Total US column represents one of the top levels in a geographic dimensional hierarchy, whereby Region is the next level down, and all regions are in the US:

Method 1:

To create a Total Column that will aggregate all Dollars across the entire geographic dimension (regardless of the data that is displayed on the report), you would create a logical column in the repository using the dollar column, and you would set the Logical Level for the geographic dimension to the Grand Total Level in the geographic dimensional hierarchy. Here’s the result of your “Grand Total”:

If we filter the report to only display the Central and Eastern Region, the Grand Total column will still calculate the total across all regions: $13,087,529.

Method 2:

To create a Total Column that only aggregates the data that is displayed within this report (thus taking the filters into account), you would create a new report column in Answers, and enter a formula similar to this:

SUM(Paint.”Sales Measures”.Dollars BY Markets."Total US")

Here’s the result of your new “Report Total” column along with the “Grand Total” column, with Region filters applied:



Note the difference between the Report Total (Method 2) and Grand Total (Method 1).