Adding dummy rows to the database is very rarely the right answer. This should be a simple report level config option.

In some cases you will want to use an outer join (or the Preserve Dimensions Technique) to bring back additional rows. This is useful for charting continuous data that has missing data (like a chart by day where there is no data for Wed).

However in some cases you are simply want a zero to show in the report when there is no value. The custom display formats have worked in some versions in the past, but I know they are not working in 11G (there is an Oracle defect Bug 14092232 filed on this already). I am encountering this problem right now on my current project.

Jeff M.

Help the community by fixing grammatical or spelling errors, summarizing or clarifying the solution, and adding supporting information or resources. Always respect the original author.

Basically what I have is a line graph that does not go to 0 when I have 0 value (e.g. no sales that day) instead of jumps from for example 5th to 8th of the month. I need that line to go to 0 on 6th and 7th as a real representation what happened. I understand that this is normal OBIEE behavior but I need those zeros.

Well that is my problem, that is what I am trying to avoid. I have a fact with millions of rows and adding anything to it would just additionally slow me down. In my case I have 4 different dates alone, I can not simply fill the "holes" by zeros. I did outer join with virtual fact which is pretty much what you are suggesting, but cartesian product kills my performance.

I am suggesting you query the database to see which dates actually exist, then insert a single row for each date for which NO data exists with a quantity of zero. This will avoid the performance problems of using a Cartesian product will having minimal impact on data query performance of the fact table.

In other words, focus your solution on the DATA SOURCE, not on the query.

Adding dummy rows to the database is very rarely the right answer. This should be a simple report level config option.

In some cases you will want to use an outer join (or the Preserve Dimensions Technique) to bring back additional rows. This is useful for charting continuous data that has missing data (like a chart by day where there is no data for Wed).

However in some cases you are simply want a zero to show in the report when there is no value. The custom display formats have worked in some versions in the past, but I know they are not working in 11G (there is an Oracle defect Bug 14092232 filed on this already). I am encountering this problem right now on my current project.

Hi guys,
thank you very much for your help.
Honestly, I can not believe that I am still fighting this seemingly simple problem.
My data is organized in a star schema where main fact drives out results and yes we do have time dimension.
I tried with virtual fact crashed the server :) with cartesian joints, tried with joining two reports, but I have 15 dashboard prompts (do not ask me why :)) needless to say ... good luck.
Actually, this morning we are were discussing adding dummy rows, but this seems so sloppy for me that I want to stay away from it at all costs.

I am still puzzled that among megagazillion check boxes I do not have one that says "display zero values" :).

Jeff thank you for your input, I will follow on it with our Oracle rep.