Naturally, OBIEE provides many easy to use features for creating totals on a report. Perhaps the easiest method is to use the Total By button in Answers to create total or subtotal rows within your report, but what about creating total columns as opposed to rows? Here are two methods, each using a different definition of “total”:
Well, first let’s set up an example. 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:
- 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.
- 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:
Not bad! As you can see, OBIEE offers considerable flexibility in defining total columns and rows.