Thanks Nosenco for your reply,
But my database is oracle and I have this options on by default.
Add some snapshot:
I have trying IFNULL in logical layer or in presentation layer and I don't have any effect to my analysis.... I think that IFNull function doesn't work in obiee 18.104.22.168.0
It's very weird.
IfNull perfectly work (on all the 22.214.171.124+ versions, just didn't try on the patch released 2 days ago...).
As we asked few posts back: post your physical query !
Because there is something you are not telling us or that we are not understanding in your model.
Thanks @Gianni Ceresa for your reply.
I try to explain my problem from the beginning for a better understand.
I'll explain based on Excel Demo Datasource that I have created although OracleDB should be the same...
My excel DS lookslike:
I config an ODBC DS Connection and Mapping two tables.
Join Fact_Table through Office_Dim like this:
Next step: Create a new businesmodel and drag two physical tables into Demo BM.
Drag BM to Presentation Layer.
Save RPD without any error.
Then I’ll do an analisys.
I create a PIVOT TABLE and I see this results:
You can see that my measure is with IFNULL formula.
But this formula doesn’t effect to my table.
Please if you need more info please ask me.
Hi Alex1 ,
thanks for all the details (I guess that after 30 answers it was good to restart from scratch).
So, what you see in the pivot is perfectly correct, and it's also correct that IFNULL doesn't work there because there is no NULL in your pivot.
IFNULL would match (with your current model), if and only if in your Fact table you have 4 extra rows:
Ana, 1, NULL
David, 2, NULL
John, 2, NULL
Rachel, 1, NULL
Right now your empty cells aren't returned at all from the physical query (and it's why we were asking to see it earlier).
Your requirement is to have "-1000" in these empty cells, right? And you say you can't use the column format to define "-1000" as the value to display for empty cells because you need to sum rows / columns and you want the -1000 to be counted in the calculation?
Exactly Gianni Ceresa.
This is my problem.
1 person found this helpful
If you really want "-1000" as a real number there you need to find a way to "produce" that cell in the physical query resultset, a dirty way to do it would be a cartesian product between your 2 tables: in that way your resultset has at least a cell for every single cell of your pivot.
Plan B), create these empty rows in the Fact table when loading it by ETL, it's less dirty at least (your DB will not like a cartesian product done every time somebody run a query...)
I'll try your options