OBIEE 10g Pivot table - dispaly all months even without data
Nihar JNov 16 2012 — edited Nov 16 2012Hello experts,
I have a requirement to build a Pivot table where
Columns - months (last 6 months)
Rows - Categories
values - no. of items per category per month.
The requirement is to display all the months regardless of the data.
i.e
May June Jul Aug Sep Oct Total
abc 1 1 1 0 1 1 5
pqr 2 1 0 0 2 1 6
Note in above case that August does not have any data, but I could achieve to show this month by using union query, now the problem is the union query always generates an additional row.
May June Jul Aug Sep Oct Total
abc 1 1 1 0 1 1 5
pqr 2 1 0 0 2 1 6
0 0 0 0 0 0 0
I need a solution to suppress this additional row.
Additionally, when there is no data at all, I need to activate the "No Result" view, but due to the union query there is always one record and the report will never show "No Result" message.
I tried using Javascript also to suppress the additional record, which helped me at some extent but not consistent. It still sometimes displays the additional row if someone accidentally refreshes the dashboard.
Please let me know if anyone came across such scenario and was able to resolve it.
Thank you,