One of the most important things when creating reports, using Oracle BI tools or not, is to understand the data you are using. For Oracle Sales Cloud and other applications using the Oracle BI suite, this means understanding how subject areas provide data for reports. That is what this post is all about.
The before
Check out the image below. I made little report listing opportunities in Oracle Sales Cloud. The total number of opportunities is 2988.
The after
The moment I add information from another data source, the number of opportunities changes. The moment I added data from revenue lines in Oracle Sales Cloud, the number of opportunities decreases! It is important to understand why this happens and how to use or revert this behavior.
Revenue lines are related to opportunities as they contain the product/service information that the salesreps are trying to sell through opportunities. Once I add revenue line information, the BI engine will suppose that you are looking for opportunities that have revenue lines. On purpose, the BI engine will no longer take into account opportunities without revenue lines. This explains the decrease in total number of opportunities in the report as shown in the image below.
What is happening here is that the BI engine always by default will only show those records that have data for all the data sources that are being used in the report. Initially the report was only using the opportunity object and hence showed the correct total number of opportunities. Once I added a revenue line field, the BI engine only took into account those opportunities having revenue lines, for which revenue line data was available.
This is actually not a problem, if you run a report to analyze opportunities and the products being sold (for which you need revenue lines), you actually would want to exclude opportunities that do not have revenue lines. It is something to take into consideration, do you want those records or not.
The fix
So in case this generates unwanted results in your reports, there is a way to instruct the BI engine to not behave like this.
In order to do so, the only thing to do, in the report properties, is to tick the checkbox ‘Include Null Values’.
From then on, the report will include opportunities without revenue lines and the total number of opportunities will increase back to the expected number of 2988.
It is important to understand to understand the consequences of this. You should always consider whether or not this effect is desired or not, whether or not that checkbox should be ticked or not:
- If you make a list of contacts, and you add call information, will you only see contacts that were at least called once? Will you see contacts that were never called? Do you want to see them in your report or not?
- If you make a list of accounts, and you add lead information, will you only see accounts for which leads have been created? Will you see accounts without leads? Do you want to see them in your report or not?
