This discussion is archived
7 Replies Latest reply: Feb 1, 2013 8:36 AM by user2534719 RSS

Sum  of the gross sales

964449 Newbie
Currently Being Moderated
Hi Dudes,

I have requirement is i need the top ten gross sales for the accounts and i want the remaining all account gross sales come in 11 row only
ex: i have 100 rows, top 10 gross sales in first 10 rows and 11 row is total gross sales from 11 row to 100 rows.
Please suggest me.

Thanks,
Raji
  • 1. Re: Sum  of the gross sales
    Vineeth.K.M Journeyer
    Currently Being Moderated
    Hi,

    You need to create BINS in obi answers.
    Please refer the below link:
    http://oraclebizint.wordpress.com/2007/12/05/oracle-bi-ee-101332-bins-and-groups/

    Thanks,
    Vineeth
  • 2. Re: Sum  of the gross sales
    964449 Newbie
    Currently Being Moderated
    Thanks for your reply.

    but my requirement is we have brands assigned to different accounts, each brand can have different accounts in top 10, so i can't group on it. please suggest me.
  • 3. Re: Sum  of the gross sales
    Naik..... Explorer
    Currently Being Moderated
    Using Selection steps we can achieve this... bt here we should take hard coded value (Like using BINS).
    -in this case i was taken prod, revenue(sort desc)
    -- select selection steps:
    prod col: Then, New Step...-> Add Groups or Calculated Items -> new calculated item
         Display label name: Remaining sum(11-100)
         function: SUM
         select products which are not in top 10 i.e, 11 to 100 records(move all then remove top 10)
         check the "Remove calculated item members from view"
         click OK

    pls mark it helps......
  • 4. Re: Sum  of the gross sales
    Vineeth.K.M Journeyer
    Currently Being Moderated
    Hi,

    Can you create a prompt for the Brand ? Is this ok with the requirement ?

    Thanks,
    Vineeth
  • 5. Re: Sum  of the gross sales
    964449 Newbie
    Currently Being Moderated
    actually no, but if yes, what is the solution.
  • 6. Re: Sum  of the gross sales
    Maqsood Hussain Journeyer
    Currently Being Moderated
    Add one more column in criteria with formula as

    case when RANK(Gross Sales) <=10 then Account Name else 'Non Top 10' end

    Also for Brand

    case when RANK(Gross Sales) <=10 then Brand else 'Non Top 10' end

    Now use these columns in your report instead of Account and Brand column. You will get results as below

    Brand Account Gross Sales

    B1 A1 100
    B1 A2 90
    .........................................
    ........................................
    B10 A10 70

    Non Top 10 Non Top 10 1000


    Hope this helps
  • 7. Re: Sum  of the gross sales
    user2534719 Newbie
    Currently Being Moderated
    I was struggling with this problem ....specially in OBIEE 11g it's kind of tricky...and a few things needs to be taken care........here is the link which worked finally for me after wasting almost 1 working day to break it thru


    http://www.rittmanmead.com/2010/09/oracle-bi-ee-11g-filters-selection-steps-top-n-products-case-study/

Legend

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