Zero Supression

RMach

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

577516

Did you try:

Limit columns based on section values or
Limit rows based on section values
in pivot table ?

RMach

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

CAn you let us know which columns are placed in section area, which in row area and which in columns area?

RMach

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

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.