Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
OBIEE 11.1.1.9.5 'Include null Values'
Hi all,
We are creating graphs, pivots in the obiee analystics (11.1.1.9.5) in the new releases we have option of showing rows with null values also.
But is there a way to print zero in the place of null?
The requirement is to show 0 instead of nulls for the months if there is no data.
Because of showing nulls, the lines on the graph are splitting because of no data.
Please help.
Answers
-
Hi,
Try to use Conversion Funstions -> IFNULL.
IFNULL(column measure, value)
For your measure:
IFNULL("Resolved Cases", 0)
0 -
Hi choracy,
I am using option of 'Include Null Values' in the pivot/graph.
Nulls are printing but not able to print 0 by using ifnull function.
Thanks.
0 -
IFNULL will not work because the data is not null, it doesn't exist (and it's different).
So the formula with IFNULL is not evaluated for "nulls" values (the physical query retrieve the dimensions attributes in a different query than the fact so dimensionality attributes aren't restricted by the facts).
On the screen in a table / pivot you can display the 0 by setting a custom data format mask, just not sure it will render the way you want in the chart.
Just keep in mind your data is not NULL, it doesn't exist at all ...
0 -
Hi Ceresa,
You are 100 % right, the data not existed at all..but the problem is we were able to show null, the question from the users will be ... if you are able to show nulls.. then why can't we show 0 in that place.
I agree with you completely,,, but need to find a way to print it.. Thanks for the time..
0 -
What Gianni is telling you is that "Showing" sometbing as null and actually technically RETRIEVING null is something completely different.
0 -
Hi Berg,
Yes, I got that.. Thanks for looking into this..
Thank you..
0 -
So the result is that you may need to remodel your models in order for queries to be spawned which manage the ifnull part and you actually have physially existing nulls (not zeros...that's a completely different answer btw) in the actual result set
0 -
The name of that checkbox is wrong as (we all agree on that) you are not retrieving NULLs values but inexistant values.
As I said in a table/pivot you can display 0 by setting a custom format mask on the value, on the chart I suspect it has no effect at all (didn't test it, so give it a try ....).
The only other option to do what you look for is to get some real data in your source. So store NULL or 0 in the fact table and they will be on screen. Or change your model to generate that data in the physical query (but going by this way open the door of cartesian products etc.).
EDIT; as often thinking and writing the same thing as Christian with few seconds delay
0 -
Hahahaha that always looks like
{quote}
WhatChristianJustSaid --verbose
{quote}
0 -
For DW design - minimize null values in your fact tables and based on use of information (your specific case) don't skip rows at the granular level. So in ETL/ELT where a certain measure doesn't apply to the grain - write in the zero row ... only do it for the fact tables that need to support that type of use. Your case matches this principle...
0
