5 Replies Latest reply: Dec 11, 2012 12:40 PM by 979015 RSS

    Custom Subtotal

    979015
      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
          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
            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
              Yes,
              parent level hold the total of the child levels.

              mark if helpful/correct..

              thanks,
              prassu
              • 4. Re: Custom Subtotal
                979015
                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
                  Any other thoughts on this?

                  Thanks
                  Elliot

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