Skip to Main Content
Forums

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Zero Supression

RMachSep 16 2008 — edited Sep 17 2008
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

Comments

577516
Did you try:

Limit columns based on section values or
Limit rows based on section values
in pivot table ?
1 person found this helpful
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.
1 - 5
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 15 2008
Added on Sep 16 2008
#answers, #business-intelligence-applications, #business-intelligence-foundation, #business-intelligence-suite-enterprise-edition-obiee, #obiee
5 comments
532 views