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