This discussion is archived
5 Replies Latest reply: Dec 11, 2012 10:40 AM by 979015 RSS

Custom Subtotal

979015 Newbie
Currently Being Moderated
Hello OBI Gurus,

I need some help. We have a report that requires what I call 'Custom Subtotals' -- i.e. I need the subtotal values to point to a different column than the one it belongs to.

For example, in a database table I have 4 columns: [Parent Entity], [Child Entity], [Parent Limit], [Child Limit]. The [Child Entity] column rolls up to the [Parent Entity] Column. In my OBI report, I need one column called 'Limit'.. And it should display [Child Limit] at the [Child Entity] Level, but it should display [Parent Limit] at the [Parent Limit] Subtotal. In this example, the subtotal is not the sum of the child limits, it is a completely separate number in the database.

Thank you,
Elliot
  • 1. Re: Custom Subtotal
    prassu Pro
    Currently Being Moderated
    Hi,
    Use dimension hierarchy to achieve your requirement.

    http://oracletechnotalk.blogspot.in/2012/04/creation-of-hierarchy-in-obiee.html

    mark if helpful/correct..

    thanks,
    prassu
  • 2. Re: Custom Subtotal
    979015 Newbie
    Currently Being Moderated
    Thanks prassu -- I know about dimension metrics, but will that be able to consolidate two columns into one one column, so that level2 attributes show level2 metrics, while the SUBTOTAL shows level1 metric?
  • 3. Re: Custom Subtotal
    prassu Pro
    Currently Being Moderated
    Yes,
    parent level hold the total of the child levels.

    mark if helpful/correct..

    thanks,
    prassu
  • 4. Re: Custom Subtotal
    979015 Newbie
    Currently Being Moderated
    Prassu, Thanks, but I don't want the parent level to be a total of the child levels. I want the total to be a completely different number in the database.

    e.g. This is how it will look in the report:

    CSV:

    Parent Level,Child Level,parent_limit,child_limit,"myNewColumn called ""Limit""",
    Company A,Company A.1," 5,000,000 "," 750,000 "," 750,000 ",
    Company A,Company A.2," 5,000,000 "," 200,000 "," 200,000 ",
    Company A,Company A.3," 5,000,000 "," 1,500,000 "," 1,500,000 ",
    Subtotal,," 15,000,000 "," 2,450,000 "," 5,000,000 ",
  • 5. Re: Custom Subtotal
    979015 Newbie
    Currently Being Moderated
    Any other thoughts on this?

    Thanks
    Elliot

    Edited by: emacmill on Dec 11, 2012 10:40 AM

Legend

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