Left Outer Join In OBIEE
649804Feb 9 2009 — edited Feb 10 2009Hi,
I have two Oracle tables in the physical layer: a fact table and a dimension table.
The fact table (FACT_TBL) like this:
CURR_MTH | PREV_MTH | CURR_GRADE | PREV_GRADE | CITY | VALUE
-----
Jun | May | B | A | X | 5
Jun | May | A | A | Y | 10
Jun | May | C | C | X | 15
Jun | May | D | C | Y | 20
And the dimension table (GRADE_TBL) like this:
GRADE | GRADE_NAME
-----
A | A
B | B
C | C
D | D
I joined those two tables above in the physical layer using GRADE field. Then, I created a business model using those two tables. I want to create a 4x4 matrix report with PREV_GRADE as the first column and the CURR_GRADE accross to the right like this:
CURR_MONTH PROMPT = Jun
PREV_MONTH PROMPT = May
CITY PROMPT = X
-----
PREV_GRADE | A | B | C | D |
-----
A | (null) | 5 | (null) | (null) |
B | (null) | (null) | (null) | (null) |
C | (null) | (null) | 15 | (null) |
D | (null) | (null) | (null) | (null)|
-----
The problem is that I couldn't show all grade (A to D) in the PREV_GRADE column in that table. Every time I filter the CITY in the prompt to X, I always get only A and C. The B and D rows are suppressed. How do I achieve the left outer join that I wanted above? Thanks a lot for the help.