Hello
I have a problem with null values.
We have a fact with measure M1 and Dimensions like Calendar, KPI_ITEM ,...
in an aggregated analysis I want to show the count of occurrences that a KPI was in a critical status.
for some days it happens once or more. for some days there is no record for it.
I want to show the counts in a line chart.
as you know OBIEE doesn't show the null values(by def).
in analysis I checked the "Include Null Values" and it makes Cartesian multiply of CALENDAR,KPI_ITEMS and FACT TABLE(M1)
the problem:
as I understood OBIEE first multiplies the tables and then starts to calculate formulas just for rows that there is real data.
so I cannot use CASE WHEN and IFNULL functions to show 0 instead of null values.
I handled this, by changing the data format in table view by using #,##0;-#,##0;0 format.
but I cant draw continues line in line chart and OBIEE just draws point for days that we had criticals
and there is no line or point for days that there isn't any critical situation for them(so they are null)
after Cartesian multiply, ifnull doesn't work for null records. so i use #,##0;-#,##0;0 to show null values:
up: what OBIEE shows.
Down: what I want to display
