This discussion is archived
4 Replies Latest reply: Jan 30, 2013 6:26 PM by user2534719 RSS

OBIEE Reporting Requirement

Pinnocchio Newbie
Currently Being Moderated
OBIEE Reporting Requirement

I have the following OBIEE reporting requirement that I am needing assistance with:

I have to produce a report from the following table.

CUSTOMER_ID|     EARNINGS |     CATEGORY
A1234     |     1000     |     A
A1234     |     2000     |     B
B1234     |     1000     |     C
C1234     |     0     |     D
D1234     |     2500     |     E
E1234     |     0     |     F


Report Layout:

Category     | Category A | Other Category | Category A & Other Category |
Earned     |     100 |     120     | 100 |
Unearned     |     150 |     150     | 200 |

So the report above basically displays a count of the customers who have earnings or no earnings in the 2 categories which are "Category A" and "Other Category." Other Category is everything else grouped together except Category A i.e. (B, C, D, E, F). The 3rd column is counting customers who are in both column 1 and 2. I have modeled the repository for the 1st 2 columns but am stuck with how to come up with the 3rd column. Repository has been set up as follows:

Fact: Distinct Count of CUSTOMER_ID

Dimension:

CASE WHEN Category = A THEN Category A
ELSE WHEN Category <> A THEN Other Category END

In Answers I have 2 calculated columns:

FILTER(Customer Count) USING Earnings > 0 (Earned)
FILTER(Customer Count) USING Earnings <= 0 (Uneared)

How can I solution this to get the 3rd column of Category A & Other Category? Thanks in advance.
  • 1. Re: OBIEE Reporting Requirement
    Srini VEERAVALLI Guru
    Currently Being Moderated
    Do you mind to update couple of your old posts?
  • 2. Re: OBIEE Reporting Requirement
    user2534719 Newbie
    Currently Being Moderated
    based on number of records you have shown in your table for customer earnings and category ...could you please post the desired report out put ...here?

    as i see the data here will be summed up as


    In Category A who earned something - 1 customer (A 1234)
    In other Category who earned something - 3 customers
    In other category unearned customers -- 2 (C1234, e1234)
    category A & Other earned - 4 customers
    Category A & others unearned -customers

    is that correct??
  • 3. Re: OBIEE Reporting Requirement
    Pinnocchio Newbie
    Currently Being Moderated
    Yes that is correct. Below is the expected output I am trying to get to.
         | Category A | Other Category | Category A & Other Category |
    Earned       | 100         | 120                 | 100                                    |
    Unearned   | 150           | 150                 | 200                                    |
  • 4. Re: OBIEE Reporting Requirement
    user2534719 Newbie
    Currently Being Moderated
    not getting ....it's confusing


    Category A & and other category = Category A + Other category (for both earned and unearned)


    but your report doesn't show this result. so some gap is there.

    your table sample data has only 6 records, why the report output is showing customer count in 100s?

    Edited by: user2534719 on Jan 30, 2013 6:25 PM

    Edited by: user2534719 on Jan 30, 2013 6:26 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points