Advertisements

Report-Based Total Pivot table calculation option in OBIEE

If you have a scenario where your pivot totals are not matching your pivot numbers (in other words, the total is not correct) in an OBIEE pivot table, this post may help.

There might be times when your pivot table shows a set of numbers that do not add up to the Grand Total calculated by OBIEE. This could be due to the grain of the data returned from your SQL, possibly from performing different calculations on multiple columns.

If already checked (default), then uncheck or deselect the “Report-Based Total” option as shown below to see if this solves your problem. (And if unchecked, then check it to see if that helps you).
From within your pivot view, select the button on your measure value, then select “Aggregation Rule”, then uncheck “Report-Based Total”.

Report-Based-Total

According to the OBIEE documentation … “If the option Report-Based Total is not selected, the Oracle BI Server will calculate the total based on the entire result set, before applying any filters to the measures.”

Basically the difference is … instead of the BI Server generating SQL that will calculate the total based on the grain of your rows (how the data rows look in the table view), it will calculate the total based on the how the data is partitioned over the rows and columns (the pivot table values). 

This is an often overlooked option that can, but rarely, affect your results. Based on your scenario, you may need the selection checked or unchecked.  Try it and see if helps your situation.

Advertisements

One Response to Report-Based Total Pivot table calculation option in OBIEE

  1. AD says:

    Hi,
    Your post is a bit older. But today it helped me alot. I was running into an issue related grand total in pivot table.

    Thanks a lot.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: