Forum Stats

  • 3,853,719 Users
  • 2,264,259 Discussions
  • 7,905,435 Comments

Discussions

Zero Supression

RMach
RMach Member Posts: 4 Blue Ribbon
This has caused a few days frustration.

I am a novice to the OBIEE world, with about 6 months of hands on experience with 2 days in a course of "Oracle BI presenatation services 10G: Create Reports/Dashboards Ed 1." I do not do any of the hard coding and/or backend. I am pretty much purely within "Answers" for my job.

What I want: (The formulas are variables that have been developed to run those formulas by someone on the backend)

Acct lvl 1 - - - - - - - - - - Acct lvl 2
AA - - - - - - - - - - - - - - - W
AA - - - - - - - - - - - - - - - X
BB - - - - - - - - - - - - - - - Y
BB - - - - - - - - - - - - - - - Z
(Formula AA - BB) - - - - (Formula AA - BB)
(Formula AA-BB / AA) - - (Formula AA-BB / AA)
CC - - - - - - - - - - - - - - - S
CC - - - - - - - - - - - - - - - T
DD - - - - - - - - - - - - - - - U
DD - - - - - - - - - - - - - - - V

What I am getting

AA - S (null)
AA - T (null)
AA - U (null)
AA - V (null)
AA - W
AA - X
AA - Y (null)
AA - Z (null)
BB - S (null)
BB - T (null)
BB - U (null)
BB - V (null)
BB - W (null)
BB - X (null)
BB - Y
BB - Z
(Formula AA - BB) - S (null)
(Formula AA - BB) - T (null)
(Formula AA - BB) - U (null)
(Formula AA - BB) - V (null)
(Formula AA - BB) - W (null)
(Formula AA - BB) - X (null)
(Formula AA - BB) - Y (null)
(Formula AA - BB) - Z (null)
(Formula AA-BB / AA) - S (null)
(Formula AA-BB / AA) - T (null)
(Formula AA-BB / AA) - U (null)
(Formula AA-BB / AA) - V (null)
(Formula AA-BB / AA) - W (null)
(Formula AA-BB / AA) - X (null)
(Formula AA-BB / AA) - Y (null)
(Formula AA-BB / AA) - Z (null)
CC - S
CC - T
CC - U (null)
CC - V (null)
CC - W (null)
CC - X (null)
CC - Y (null)
CC - Z (null)
DD - S (null)
DD - T (null)
DD - U
DD - V
DD - W (null)
DD - X (null)
DD - Y (null)
DD - Z (null)

The pivot table showing these results works with the Acct 1 level by itself. Expanding it into Acct 2 is where we are stuck. The table looks like

Columns:
current month

Rows:
Measure Lables
Acct - lvl 2

Measures:
AA
BB
(Formula AA - BB)
(Formula AA-BB / AA)
CC
DD

What other information is needed that may help to answer this question?

Any feedback is greatly appreciated.*

In our old system, Cognos, "Zero Suppression" seemed to be the solution. There seems to be no equvilant in OBIEE.

Edited by: user10269236 on Sep 16, 2008 9:32 AM

Edited by: user10269236 on Sep 16, 2008 11:50 AM
Tagged:

Answers

  • 577516
    577516 Member Posts: 1,058
    Did you try:

    Limit columns based on section values or
    Limit rows based on section values
    in pivot table ?
    577516
  • RMach
    RMach Member Posts: 4 Blue Ribbon
    Yes.

    Each row is being shown because it is being used at some point in the pivot table. We tried to add "AA" IS NOT NULL filters to the column, but they would prevent the rows that were null for "AA" from being displayed for the other rows that did have value.
  • 577516
    577516 Member Posts: 1,058
    CAn you let us know which columns are placed in section area, which in row area and which in columns area?
  • RMach
    RMach Member Posts: 4 Blue Ribbon
    Sorry, I just edited the original message to be a little clearer.

    In the end we are hoping to make a financial statement.
    AA = Revenues
    BB = Costs
    First Formula = Gross Margin
    Second Formula = Gross Margin %
    CC = Pay
    DD = Taxes

    This is a simplified version, if we can get this to show what we want it too, we can figure the rest out.


    Right now we are trying to do this with two different avenues, neither are working. The first avenue is the one we have listed above. We think it is the most promising if we can get zero suppression to remove the rows of nulls.

    The other path we are exploring is to just use the hierarchy as is.

    Columns:
    Measure Lables

    Rows:
    Acct-Lvl 1
    Acct-Lvl 2

    Measures:
    Actual for Current Month

    The problem with this path is that we cannot get Gross Margin and Gross Margin % in because they are formulas. They work on the first level (Sort of, they are auto sorted to the bottom of the pivot table), but when you add the second level, those formula attempt to calculate for each secondary level with values, and the table becomes monsterous.



    Is the problem only solvable within the cube itself?
  • RMach
    RMach Member Posts: 4 Blue Ribbon
    Well, the person managing the database has altered how things work a bit in an attempt to fix this.

    Thus the answer to the orignal question in this thread is that it can't be done without an alteration in the back end.
This discussion has been closed.