2 Replies Latest reply: Nov 16, 2012 10:41 AM by NiharJ RSS

    OBIEE 10g Pivot table - dispaly all months even without data

      Hello 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.

      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,