4 Replies Latest reply: Jan 30, 2013 8:26 PM by user2534719 RSS

    OBIEE Reporting Requirement

    Pinnocchio
      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
          Do you mind to update couple of your old posts?
          • 2. Re: OBIEE Reporting Requirement
            user2534719
            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
              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
                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